SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logging Raiserror log messages into a table


Logging Raiserror log messages into a table

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15155 Visits: 14396
aravind-305595 (3/8/2011)
Thanks All, The solutions are working fine. Thanks for your inputs.

Do we have an option incase if we are not scheduling them in SQL job ? I mean if we execute a stored procedure normally in SSMS, can we get those messages and store them in a table ??

Tabular results, errors and and info messages are three different data streams coming out of SQL Server. We take handling tabular results for granted, an INSERT...SELECT statement is an example of handling tabular results in a T-SQL context. We can react to the error stream using TRY/CATCH. To my knowledge however there is no way to react/handle items on the info message stream from within a T-SQL context...it is always output to the client un-touched.

The info messages stream is exposed to all the SQL Server data access drivers which is why SQL Server Agent can capture it, as could any app using the Native Client, OLE DB or ODBC driver. SSMS uses the Native Client to talk to SQL Server.

If you're sending your results to Grid (Press Ctrl+D in your query windows before running a query to switch to Grid results) SSMS displays messages from the error and info streams in the "Messages tab".

If you're sending your results to Text (Press Ctrl+T in your query window before running a query to switch to Text results) SSMS displays all three streams in the "Results tab".

It might be possible to write an SSMS plug-in to capture info messages whenever someone runs a query and then pipe them back into a database table but that would have to be running on everyone's client machine identically to work properly.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
~Alex
~Alex
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 224
What information messages are you looking to log? Do you have any examples?

If you're trying to log every time a process completes successfully, than you could modify your stored procedures to simply write a new entry to the log at the end of the batch.

If you're trying to log data that is returned by the process, you could use return or output variables in your main stored procedures & wrap them into another stored procedure that would handle the logging.

And, of course, if you're trying to log when the process fails, you could use the other suggestions from this topic (try/catch blocks).
SQLspy
SQLspy
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 189
I was wondering if you would be willing to share the relevant portions of your code to do this RAISERROR logging?

Thanks in advance
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7977 Visits: 9971
You need to also be aware that any explicit transactions - with explicit rollbacks will also roll back any of these messages that are being logged.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15155 Visits: 14396
Jeffrey Williams 3188 (5/2/2013)
You need to also be aware that any explicit transactions - with explicit rollbacks will also roll back any of these messages that are being logged.

To clarify, I think this pertains only to inserts into a custom log table from within T-SQL code but not to the setting in SQL Agent I described above that writes messages to msdb.dbo.sysjobsteplogs. SQL Agent will write to the log table whatever is sent to the error or info streams by the code in the job step regardless of the transactions being used in that code.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15155 Visits: 14396
SQLspy (5/2/2013)
I was wondering if you would be willing to share the relevant portions of your code to do this RAISERROR logging?

Thanks in advance

I misread your PM. Sorry, I do not have access to the code I referenced in my earlier post. It would not be hard to recreate though.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search