Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Logging Raiserror log messages into a table Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2011 7:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
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
Post #1074766
Posted Tuesday, March 8, 2011 8:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 25, 2013 12:01 PM
Points: 191, Visits: 223
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).
Post #1074879
Posted Thursday, May 2, 2013 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 9:49 AM
Points: 15, 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
Post #1448863
Posted Thursday, May 2, 2013 11:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:37 PM
Points: 4,389, Visits: 9,522
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1448929
Posted Friday, May 3, 2013 10:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
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
Post #1449296
Posted Friday, May 3, 2013 10:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
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
Post #1449300
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse