Cosuming PRINT output of a SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cosuming PRINT output of a SP

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 09:33:16

Hi all,

Does anyone have a clever way of consuming the output of PRINT statements from a sp? (In process)

Say I have a script and it calls a stored proc and that proc spits out a message (using PRINT)

is there any way I can consume that message into a variable?

Ideally if there was a way I could pipe the output from the sp into a VARCHAR(MAX) or something...... Can't find much online about it.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-19 : 09:54:25

maybe using dbcc outputbuffer?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-19 : 11:14:01

If you are talking about doing it within SQL, I don't know of a way. If you are trying to do it outside of SQL things get easier. Here are a few links that may or maynot help:

http://blogs.msdn.com/b/mattm/archive/2007/09/13/capture-print-messages-from-a-stored-procedure.aspx
http://sqlskills.com/blogs/jonathan/post/Capturing-InfoMessage-Output-(PRINT-RAISERROR)-from-SQL-Server-using-PowerShell.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 11:29:16

You I'm trying to do it inside sql.... I *could* write an ado.net wrapper but I really don't want to have to.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 11:29:58

I guess I could write a clr function that calls the sp and does something with the output....

gahhh!

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-19 : 12:01:29

dbcc outputbuffer shoould give it but there is a limit to how much data it holds and you will probably want to reformat it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 12:09:41

I don't think I can call DBCC OUTPUTBUFFER.

What I'm trying to do is (inside the same batch)

1) Call a sp
2) Trap the results of that sp.....

I think I'd need to have some sort of watcher polling outputbuffer on it to use given this context..

Looks like I should be doing this outside the db..

Or just change a spiderweb of 3rd party sps to do some sort of sensible logging.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 12:19:32

ah no I see

I can call the sp and then call OUTPUTBUFFER on my own process.....

ok....

So doing something like *this* is possible



USE tempdb
GO

CREATE PROCEDURE foo
AS BEGIN
	PRINT 'Hi There World. I''m a completely annoying message that is vital somehow and you don''t know it yet. HAHAHA'
END
GO

And then in another batch:


EXEC foo
DECLARE @output TABLE ([Buffer] VARCHAR(MAX))
INSERT @output
EXEC ('DBCC OUTPUTBUFFER(@@SPID)')

SELECT * FROM @output

Will produce some ~~nice~~ stuff


(256 row(s) affected)
Buffer
------------------------------------------------------------------------------
00000000   04 01 00 d5 00 65 07 00 ab 22 01 00 00 00 00 01   ...Õ.e..«"......
00000010   00 69 00 48 00 69 00 20 00 54 00 68 00 65 00 72   .i.H.i. .T.h.e.r
00000020   00 65 00 20 00 57 00 6f 00 72 00 6c 00 64 00 2e   .e. .W.o.r.l.d..
00000030   00 20 00 49 00 27 00 6d 00 20 00 61 00 20 00 63   . .I.'.m. .a. .c
00000040   00 6f 00 6d 00 70 00 6c 00 65 00 74 00 65 00 6c   .o.m.p.l.e.t.e.l
00000050   00 79 00 20 00 61 00 6e 00 6e 00 6f 00 79 00 69   .y. .a.n.n.o.y.i
00000060   00 6e 00 67 00 20 00 6d 00 65 00 73 00 73 00 61   .n.g. .m.e.s.s.a
00000070   00 67 00 65 00 20 00 74 00 68 00 61 00 74 00 20   .g.e. .t.h.a.t. 
00000080   00 69 00 73 00 20 00 76 00 69 00 74 00 61 00 6c   .i.s. .v.i.t.a.l
00000090   00 20 00 73 00 6f 00 6d 00 65 00 68 00 6f 00 77   . .s.o.m.e.h.o.w
000000a0   00 20 00 61 00 6e 00 64 00 20 00 79 00 6f 00 75   . .a.n.d. .y.o.u
000000b0   00 20 00 64 00 6f 00 6e 00 27 00 74 00 20 00 6b   . .d.o.n.'.t. .k
000000c0   00 6e 00 6f 00 77 00 20 00 69 00 74 00 20 00 79   .n.o.w. .i.t. .y
000000d0   00 65 00 74 00 2e 00 20 00 48 00 41 00 48 00 41   .e.t... .H.A.H.A
000000e0   00 48 00 41 00 1e 53 00 4b 00 59 00 53 00 43 00   .H.A.

Cheers Nigel.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 12:20:28

you know this may actually be the weirdest hack I've seen with sqlserver yet. Grats!

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-19 : 12:21:10

As long as the spid is available dbcc outputbuffer shoould get the result of the sp.

Not saying it's a good solution though - I've only implemented it once. For a critical task - and that was to get whatever info I could to troubleshoot errors.

try this
create proc test
as
print 'hello there'
go

exec test
dbcc outputbuffer (@@spid)

Would only use it in desperation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-07-19 : 12:49:04

quote:

Originally posted by Transact Charlie



USE tempdb
GO

CREATE PROCEDURE foo
AS BEGIN
	PRINT 'Hi There World. I''m a completely annoying message that is vital somehow and you don''t know it yet. HAHAHA'
END
GO

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

You really should not put in actual production code as an example like that

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources