Capturing PRINT Messages in a temporary table

Question

Capturing PRINT Messages in a temporary table

satguru.saran

SSC Enthusiast

Points: 177
More actions
January 6, 2008 at 9:59 am

#61485

I have a stored procedure that prints information to the message window of the QA. I want to save the message to a temp table. Is this possible in SQL Server ? Please find my code mentioned below:
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLTEXT = 'EXEC GetPassword ' + '''' + @AccountDomain+ ''''+ ',' + '''' + @AccountName + ''''
INSERT INTO #t1 EXEC (@SQLTEXT)
END
The GetPassword (highlighted above) procedure prints messages in the query analyzer message window. My requirement is to save the message to a temp table as shown above. I have been stuck with this problem since the last 2 days. 🙁 Any help in this regarding is much appreciated.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 740606 More actions · Answer 1

PRINT messages are intended for display and not capture. AFAIK, there is no way to capture this in a table. You'd need to change the procedure to output the messages as a SELECT instead. Or log them in the procedure, maybe with another parameter.

satguru.saran SSC Enthusiast Points: 177 More actions · Answer 2

BUt then I have seen the DBCC messages being captured in the same fashion in some of the examples that I have seen like the one mentioned below:

INSERT INTO #logspace

EXEC ('dbcc sqlperf( logspace)')

Am I missing something here ?

Sergiy SSC Guru Points: 110209 More actions · Answer 3

satguru.saran (1/6/2008)
BUt then I have seen the DBCC messages being captured in the same fashion in some of the examples that I have seen like the one mentioned below:
INSERT INTO #logspace
EXEC ('dbcc sqlperf( logspace)')
Am I missing something here ?

I believe after running this table #logspace contains data returned by dbcc sqlperf( logspace) in Grid tab, not in Messages tab.

Right?

And PRINT returns messages to Messages tab, not to Grid tab where it coud be caught.

_____________
Code for TallyGenerator

Steve Jones - SSC Editor SSC Guru Points: 740606 More actions · Answer 4

You could do a RAISERROR and get some info, but other than that, I've checked with the MVP groups and there's no way to do this with PRINT. Someone did suggest a CLR function.

How much data are you trying to return?