FIX: RAISERROR Does Not Work with SQLOLEDB Provider

Article translations Article translations
Article ID: 217019 - View products that this article applies to.
This article was previously published under Q217019
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55618 (SQLBUG70)
Expand all | Collapse all

On This Page

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

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 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

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 must modify this connection string with the appropriate username, database, and servername before running this code.
       .ConnectionString = "Provider=SQLOLEDB.1;Persist Security
        Info=False;User ID=<username>;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=<username>;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. Note that the error description is not returned to the client program. Modify the code so that it uses client side cursors and it behaves as expected.

Properties

Article ID: 217019 - Last Review: February 21, 2014 - Revision: 6.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 7.0 Service Pack 1
  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
Keywords: 
kbnosurvey kbarchive kbado270fix kbbug kbdatabase kbfix kbmdac270fix KB217019

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com