Logging Raiserror log messages into a table

  • Hi,

    I have several raiserror statements (all informational messages ) inside my stored procedure, Can I have them logged into a table automatically when the stored procedure is executed ?

  • BEGIN TRY

    RaisError...

    END TRY

    BEGIN CATCH

    INSERT [Your Table]

    SELECT

    ERROR_NUMBER() AS ErrorNumber,

    ERROR_SEVERITY() AS ErrorSeverity,

    ERROR_STATE() AS ErrorState,

    ERROR_PROCEDURE() AS ErrorProcedure,

    ERROR_LINE() AS ErrorLine,

    ERROR_MESSAGE() AS ErrorMessage

    END CATCH

  • Thanks,

    The scenario is, I have thousands of stored procedure which have been written with raiserror already. All these days we were having the front end (C++) handling this logging into the table during the stored procedure execution, now that was going to be retired and all the stored procedure execution going to take place only through the SQL jobs. In that case can we handle those logging into the table through the SQL Server ??

  • put procedure execution into job's step

    /*

    CREATE PROCEDURE test

    AS SELECT 1/0

    GO

    */

    BEGIN TRY

    EXEC test

    END TRY

    BEGIN CATCH

    --INSERT [Your Table]

    SELECT

    ERROR_NUMBER() AS ErrorNumber,

    ERROR_SEVERITY() AS ErrorSeverity,

    ERROR_STATE() AS ErrorState,

    ERROR_PROCEDURE() AS ErrorProcedure,

    ERROR_LINE() AS ErrorLine,

    ERROR_MESSAGE() AS ErrorMessage

    END CATCH

  • another way

    put into job (named 'test' ) step procedure execution only

    DECLARE

    @job_id uniqueidentifier

    SELECT

    @job_id = sv.job_id

    FROM

    msdb.dbo.sysjobs_view AS sv

    where name ='test'

    ORDER BY

    [Name] ASC

    declare @tmp_sp_help_jobhistory table

    (

    instance_id int null,

    job_id uniqueidentifier null,

    job_name sysname null,

    step_id int null,

    step_name sysname null,

    sql_message_id int null,

    sql_severity int null,

    message nvarchar(4000) null,

    run_status int null,

    run_date int null,

    run_time int null,

    run_duration int null,

    operator_emailed sysname null,

    operator_netsent sysname null,

    operator_paged sysname null,

    retries_attempted int null,

    server sysname null

    )

    insert into @tmp_sp_help_jobhistory

    exec msdb.dbo.sp_help_jobhistory

    @job_id = @job_id

    @mode='FULL'

    SELECT

    tshj.instance_id AS [InstanceID],

    tshj.sql_message_id AS [SqlMessageID],

    tshj.message AS [Message],

    tshj.step_id AS [StepID],

    tshj.step_name AS [StepName],

    tshj.sql_severity AS [SqlSeverity],

    tshj.job_id AS [JobID],

    tshj.job_name AS [JobName],

    tshj.run_status AS [RunStatus],

    CASE tshj.run_date WHEN 0 THEN NULL ELSE

    convert(datetime,

    stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +

    stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),

    120) END AS [RunDate],

    tshj.run_duration AS [RunDuration],

    tshj.operator_emailed AS [OperatorEmailed],

    tshj.operator_netsent AS [OperatorNetsent],

    tshj.operator_paged AS [OperatorPaged],

    tshj.retries_attempted AS [RetriesAttempted],

    tshj.server AS [Server],

    getdate() as [CurrentDate]

    FROM @tmp_sp_help_jobhistory as tshj

    ORDER BY [InstanceID] ASC

  • I would suggest that you also wrap the logging into another stored procedure. That way you can reuse it over & over in your code.

    Plus if you ever need to make any changes to the how the logging is handled (e.g. change of schema or table), you can change it once & know that the change will be applied universally.

  • You said are going to put all the proc executions into SQL Server Agent Jobs so I would recommend looking into the logging functionality available within SQL Server Agent. Within each job step you can instruct SQL Agent to log all output to a table. Sending the output to a table will result in all your informational RAISERROR output being stored in the table msdb.dbo.sysjobsteplogs. You can instruct SQL Agent to replace the output with each step execution or to append it to the output from all previous executions. To see the options go to the "Advanced" tab of the "Job Step Properties" dialog. There you'll see the "Log to table" check and the "Append to existing entry in table" checkbox.

    My homegrown solution: I like the 'log to table' option since I can then draw that information into reports or use it in an email when a job fails. In my own systems I like to install a stored proc I wrote into msdb. My proc accepts a job_id and emails the log message from the table msdb.dbo.sysjobsteplogs for the last failed step of the job. I then add a new step to all jobs that will call my proc and pass the job_id of the job using SQL Agent tokens. On failure, all other steps go to this new last step. The second to last step of the job ends the job successfully On Success. In other words the only way to reach the last step that emails the logs is if a step fails.

    Some things to think about before implementing SQL Agent logging:

    1) Ideally you'll want to set the step so the new step executions replace, not append, the previous execution's output. This is especially important if you have chatty stored procedures.

    2) Evaluate whether you'll need to provision extra space for msdb and also think about moving it to a fast disk sub-system...especially if you have many chatty procs.

    3) The column that stores the output is msdb.dbo.sysjobsteplogs.log which is NVARCHAR(MAX). If using the append method, as the column size grows into the hundreds of megabytes, or worse yet gigabytes, it becomes a major chore to retrieve the data and hogs valuable disk and network resources. This is also why I recommend having your jobs email the information and let Agent replace the previous output.

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

  • 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 ??

  • 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 ??

    My personal preference would be to add the error handing into the Stored procedure, rather than relying on SQL Agent logging as it would make no difference as to what or who ran the stored procedure.

    The other suggestion about a global error handler proc is a good one as you get a lot of code reuse, and you can add additional logic to enrich the data, such as adding an ErrorType based on the Severity, also dont forget to log the Stored proc Name

    You can even log the paramaters by casting them as an XML string in the CATCH and passing them through to the procedure. That way you can replicate conditions that may have caused the stored proc to fail.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • All the Raiserror statements inside the stored procedures are for informational messages and their severity is set to 0. So they are not getting caught inside the TRY....CATCH block.

  • 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

  • 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).

  • I was wondering if you would be willing to share the relevant portions of your code to do this RAISERROR logging?

    Thanks in advance

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

  • 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

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

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