Microsoft KB Archive/254304

= PRB: ADO Errors Collection Does Not Contain User-Defined Error Messages =

Article ID: 254304

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft OLE DB Provider for SQL Server 7.01
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q254304



SYMPTOMS
Executing a SQL Server stored procedure (SP) by using ActiveX Data Objects (ADO) does not populate the ADO Errors collection of the Connection object with user-defined errors that are raised in the SP. This behavior only occurs when using the OLE DB Provider for SQL Server (SQLOLEDB) to establish an ADO connection to the SQL Server database.



CAUSE
This problem is specific to the SQLOLEDB. It only occurs when the NOCOUNT SQL Server option has been turned OFF, which is the default setting.



RESOLUTION
Either of the following resolutions may be used to address this issue:
 * Turn on the SQL Server NOCOUNT option by using the SET statement before executing the stored procedure by using an ADO connection or command. This adds the user-defined error messages raised in the SP to the ADO Connection object's Errors collection.

-or-


 * Use the OLE DB Provider for ODBC (MSDASQL) to open the ADO connection to SQL Server. The MSDASQL Provider establishes the connection to the specified SQL Server by using the SQL Server ODBC driver. The ADO Errors collection is populated with user-defined errors raised in the SP without having to turn on the NOCOUNT option when executed by using an ADO Connection opened with the MSDASQL Provider.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  Launch the SQL Server Query Analyzer and add a Table called Test to the Pubs database in SQL Server by using the following SQL statement: CREATE TABLE TEST ( Empno char(5) PRIMARY KEY, Empname varchar(20))   Create an SP called INSTEST in the pubs database by running the TSQL block given here: create procedure INSTEST (@mempno char(5),@mempname varchar(20)) as Insert into Test values (@mempno,@mempname) if (@@error != 0) begin raiserror ('Invalid Insert',16,1) end   Execute the following Insert statement in the pubs database to insert a row into the TEST table: EXEC INSTEST 'E001','aaa'  Open a Standard EXE project in Visual Basic, and drop a CommandButton control on Form1. Change the caption of the CommandButton to Execute INSTEST.</li>  Place the following line of code in the Form's General Declarations section to declare an ADO Connection object: Dim cn As ADODB.Connection </li>  Place the following lines of code in the Form_Load event to establish an ADO Connection to your SQL Server pubs database by using the SQLOLEDB provider. Make sure to change the connection string properties so that they point to your SQL Server installation:

Note You must change User ID= to the correct value before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database. Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;Data Source=YourSQLServer;Initial Catalog=pubs;User ID= " </li>  Place the following segment of code in the Click event of the CommandButton: Private Sub Command1_Click On Error GoTo errproc:

cn.Execute "EXEC INSTEST 'E001','aaaa'" Exit Sub

errproc: Dim merr As ADODB.Error For Each merr In cn.Errors MsgBox merr.Number & " - " & merr.Description Next End Sub The preceding code attempts to insert a row into the TEST table by using the INSTEST SP that would create a duplicate entry in the primary key column [Empno] if added. This causes the RAISERROR statement in the SP that returns the user-defined error message to be executed.

</li> When you run the project and click on the Execute INSTESTCommandButton, you would expect to see the user-defined message Invalid Insert that is raised in the SP in the ADO errors collection. However, note that when the NOCOUNT option is turned OFF (the default setting), the ADO Errors collection does not contain the user-defined error message.</li>  Modify the code in the Click event of the CommandButton to insert calls to the SET NOCOUNT ON and SET NOCOUNT OFF statements as shown here: Private Sub Command1_Click On Error GoTo errproc:

cn.Execute "SET NOCOUNT ON" cn.Execute "EXEC INSTEST 'E001','aaaa'" cn.Execute "SET NOCOUNT OFF"

Exit Sub

errproc: Dim merr As ADODB.Error For Each merr In cn.Errors MsgBox merr.Number & " - " & merr.Description Next cn.Execute "SET NOCOUNT OFF" End Sub </li> Note that the NOCOUNT option is being turned ON prior to executing the SP. It is being turned OFF immediately after executing the SP and in the error handler to ensure that the setting for this option is restored to its default value (assuming that it is OFF) irrespective of whether an error occurs while executing the INSTEST SP.</li> Run the project and click the Execute INSTEST button. Note that the user-defined message Invalid Insert is listed in the Connection object's ADO Errors collection.NOTE: If the NOCOUNT option has been turned ON in your database, then all user-defined error messages raised in SPs by using the RAISERROR statement are populated in the ADO Errors collection when you use the SQLOLEDB Provider.</li></ol>

Keywords: kbprb KB254304

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.