Customized success or failure log of the SQL job in the Windows Event Viewer.

This #JustLearned was published in Beyondrelational.com and for other #JustLearned by me check the link provided at the end of this article.

 

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

IF NOT EXISTS (SELECT TOP 1 RUN_DATE FROM MSDB.DBO.SYSJOBHISTORY WHERE JOB_ID=
(SELECT JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=’S_000W’) AND
STEP_ID = 3 AND RUN_STATUS=1 AND
RUN_DATE=CONVERT(VARCHAR , GETDATE(), 112) ORDER BY RUN_DATE DESC)
BEGIN
EXEC XP_LOGEVENT 60000, ‘S_000W: DAILY JOB NOT COMPLETE’, ERROR
END
ELSE
BEGIN EXEC XP_LOGEVENT 60000, ‘S_000W: DAILY JOB COMPLETED’, INFORMATIONAL
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

For other #JustLearned check the below links

SQL Server Tips – Identify running jobs on remote server

Checking the Job Schedule status of a SQL job

Windows Service Installation with a Service User Account

Dotnet – Its possible to generate the assembly of specific platform

Platform specific code in dotnet

Disabling/Enabling SQL Jobs or Job schedule from remote computer

Windows Service with Windows Timer control doesn’t tick

#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