Microsoft KB Archive/294178

= BUG: T-SQL PRINT Statement May Not Show as Informational Error =

Article ID: 294178

Article Last Modified on 7/15/2004

-

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 ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
 * Microsoft Visual Basic 6.0 Professional Edition

-



This article was previously published under Q294178



SYMPTOMS
You can use Microsoft SQL Server's PRINT statement in stored procedures to return messages as informational errors in ADO, OLE DB, and ODBC applications. However, a Visual Basic client application may not capture such informational messages when it sets up a DataEnvironment command and uses the DataEnvironment.CommandName syntax to run the stored procedure.



CAUSE
This problem has its roots in ADO. The InfoMessage event of an ADO Connection object does not fire when its CursorLocation property is set to adUseClient. By default, the CursorLocation property of Visual Basic 6.0 DataEnvironment Connection objects is set to adUseClient. As a result, the InfoMessage event procedure of DataEnvironment Connection objects does not fire when informational messages are returned to the client application.



RESOLUTION
There are two ways to work around this problem:
 * Set the CursorLocation property of the DataEnvironment Connection object to adUseServer, and use an ADO Recordset object to run the DataEnvironment Command setup, which runs the SQL Server stored procedure. When you use the DataEnvironment.CommandName syntax to run the command, the InfoMessage event is not raised, even if the DataEnvironment Connection object's CursorLocation property is set to adUseServer.
 * Use straight ADO code to run the stored procedure. When you use straight ADO code, make sure that the CursorLocation of the ADO Connection object is not set to adUseClient.

See the &quot;More Information&quot; section for sample code that illustrates these two workarounds.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The Connection object in ADO versions 2.0 and later exposes programmable event procedures that can be implemented in client applications to identify the occurrence of specific events during the lifetime of an active ADO connection that is used to access a data source. The InfoMessage event of the ADO Connection object fires whenever an operation that uses the connection completes successfully, and the OLE DB Provider that is used to establish the connection returns additional information such as a warning. Messages that are returned to ADO are exposed as errors if their severity level is greater than 10; they are exposed as informational messages if their severity level is 10 or less.

Steps to Reproduce Behavior
  Create the following stored procedure in one of your SQL Server databases: Create Procedure sp_Print As PRINT &quot;Hello World&quot;  Open a new Visual Basic Standard EXE project, and set a reference to the Microsoft ActiveX Data Objects Library 2.0 or later. Place two command buttons on the form, and rename them &quot;cmdSolution&quot; and &quot;cmdProblem&quot;. Add a DataEnvironment designer instance to the project, and rename it &quot;DE1&quot;. Add a connection to &quot;DE1&quot;, and rename it &quot;CN1&quot;. Configure the connection to point to the SQL Server database in which you created the sp_Print stored procedure in Step 1.</li> Add a command to &quot;CN1&quot; and rename it &quot;sp_Print&quot;.</li> On the General tab in the Properties window, set Database Object to &quot;Stored Procedure&quot; and Object Name to &quot;dbo.sp_Print&quot;.</li>  Copy and paste the following code in the code window for &quot;DE1&quot;: Private Sub CN1_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _                   ByVal pConnection As ADODB.Connection) MsgBox pError.Description End Sub </li>  Copy and paste the following code in the code window for the default form.

Note You must change User ID =<UID> and password = to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database. Private WithEvents cn As ADODB.Connection

Private Sub cmdSolution_Click Dim cmd As New ADODB.Command

Set cn = New ADODB.Connection ' Make necessary changes to point to your server and database. cn.Open &quot;Provider=SQLOLEDB;Data Source=<Name of your SQL Server>; _ Initial Catalog=<Database name>;User ID= ;password= &quot; cmd.CommandType = adCmdStoredProc cmd.CommandText = &quot;sp_Print&quot; Set cmd.ActiveConnection = cn   cmd.Execute Set cmd = Nothing cn.Close Set cn = Nothing End Sub

Private Sub cn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _                  ByVal pConnection As ADODB.Connection) MsgBox pError.Description End Sub

Private Sub cmdProblem_Click DE1.sp_Print End Sub

Private Sub Form_Load cmdSolution.Caption = &quot;Solution&quot; cmdProblem.Caption = &quot;Problem&quot; End Sub </li> Run the project, and click Solution. Notice that the PRINT statement in the sp_Print stored procedure returns a message box with the informational message &quot;Hello World&quot;. Click OK to dismiss the message box.</li> Click Problem. Notice that no message box appears.</li> Stop the execution of the project.</li></ol>

Workaround
<ol> In the Properties window, set the CursorLocation property of the DataEnvironment Connection object &quot;CN1&quot; to adUseServer.</li>  Replace the code in the cmdProblem_Click event procedure with the following statements: cmdProblem.Caption = &quot;No longer a problem !&quot; Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open DE1.Commands(&quot;sp_Print&quot;) </li> Run the project, and click Problem. Notice that the PRINT statement in the sp_Print stored procedure returns a message box with the informational message as expected.</li></ol>

<div class="references_section">