How to store MSSQL PRINT output to a variable

Question

On a MS SQL 2008 R2, I want to be able to catch the messages output into a variable. I need to run a script on many databases. Catch the message output (messages from print or raiserror) and log it into table. I need to do this from inside of a stored procedure.

For this script

Declare @sqlscript nvarchar(500)

Set @sqlscript = 
'select * from sys.objects
raiserror (''My raised error'', 10,1)
select * from sys.schemas
print ''my print'''

EXEC sp_executesql @sqlscript

I would like to get

My raised error    
my print

or

(60 row(s) affected)  
My raised error

(21 row(s) affected)  
my print

Update
I've decided to go with @rs suggestion. Logging into a table is the easiest way for me. Using a SP to log (to keep the code clean), plus a small regex to refactor all my scripts. The solution will be ready for tomorrow. Thanks a lot.

check @@error, @@rowcount after your exec statement Ex: SELECT @@rowcount — rs.
– rs., Commented May 10, 2012 at 21:01
After the sp_executesql, both the @@error and @@rowcount are at 0, because the last statement is a print. It will only give me info on the last statement and not the entire script executed. — Alexandre Rondeau
– Alexandre Rondeau, Commented May 10, 2012 at 21:12
Can you use insert into table instead of printing? Replace print 'my print' with insert into logtable (msg) values ('print') — rs.
– rs., Commented May 10, 2012 at 21:17
Yes, I could replace prints with a logtable or with a nvarchar(max) variable that I would append. But I have about 75 scripts that I would love not to have to refactor. :D Plus using Print is kindda standard for trace. I can change my habbits, it team that will be more reluctant to change. — Alexandre Rondeau
– Alexandre Rondeau, Commented May 10, 2012 at 21:21
@rs: You should make an answer with your comment so he could accept it. — Gilles
– Gilles, Commented Jun 27, 2012 at 18:16

Cade Roux · Accepted Answer · 2012-05-10 21:38:13Z

2

You could probably use the CLR and a technique similar to this:

http://blogs.msdn.com/b/mattm/archive/2007/09/13/capture-print-messages-from-a-stored-procedure.aspx

But the best option is to probably EXEC into a temporary table which simply has a single varchar column:

http://msdn.microsoft.com/en-us/library/aa175921%28v=sql.80%29.aspx

answered May 10, 2012 at 21:38

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexandre Rondeau Over a year ago

CLR is just not an option, we're hosted on a server that doesn't provide us with the correct credentials to use CLR and the EXEC option I've just tested it but I can't make it work. I can get results out of the Stored Proc, but not the messages.

Collectives™ on Stack Overflow

How to store MSSQL PRINT output to a variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related