FIX: RAISERROR Does Not Work with SQLOLEDB Provider

• Microsoft SQL Server version 7.0 Service Pack 1
• Microsoft Data Access Components versions 1.5, 2.0, 2.1 SP2
• Microsoft Visual Basic Enterprise Edition for Windows, version 6.0

SYMPTOMS

When using the SQLOLEDB provider with server side cursors, the error description is not returned from a RAISERROR call inside a SQL Server procedure. An error is raised to the client, however, the description is not returned to the client. Instead the client only receives the following error message:

Run-time error '-2147217887 (80040e21)':
Errors occurred

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

   Date       Size     File name     Platform
   ----------------------------------------------------------

   05/24/99   285 KB   s70664i.exe   Intel
 

WORKAROUND

To work around this problem, try either of the following:

• Use the client side cursor library. In the following code you need to change the cursor location to adUseClient instead of adUseServer.


-or-


• Use the Microsoft OLE DB Provider for ODBC Drivers instead of the Microsoft OLE DB Provider SQL Server.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Visual Basic Standard EXE and add a command button to the default form.



2. Set a reference to the Microsoft ActiveX Data Objects Library.



3. Add the following code to the command button Click event:




   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim SQL As String

   Set cn = New ADODB.Connection
   With cn
   'You will need to modify this connection string before running this.
   .ConnectionString = "Provider=SQLOLEDB.1;Persist Security    Info=False;User ID=sa;Initial 
   Catalog=yourdatabase;Data Source=yourserver"
   'The following ConnectionString works around the problem.
   '.ConnectionString = "Provider=MSDASQL.1;DRIVER={SQL    'Server};SERVER=yourserver;Persist Security Info=False;User       'ID=sa;DATABASE=yourdatabase;"
   '.CursorLocation = adUseClient '<<This works.
   .CursorLocation = adUseServer '<<This does not work.
   .Open
 End With

 'Add the database objects necessary for this test. 
 On Error Resume Next
 SQL = " CREATE TABLE ErrorTest " & _
 "( ID INT, " & _
 " MyText CHAR (255) )"
 cn.Execute SQL

 SQL = "CREATE TRIGGER trg_ErrorTest " & _
 "ON ErrorTest " & _
 "FOR INSERT " & _
 "AS " & _
 "IF EXISTS " & _
 " (SELECT * FROM inserted WHERE inserted.id = -99) " & _
 "BEGIN " & _
 " RAISERROR ('Error on Insert Into Trigger_Test (Invalid ID = -99)', 18, 1) " & _
 " ROLLBACK TRAN " & _
 "END " 

 cn.Execute SQL

 'Force an error to be raised to see when the problem occurs.
 On Error GoTo errhandler
 Set rs = New ADODB.Recordset
 rs.Open "SELECT * FROM ErrorTest where id = 999", cn, adOpenDynamic, adLockOptimistic
 rs.AddNew
 rs(0).Value = "-99"
 rs(1).Value = ""
 rs.Update   '<<Error will be raised here. 

 Exit Sub

 errhandler:

 Debug.Print "--errors occurred-----------------------------------"
 Debug.Print "Err Object: "
 Debug.Print Err.Number & " --> " & Err.Description
 Debug.Print "ADODB.Errors: "
 Dim localerr As ADODB.Error
 For Each localerr In cn.Errors
 Debug.Print localerr.Number & " --> "; localerr.Description
 Next
 

4. Run the code. Notice that the error description is not returned to the client program. Modify the code so that it uses client side cursors and it will behave as expected.

Additional query words: kbADO kbado200 kbado210sp2 kbstoredproc kbdsupport kbgrpmdac kbgrpvbdb kbOLEDB210bug kbOLEDB200bug

Keywords : kbADO200bug kbDatabase kbMDAC kbSQLServ kbVBp600
Version : WINDOWS:1.5,2.0,2.1 SP2,6.0; winnt:7.0 Service Pack 1
Platform : WINDOWS winnt
Issue type : kbbug