p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

pro_vb thread: FW: ADO2.6 and stored procedure


Message #1 by "Percival, Steve" <Steve.Percival@c...> on Wed, 4 Apr 2001 13:59:58 +0100
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0BD07.271BADC0
Content-Type: text/plain;
	charset="iso-2022-jp"

Thanks for you help but what I really want is the error being returned to
the Connection object errors collection, which is not happening even with a
select null in the Stored procedure. I am not really interested in the
return value(I just copied that part out of the "Inside SQL Server" book. 

-----Original Message-----
From: Katsumi Ohsawa [mailto:kosawa@p...]
Sent: 04 April 2001 12:18
To: Steve.percival@c...
Subject: ADO2.6 and stored procedure


Hi, Steve.
I misunderstood your question.
I hope this is what you want.

ADO never returns the return value if there is no "SELECT" state ment in
your souce code.

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

  _____



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:


1.	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



2.	Create a Standard .EXE project in Visual Basic. Form1 is created by
default.


3.	From the Project menu, choose References and select the Microsoft
ActiveX Data Objects 2.0 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/>


4.	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


5.	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 <mk:@MSITStore:kbdatacc.chm::/Source/adobj/q190988.htm> HOWTO: Open
ADO Recordsets Asynchronously Using WithEvents

Additional query words:


Keywords          : kbprint kbADO200 kbVBp kbVBp500 kbVBp600
Version           : WINDOWS:5.0,6.0
Platform          : WINDOWS
Issue type        : kbhowto



THIS E-MAIL AND ANY ATTACHED FILES ARE CONFIDENTIAL AND MAY BE LEGALLY
PRIVILEGED. If you are not the addressee, any disclosure, reproduction,
copying, distribution or other dissemination or use of this communication is
strictly prohibited. If you have received this transmission in error please
notify the sender immediately and then delete this e-mail. 
Opinions, advice or facts included in this message are given without any
warranties or intention to enter into a contractual relationship with the
Corporation of London unless specifically indicated otherwise by agreement,
letter or facsimile signed by an authorised signatory of the Corporation. 
Any part of this e-mail which is purely personal in nature is not authorised
by the Corporation of London. All e-mail through the Corporation's gateway
is potentially the subject of monitoring. 
All liability for errors and viruses is excluded. 



  Return to Index