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
aravind-305595
aravind-305595
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 119
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 ?
Victor Dvoryaninov
Victor Dvoryaninov
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 2437

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


aravind-305595
aravind-305595
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 119
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 ??
Victor Dvoryaninov
Victor Dvoryaninov
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 2437
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


Victor Dvoryaninov
Victor Dvoryaninov
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 2437
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


~Alex
~Alex
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 224
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14825 Visits: 14396
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
aravind-305595
aravind-305595
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 119
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 ??
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2059 Visits: 3232
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
aravind-305595
aravind-305595
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 119
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.
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