BUG: T-SQL PRINT Statement May Not Show as Informational Error (294178)

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

1. Create the following stored procedure in one of your SQL Server databases:

   Create Procedure sp_Print As
   PRINT "Hello World"
   					

2. Open a new Visual Basic Standard EXE project, and set a reference to the Microsoft ActiveX Data Objects Library 2.0 or later.
3. Place two command buttons on the form, and rename them "cmdSolution" and "cmdProblem".
4. Add a DataEnvironment designer instance to the project, and rename it "DE1".
5. Add a connection to "DE1", and rename it "CN1". Configure the connection to point to the SQL Server database in which you created the sp_Print stored procedure in Step 1.
6. Add a command to "CN1" and rename it "sp_Print".
7. On the General tab in the Properties window, set Database Object to "Stored Procedure" and Object Name to "dbo.sp_Print".
8. Copy and paste the following code in the code window for "DE1":

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

9. Copy and paste the following code in the code window for the default form.
   
   Note You must change User ID =<UID> and password =<strong 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 "Provider=SQLOLEDB;Data Source=<Name of your SQL Server>; _
            Initial Catalog=<Database name>;User ID=<uid>;password=<strong password>"
       cmd.CommandType = adCmdStoredProc
       cmd.CommandText = "sp_Print"
       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 = "Solution"
       cmdProblem.Caption = "Problem"
   End Sub
   					

10. 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 "Hello World". Click OK to dismiss the message box.
11. Click Problem. Notice that no message box appears.
12. Stop the execution of the project.

Workaround

1. In the Properties window, set the CursorLocation property of the DataEnvironment Connection object "CN1" to adUseServer.
2. Replace the code in the cmdProblem_Click() event procedure with the following statements:

   cmdProblem.Caption = "No longer a problem !"
   Dim rs As ADODB.Recordset
   Set rs = New ADODB.Recordset
   rs.Open DE1.Commands("sp_Print")
   					

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