February 22, 2011 at 4:47 am
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 ?
February 22, 2011 at 4:59 am
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
February 22, 2011 at 5:43 am
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 ??
February 22, 2011 at 6:47 am
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
February 22, 2011 at 7:03 am
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
February 22, 2011 at 8:07 am
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.
February 22, 2011 at 12:10 pm
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
March 8, 2011 at 12:59 am
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 ??
March 8, 2011 at 1:52 am
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
March 8, 2011 at 2:42 am
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.
March 8, 2011 at 7:08 am
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
March 8, 2011 at 8:58 am
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).
May 2, 2013 at 9:38 am
I was wondering if you would be willing to share the relevant portions of your code to do this RAISERROR logging?
Thanks in advance
May 2, 2013 at 11:37 am
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
May 3, 2013 at 10:37 am
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