Capture PRINT messages from TSQL statements
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!
Re: Capture PRINT messages from TSQL statements
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.
Re: Capture PRINT messages from TSQL statements
So it means that reading PRINTed text with TSQL is impossible? :eek2:
Re: Capture PRINT messages from TSQL statements
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.
Re: Capture PRINT messages from TSQL statements
Quote:
Originally Posted by szlamany
Thanks for the link, but I just covers how the read the messages using ADO.
I'll keep you informed on any progress.
Re: Capture PRINT messages from TSQL statements
I got an answer from another forum (blasphemy :blush:). 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
Code:
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