Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PRB: Errors Not Returned When Stored Procedure Statement Fails


View products that this article applies to.

Symptoms

When executing a stored procedure with multiple queries (SELECTs, INSERTs and UPDATEs), the ActiveX Data Objects (ADO) errors collection appears not to be populated after an error occurs in the stored procedure. The stored procedure might also not return values from its RETURN statement.

↑ Back to the top


Cause

The SQL Server OLE DB provider provides more accurate information (than ODBC) to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected or a result set. You can walk through these result sets in ADO using the NextRecordset method on the Recordset object.

The SQL Server ODBC provider, however, does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement, if it has one. This is why this problem may not manifest with ODBC.

In both cases, return values from the stored procedure may not be returned.

↑ Back to the top


Resolution

To resolve this problem, make sure that the SET NOCOUNT ON statement comes before any other SQL statements in the stored procedure, as in the following:
CREATE PROCEDURE TestProc
AS 
  SET NOCOUNT ON -- This is off by default
  SELECT au_lname FROM Authors
  RETURN 0
				

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

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


Keywords: kbnosurvey, kbarchive, kbcodesnippet, kbdatabase, kberrmsg, kbprb, KB253240

↑ Back to the top

Article Info
Article ID : 253240
Revision : 5
Created on : 2/28/2014
Published on : 2/28/2014
Exists online : False
Views : 335