Visual Basic Q&A

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

Symptoms
When you use the ActiveX Data Objects (ADO) Connection object in Microsoft Visual Basic .NET or Microsoft Visual Basic 2005 to run a SQL query that is associated with the ADODB.Command object two times consecutively, you may receive the following error message similar to:

An unhandled exception of type ‘System.Runtime.InteropServices.COMException’ occurred in microsoft.visualbasic.dll
Additional information: Syntax error or access violation
Resolution
When you use the ADODB.Connection object to run a SQL query that is associated with the ADODB.Command object, late binding is used. The error that is mentioned in the “Symptoms” section occurs because late binding the COM interop caches the results of the GetIDsOfNames method of the IDispatch Interface.

Symptoms
If multiple stored procedures are executed when using ADO 2.0, the T-SQL Debugger automaticallystarts for the first stored procedure, but does not automatically startupon executing the second or subsequent stored procedures. The followingerror message displays:

The query could not be debugged due to a problem coordinatingevents with the server. Check the server and client log to find theexact cause, fix the problem and try again.This problem no longer occurs in ADO 2.1 and later when calling simple stored procedures like the pubs..reptq1 procedure that is called in the example code below.However, if you are calling multiple stored procedures that accept parameters, using a single ADO Connection, the T-SQL Debugger does not start automatically when executing the second or subsequent stored procedures.You will either receive the error described above, or it will fail silently.
Resolution
In ADO 2.1 and later, to temporarily work around this while debugging, execute each store procedure on a separate connection. This workaround is illustrated in “Steps to Reproduce Behavior Using ADO 2.1″ that follows.