Capture T-SQL PRINT output in ADO?

Question

Is it possible to capture PRINT output from a T-SQL in ADO?

For example:

PRINT 'Before the thing'
SELECT * FROM sys.tables
PRINT 'After the thing'

Is it possible in ADO to receive these PRINT messages?

I tried connecting to the InfoMessage event, but it is never fired. (although ExecuteComplete is; which is nice)

ConnectionEvents

For your convenience here are all the events exposed by an ADO Connection object:

[InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
[Guid("00000402-0000-0010-8000-00AA006D2EA4")]
public interface IConnectionEvents
{
   void InfoMessage(Error pError, ref EventStatusEnum, Connection, pConnection);
   void BeginTransComplete(Integer TransactionLevel, Error pError, ref EventStatusEnum adStatus, Connection pConnection);
   void CommitTransComplete(Error pError, ref EventStatusEnum adStatus, Connection pConnection);
   void RollbackTransComplete(Error pError, ref EventStatusEnum adStatus, Connection pConnection);
   void WillExecute(ref string Source, ref CursorTypeEnum CursorType, ref LockTypeEnum LockType, ref Integer Options, ref EventStatusEnum adStatus, Command pCommand, _Recordset pRecordset, Connection pConnection);
   void ExecuteComplete(Integer RecordsAffected, Error pError, ref adStatus EventStatusEnum, Command pCommand, Recordset pRecordset, Connection pConnection);
   void WillConnect(ref string ConnectionString, ref string UserID, ref string Password, ref Integer Options, ref EventStatusEnum adStatus, Connection pConnection);
   void ConnectComplete(Error pError, ref EventStatusEnum adStatus, Connection pConnection);
   void Disconnect(ref EventStatusEnum adStatus, Connection pConnection);
}

PRINT results not reported through Errors

The result of PRINT statements do not appear in the Errors collection after the T-SQL completes.

Which is good, because i need the feedback real-time; not after the entire batch completes.

Pseudocode

Connection conn = new Connection();
conn.ConnectionString = connectionString;
conn.Open();

//Hook up events listener
ConnectionEvents ev = new ConnectionEvents();
Integer cookie = (conn as IConnectionPointContainer).FindConnectionPoint(DIID_ConnectionEvents).Advise(ev);

//run our sql batch
conn.Execute(sql, out recordsAffected, adCmdText);

//Disconnect events
(conn as IConnectionPointContainer).FindConnectionPoint(DIID_ConnectionEvents).Unadvise(cookie);

And the magic of COM is that you create an object to receive all the events:

public class ConnectionEvents: IConnectionEvents
{
   void InfoMessage(Error pError, ref EventStatusEnum, Connection, pConnection) 
   {
      Debugger.Break();
   }
   void BeginTransComplete(Integer TransactionLevel, Error pError, ref EventStatusEnum adStatus, Connection pConnection) {}
   void CommitTransComplete(Error pError, ref EventStatusEnum adStatus, Connection pConnection) {}
   void RollbackTransComplete(Error pError, ref EventStatusEnum adStatus, Connection pConnection) {}
   void WillExecute(ref string Source, ref CursorTypeEnum CursorType, ref LockTypeEnum LockType, ref Integer Options, ref EventStatusEnum adStatus, Command pCommand, _Recordset pRecordset, Connection pConnection) {}
   void ExecuteComplete(Integer RecordsAffected, Error pError, ref adStatus EventStatusEnum, Command pCommand, Recordset pRecordset, Connection pConnection) 
   {
      Debugger.Break();
   }
   void WillConnect(ref string ConnectionString, ref string UserID, ref string Password, ref Integer Options, ref EventStatusEnum adStatus, Connection pConnection) {}
   void ConnectComplete(Error pError, ref EventStatusEnum adStatus, Connection pConnection) {}
   void Disconnect(ref EventStatusEnum adStatus, Connection pConnection) {}
}

SQLOLEDB Provider keywords

I wondered if maybe there was a ConnectionString keyword that would enable messages.

Fortunately, you can ask an OLEDB provider all the connection string keywords it supports. Your OLEDB provider is an IDataInitialize object. Cast it to IDBProperties, and you can interrogate it for all the properties it supports:

• Property Set DBPROPSET_DBINIT

  • DBPROP_AUTH_INTEGRATED: "Integrated Security": VT_BSTR
  • DBPROP_AUTH_PASSWORD: "Password": VT_BSTR
  • DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO: "Persist Security Info": VT_BOOL
  • DBPROP_AUTH_USERID: "User ID": VT_BSTR
  • DBPROP_INIT_CATALOG: "Initial Catalog": VT_BSTR
  • DBPROP_INIT_DATASOURCE: "Data Source": VT_BSTR
  • DBPROP_INIT_HWND: "Window Handle": VT_I4
  • DBPROP_INIT_LCID: "Locale Identifier": VT_I4
  • DBPROP_INIT_PROMPT: "Prompt": VT_I2
  • DBPROP_INIT_PROVIDERSTRING: "Extended Properties": VT_BSTR
  • DBPROP_INIT_TIMEOUT: "Connect Timeout": VT_I4
  • DBPROP_INIT_GENERALTIMEOUT: "General Timeout": VT_I4
  • DBPROP_INIT_OLEDBSERVICES: "OLE DB Services": VT_I4

• Property Set DBPROPSET_SQLSERVERDBINIT

  • SSPROP_INIT_CURRENTLANGUAGE: "Current Language": VT_BSTR
  • SSPROP_INIT_NETWORKADDRESS: "Network Address": VT_BSTR
  • SSPROP_INIT_NETWORKLIBRARY: "Network Library": VT_BSTR
  • SSPROP_INIT_USEPROCFORPREP: "Use Procedure for Prepare": VT_I4
  • SSPROP_INIT_AUTOTRANSLATE: "Auto Translate": VT_BOOL
  • SSPROP_INIT_PACKETSIZE: "Packet Size": VT_I4
  • SSPROP_INIT_APPNAME: "Application Name": VT_BSTR
  • SSPROP_INIT_WSID: "Workstation ID": VT_BSTR
  • SSPROP_INIT_FILENAME: "Initial File Name": VT_BSTR
  • SSPROP_INIT_ENCRYPT: "Use Encryption for Data": VT_BOOL
  • SSPROP_AUTH_REPL_SERVER_NAME: "Replication server name connect option": VT_BSTR
  • SSPROP_INIT_TAGCOLUMNCOLLATION: "Tag with column collation when possible": VT_BOOL

None seem related to opting-in to extra messages.

Must be possible, Query Analyzer does it.

Bonus Reading

• Capture Stored Procedure print output in .NET
• Capture PRINT messages from a stored procedure