Capture PRINT messages from TSQL statements

[kaffenils]

I have installed the XPSMTP extended procedure from http://www.sqldev.net/xp/xpsmtp.htm.
This procedure returns 0 if an error occurs and 1 of execution is successful.
If I execute this procedure in QA it prints any errors in clear text in the Messages tab.

My question is: When I use this extended procedure in one of my own sprocs, is there any way to capture the text that QA prints in Messages? I want to log this message in a table.

The same question applies for procedures the uses the PRINT function. Is it possible for the calling TSQL to capture the PRINTed text?

Thanks in advanced!

[szlamany]

I remember going around and around in circles trying to get this to work two years ago.

Those PRINT messages should be in the ERRORS collection (I don't have SQL or VB on this box, so I cannot check that).

But something in my memory tells me that you can't process the ADO ERRORS collection until you are done with any RECORDSET collection processing.

[kaffenils]

So it means that reading PRINTed text with TSQL is impossible?

[szlamany]

It can't be impossible - as QA does it...

I found this MSDN link - maybe it will help.

http://msdn.microsoft.com/library/de...tml/vb03k4.asp

If you get it to work, post back what you did as I would like to know for future reference.

[kaffenils]

It can't be impossible - as QA does it...

I found this MSDN link - maybe it will help.

http://msdn.microsoft.com/library/de...tml/vb03k4.asp

If you get it to work, post back what you did as I would like to know for future reference.

Thanks for the link, but I just covers how the read the messages using ADO.
I'll keep you informed on any progress.

[kaffenils]

I got an answer from another forum (blasphemy ). I wasn't exactly the answer I was hoping for, but it works .... in a way ... even if it means breaking a lot of my personal "best practices".

Anyway, here is the solution

CREATE TABLE #foo (
  x VARCHAR(1024)
)

INSERT INTO #foo (x)
   EXECUTE master.dbo.xp_cmdshell 'OSQL -E -Q"EXECUTE sp_smtp_sendmail @parameters...."'

SELECT * FROM #foo