HOWTO: Retrieve Values in SQL Server Stored Procedures w/ ADO
ID: Q194792
|
The information in this article applies to:
-
Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
-
ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
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 2.0 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://www.microsoft.com/data/ - 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.
REFERENCES
Transact-SQL Help: search on: "PRINT/RAISERROR statement"
For more information, please see the following article in the Microsoft
Knowledge Base:
Q190988 HOWTO: Open ADO Recordsets Asynchronously Using WithEvents
Additional query words:
Keywords : kbprint kbADO200 kbDatabase kbSQLServ kbStoredProc kbVBp kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto
|