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

Interview Questions & Answers (.NET & SQLServer)

I have written couple of questions and answer in my blog at BeyondRelational. Here I plan to put all of them together and also in future when ever I will add any questions answers to that blog, I will add a link here. Go through the questions and answers , but don’t think these are complete, you have to read a lot using the reference material and other books. You have to practice a lot and then you will get the answers fundamentally and practically correct for your understanding. So happy reading and please provide feedback for this series. If you find anything wrong, please guide me and tell me the correct answer so that I can verify the same and will update the most appropriate answer.

Till next time Keep smiling :)