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, February 22, 2011 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114
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 ?
Post #1067505
Posted Tuesday, February 22, 2011 4:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 27, 2011 4:42 AM
Points: 14, Visits: 2,437
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

Post #1067510
Posted Tuesday, February 22, 2011 5:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114
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 ??
Post #1067547
Posted Tuesday, February 22, 2011 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 27, 2011 4:42 AM
Points: 14, Visits: 2,437
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

Post #1067577
Posted Tuesday, February 22, 2011 7:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 27, 2011 4:42 AM
Points: 14, Visits: 2,437
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

Post #1067590
Posted Tuesday, February 22, 2011 8:07 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
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.
Post #1067628
Posted Tuesday, February 22, 2011 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1067818
Posted Tuesday, March 08, 2011 12:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114
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 ??
Post #1074623
Posted Tuesday, March 08, 2011 1:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
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
Post #1074639
Posted Tuesday, March 08, 2011 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114
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.
Post #1074649
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse