Board index » delphi » Accessing PRINTed output in SQL7 stored procs

Accessing PRINTed output in SQL7 stored procs

Considering the following stored procedure on SQL Server 7:

CREATE PROCEDURE PrintTest AS
  print 'This is a test message on ' + convert(varchar(32), getdate())
  return (0)

My ADO client application accesses the printed message output during
execution of the above SP.  It does this via the ADOConnection Errors
collection (PRINT output is treated as a severtity level 0 SQL error)
provided I run the SP using an ADOCommand object and set the ExecuteOptions
to [eoExecuteNoRecords]. However, as soon as my procedure becomes this:

CREATE PROCEDURE PrintTest AS
  select "Hello world"
  print 'This is a test message on ' + convert(varchar(32), getdate())
  return (0)

the Errors collection is no longer populated with the PRINT output.  I guess
the fact that a result set is being returned affects things, but can anyone
suggest a way of accessing PRINTed output from an SP called from ADO?

TIA

John O'Connell

 

Re:Accessing PRINTed output in SQL7 stored procs


Interesting. I tried similar and found out that only the first PRINT
statement of many is captured in the ADOConnection.Errors. My SP looks like
this:

CREATE PROCEDURE a111 AS
    CREATE TABLE a222 (b222 INT NOT NULL, c222 DATETIME NULL)
    PRINT 'Created table a222'
    INSERT INTO a222 VALUES (1, GETDATE())
    PRINT 'Inserted 1 and current date into table a222'
    DROP TABLE a222
    PRINT 'Dropped table a222'
GO

Alex

Quote
"John O'Connell" <ltj__bu...@hotmail.com> wrote in message

news:3a34d579_2@dnews...
Quote
> My ADO client application accesses the printed message output during
> execution of the above SP.  It does this via the ADOConnection Errors
> collection (PRINT output is treated as a severtity level 0 SQL error)
> provided I run the SP using an ADOCommand object and set the
ExecuteOptions
> to [eoExecuteNoRecords]. However, as soon as my procedure becomes this:

Re:Accessing PRINTed output in SQL7 stored procs


You're right.  I've just tried it.  The technique I used was taken from a
Borland developer support TI sheet (from
http://community.borland.com/delphi) and funnily enough, the example uses
just one print statement.

I've worked around the problem by writing my print messages to a table which
my ADO application then reads and empties when done.  Seems to me to be the
only solution for capturing print statements.

-John

Quote
"Alexei Malmyguine" <alex...@usa.net> wrote in message

news:3a351352$1_2@dnews...
Quote
> Interesting. I tried similar and found out that only the first PRINT
> statement of many is captured in the ADOConnection.Errors. My SP looks
like
> this:

> CREATE PROCEDURE a111 AS
>     CREATE TABLE a222 (b222 INT NOT NULL, c222 DATETIME NULL)
>     PRINT 'Created table a222'
>     INSERT INTO a222 VALUES (1, GETDATE())
>     PRINT 'Inserted 1 and current date into table a222'
>     DROP TABLE a222
>     PRINT 'Dropped table a222'
> GO

> Alex

> "John O'Connell" <ltj__bu...@hotmail.com> wrote in message
> news:3a34d579_2@dnews...
> > My ADO client application accesses the printed message output during
> > execution of the above SP.  It does this via the ADOConnection Errors
> > collection (PRINT output is treated as a severtity level 0 SQL error)
> > provided I run the SP using an ADOCommand object and set the
> ExecuteOptions
> > to [eoExecuteNoRecords]. However, as soon as my procedure becomes this:

Other Threads