Microsoft KB Archive/194792

= How To Retrieve Values in SQL Server Stored Procedures with ADO =

Article ID: 194792

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q194792





For a Microsoft Visual Basic .NET version of this article, see 308049.



SUMMARY
There are important issues to consider when attempting to retrieve RAISERROR/PRINT/RETURN values from SQL Server stored procedures through ActiveX Data Objects (ADO). Here are three issues:
 * RAISERROR statements in SQL Server must be a severity level of 11-18.
 * PRINT statements in SQL Server can also populate the ADO errors collection. However, PRINT statements are severity level zero (0) so, at least one RAISERROR statement is required in the stored procedure to retrieve a PRINT statement with ADO through the Errors collection.
 * RETURN values in a stored procedure must be associated with at least one resultset.



MORE INFORMATION
The following code sample demonstrates browsing the ADO Errors collection to access the RAISERROR/PRINT/RETURN detail from a SQL Server stored procedure returning multiple resultsets:   Paste and execute the following code in the ISQL_W window to create the stored procedure used for the ADO sample in step 4: use pubs GO

if exists (select * from sysobjects where id =       object_id('dbo.ADOTestRPE') and sysstat & 0xf = 4) drop procedure dbo.ADOTestRPE GO

create procedure ADOTestRPE (     @SetRtn  INT=0 OUTPUT,      @R1Num   INT=1,      @P1Num   INT=1,      @E1Num   INT=1,      @R2Num   INT=2,      @P2Num   INT=2,      @E2Num   INT=2      ) AS     DECLARE @iLoop     INT DECLARE @PrintText VARCHAR(255) DECLARE @iErrNum  INT

/*  Check for no Resultsets - needed to get the RETURN value back */ IF @R1Num + @R2Num = 0 SELECT NULL

/*  Resultset 1  ******************************* */

IF @R1Num > 0 BEGIN SET ROWCOUNT @R1Num SELECT 'Resultset 1' RsNum, Title FROM Pubs..Titles SET ROWCOUNT 0 END

/* Must raise a default error context in which to return the PRINT */ /* statement */ /* (if none present) since PRINT statements are a severity level of */ /*0. */      IF (@P1Num > 0) AND (@E1Num = 0) RAISERROR ("RAISERROR.PError1",         11, 2)

IF @P1Num > 0 BEGIN SELECT @iLoop = 0 WHILE @iLoop < @P1Num BEGIN SELECT @iLoop = @iLoop + 1 SELECT @PrintText = 'PRINT.Resultset.1: Line ' + CONVERT(char(2), @iLoop) PRINT @PrintText END END

IF @E1Num > 0 BEGIN SELECT @iLoop = 0 WHILE @iLoop < @E1Num BEGIN SELECT @iLoop = @iLoop + 1 SELECT @iErrNum = @iLoop + 201000 RAISERROR ("RAISERROR.Resultset.1", 11, 2) END END

/*  Resultset 2  ******************************* */

IF @R2Num > 0 BEGIN SET ROWCOUNT @R2Num SELECT 'Resultset 2' RsNum, Title FROM Pubs..Titles SET ROWCOUNT 0 END

/* Must raise a default error context in which to return the PRINT */ /* statement */ /* (if none present) since PRINT statements are a severity level of */ /* 0. */       IF (@P2Num > 0) AND (@E2Num = 0) RAISERROR ("RAISERROR.PError2",      11, 2)

IF @P2Num > 0 BEGIN SELECT @iLoop = 0 WHILE @iLoop < @P2Num BEGIN SELECT @iLoop = @iLoop + 1 SELECT @PrintText = 'PRINT.Resultset.2: Line ' + CONVERT(char(2), @iLoop) PRINT @PrintText END END

IF @E2Num > 0 BEGIN SELECT @iLoop = 0 WHILE @iLoop < @E2Num BEGIN SELECT @iLoop = @iLoop + 1

SELECT @iErrNum = @iLoop + 202000 RAISERROR ("RAISERROR.Resultset.2", 11, 2) END END

/*  Return & Output ************************************ */ select @SetRtn = -1 RETURN @SetRtn GO

 Create a Standard .EXE project in Visual Basic. Form1 is created by default. From the Project menu, choose References and select the Microsoft ActiveX Data Objects Library. NOTE: You must use ADO version 2.0 or later for the code to work correctly. You can obtain the latest Microsoft Data Access Components (MDAC) components on the Web at the following URL:

http://msdn.microsoft.com/dataaccess

  Place a Command button on the Form, and then paste the following code in the General Declarations section of the Form: NOTE: You may need to change the database connect string for your environment. 'This Code demonstrates RAISERROR/PRINT/RETURN values with ADO and 'multiple resultsets.

Sub CreateParms

Dim ADOCmd As New ADODB.Command Dim ADOPrm As New ADODB.Parameter Dim ADOCon As ADODB.Connection Dim ADORs As ADODB.Recordset Dim sParmName As String Dim strConnect As String Dim rStr As String

On Error GoTo ErrHandler

strConnect = "driver={SQL       Server};server=(local);uid=sa;pwd=;database=pubs"

Set ADOCon = New ADODB.Connection With ADOCon .Provider = "MSDASQL" .CursorLocation = adUseServer 'Must use Server side cursor. .ConnectionString = strConnect .Open End With

Set ADOCmd.ActiveConnection = ADOCon With ADOCmd .CommandType = adCmdStoredProc .CommandText = "ADOTestRPE" End With

'Parameter 0 is the stored procedure Return code. sParmName = "Return" Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamReturnValue,, 0) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = -1

'Parameter 1 is the setting for the stored procedure Output ' parameter. sParmName = "Output" Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamOutput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 999

'Parameter 2 sParmName = "R1Num"    'Number of rows to return in Resultset 1. Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamInput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 1

'Parameter 3 sParmName = "P1Num"    'Number of PRINT statements in Resultset 1. Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _      adParamInput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 0

'Parameter 4 sParmName = "E1Num"    'Number of RAISERROR statements in Resultset '1.     Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamInput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 0

'Parameter 5 sParmName = "R2Num"    'Number of rows to return in Resultset 2. Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamInput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 2

'Parameter 6 sParmName = "P2Num"    'Number of PRINT statements in Resultset 2. Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamInput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 0

'Parameter 7 sParmName = "E2Num"    'Number of RAISERROR statements in Resultset ' 2.     Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamInput) ADOCmd.Parameters.Append ADOPrm ADOCmd.Parameters(sParmName).Value = 0

Set ADORs = ADOCmd.Execute

Do While (Not ADORs Is Nothing) If ADORs.State = adStateClosed Then Exit Do         While Not ADORs.EOF For i = 0 To ADORs.Fields.Count - 1 rStr = rStr & " : " & ADORs(i) Next i             Debug.Print Mid(rStr, 3, Len(rStr)) ADORs.MoveNext rStr = "" Wend Debug.Print "--" Set ADORs = ADORs.NextRecordset Loop

Debug.Print "Return: " & ADOCmd.Parameters("Return").Value Debug.Print "Output: " & ADOCmd.Parameters("Output").Value

GoTo Shutdown

ErrHandler: Call ErrHandler(ADOCon) Resume Next

Shutdown: Set ADOCmd = Nothing Set ADOPrm = Nothing Set ADORs = Nothing Set ADOCon = Nothing

End Sub

Private Sub Command1_Click

Call CreateParms

End Sub

Sub ErrHandler(objCon As Object)

Dim ADOErr As ADODB.Error Dim strError As String

For Each ADOErr In objCon.Errors strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _ & vbCr & _ "  (Source: " & ADOErr.Source & ")" & vbCr & _ "  (SQL State: " & ADOErr.SQLState & ")" & vbCr & _ "  (NativeError: " & ADOErr.NativeError & ")" & vbCr If ADOErr.HelpFile = "" Then strError = strError & "  No Help file available" & vbCr & vbCr Else strError = strError & "  (HelpFile: " & ADOErr.HelpFile & ")" _ & vbCr & _ "  (HelpContext: " & ADOErr.HelpContext & ")" & _ vbCr & vbCr End If      Debug.Print strError Next

objCon.Errors.Clear

End Sub  Change the value of parameters two through seven to alter the number of PRINT statements and/or RAISERROR statements generated by the stored procedure and returned through ADO. Run the Visual Basic code sample again and note that the RAISERROR and PRINT statements are returned through the ADO errors collection. Change the values to experiment with different combinations of PRINT/RAISERROR statements with different resultsets. Please refer to the SQL stored procedures for specific workarounds for special cases.

NOTE: To retrieve a RETURN value in ADO with a stored procedure there must be at least one resultset. In order to work around this problem, when no resultsets are specified (in the ADO sample code) the stored procedure executes a SELECT NULL to return a null resultset to ADO thereby populating the RETURN value. In addition, to work around the issue of specifying no RAISERROR statements and a combination of PRINT statements, default RAISERROR statements are generated in order to provide a context for returning the PRINT statement via ADO. You must code RAISERROR statements in the format shown in the stored procedure because only severity levels of 11-18 return through the ADO errors collection.</ol>

<div class="references_section">