#Just Learned 4 Customised success or failure log of the SQL job in the Windows Event Viewer.

You can check the status of a SQL job and log the customised success or failure message of the job for the day in the Windows Event Viewer.

01.IF NOT EXISTS (SELECT TOP 1 run_date FROM msdb.dbo.sysjobhistory 
02.WHERE job_id=(SELECT job_id FROM msdb.dbo.sysjobs where name='S_000w') AND step_id = 3
03.AND run_status=1 AND run_date=CONVERT(VARCHAR , GETDATE(), 112) ORDER BY run_date DESC)
04.BEGIN 
05.EXEC xp_logevent 60000, 'S_000w: Daily Job not complete', ERROR 
06.END
07.ELSE 
08.BEGIN 
09.EXEC xp_logevent 60000, 'S_000w: Daily Job completed successfully', INFORMATIONAL 
10.END

Usage –xp_logevent { error_number , ‘message’ } [ , ‘severity’ ] For more information –http://msdn.microsoft.com/en-us/library/ms186244.aspx Also check the uses RAISERROR

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s