View output of 'print' statements using ADOConnection in Delphi

Question

Some of my MS SQL stored procedures produce messages using the 'print' command. In my Delphi 2007 application, which connects to MS SQL using TADOConnection, how can I view the output of those 'print' commands?

Key requirements: 1) I can't run the query more than once; it might be updating things. 2) I need to see the 'print' results even if datasets are returned.

Answer 1

That was an interesting one...
The OnInfoMessage event from the ADOConnection works but the devil is in the details!

Main points:

• use CursorLocation = clUseServer instead of the default clUseClient.
• use Open and not ExecProc with your ADOStoredProc.
• use NextRecordset from the current one to get the following, but be sure to check you have one open.
• use SET NOCOUNT = ON in your stored procedure.

SQL side: your stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FG_TEST]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[FG_TEST]
GO
-- =============================================
-- Author:      François
-- Description: test multi ADO with info
-- =============================================
CREATE PROCEDURE FG_TEST
AS
BEGIN
    -- SET NOCOUNT ON absolutely NEEDED
    SET NOCOUNT ON;

    PRINT '*** start ***'

    SELECT 'one' as Set1Field1

    PRINT '*** done once ***'

    SELECT 'two' as Set2Field2

    PRINT '*** done again ***'

    SELECT 'three' as Set3Field3

    PRINT '***finish ***'
END
GO

Delphi side:
Create a new VCL Forms Application.
Put a Memo and a Button in your Form.
Copy the following text, change the Catalog and Data Source and paste it onto your form.

object ADOConnection1: TADOConnection
  ConnectionString = 
    'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
    'fo=False;Initial Catalog=xxxYOURxxxDBxxx;Data Source=xxxYOURxxxSERVERxxx'
  CursorLocation = clUseServer
  LoginPrompt = False
  Provider = 'SQLOLEDB.1'
  OnInfoMessage = ADOConnection1InfoMessage
  Left = 24
  Top = 216
end
object ADOStoredProc1: TADOStoredProc
  Connection = ADOConnection1
  CursorLocation = clUseServer
  ProcedureName = 'FG_TEST;1'
  Parameters = <>
  Left = 24
  Top = 264
end

In the OnInfoMessage of the ADOConnection put:

Memo1.Lines.Add(Error.Description);

For the ButtonClick, paste this code:

procedure TForm1.Button1Click(Sender: TObject);
const
  adStateOpen = $00000001; // or defined in ADOInt
var
  I: Integer;
  ARecordSet: _Recordset;
begin
  Memo1.Lines.Add('==========================');

  ADOStoredProc1.Open; // not ExecProc !!!!!

  ARecordSet := ADOStoredProc1.Recordset;
  while Assigned(ARecordSet) do
  begin
    // do whatever with current RecordSet
    while not ADOStoredProc1.Eof do
    begin
      Memo1.Lines.Add(ADOStoredProc1.Fields[0].FieldName + ': ' + ADOStoredProc1.Fields[0].Value);
      ADOStoredProc1.Next;
    end;
    // switch to subsequent RecordSet if any
    ARecordSet := ADOStoredProc1.NextRecordset(I);
    if Assigned(ARecordSet) and ((ARecordSet.State and adStateOpen) <> 0) then
      ADOStoredProc1.Recordset := ARecordSet
    else
      Break;
  end;

  ADOStoredProc1.Close;
end;