Steps to Reproduce the Behavior

1. Create an ASP page with the following code:
   
   Note You must change UserID=<username> to the correct value before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

   <%
   
   Set oConn = Server.CreateObject("ADODB.Connection")
   Set Rs = Server.CreateObject("ADODB.Recordset")
   
   On Error Resume Next
   
   oConn.Open("Provider=SQLOLEDB.1;User ID=<username>;Initial Catalog=Northwind;Data Source=DataSourceName")
   
   Set oCmd = Server.CreateObject("ADODB.Command")
   
   Set oCmd.ActiveConnection = oConn
   
   oCmd.CommandText = "TestProc"
   oCmd.CommandType = adCmdStoredProc
   oCmd.Parameters.Append oCmd.CreateParameter("RETURN_VALUE",3,4,0)
   
   oCmd.Execute()
   
   If oCmd.ActiveConnection.Errors.Count > 0 Then
     For Each oError in oCmd.ActiveConnection.Errors 
       Response.Write "Description = " & oError.Description & "<BR>"
     Next
   End If
   
   Response.Write( "Return Value = "  & oCmd.Parameters("RETURN_VALUE") & "<BR>")
   oConn.Close
   
   Set oConn = Nothing
   Set oCmd = Nothing
   
   %>
   					

2. Create a stored procedure similar to the following:

   ALTER Procedure TestProc AS
   -- Uncommenting the line below resolved this problem
   -- set nocount ON
   begin tran
     insert into xxx values(1)
      
     If @@error <> 0
       Begin    
         rollback
         return 1     
       End
   
     insert into yyy values(<Invalid Data>)
     if @@error <> 0
      begin
       rollback
       return 2
      end
   
     insert into zzz values(3)
     if @@error <> 0
      begin
       rollback
       return 3
      end
   
   commit tran
   return 0
   					

3. Run the ASP page, and you should get the following result instead of an error message and a return value of 2.
   
   Return Value =

Back to the top