Next, simply create a job on your desktop with whatever schedule you like to run the stored procedure. Once the table has data, it is a simple procedure to define reporting stored procedures to determine the outcome of jobs, average the run time of jobs, report on the history of the jobs, etc.
If you want an automatic email sent to you, just configure SQL Mail on your desktop and create a new job or new job step that uses the xp_sendmail system stored procedure to run a basic query.
EXEC master.dbo.xp_sendmail @recipients = ‘randydyess@transactsql.com’,
@message = ‘Daily Job Report’,
@query = ‘
SELECT status,server, jobname
FROM msdb.dbo.tJobReport
WHERE status = ‘Failed’
AND rundate > DATEADD(hh,-25,GETDATE())’,
@subject = ‘Job Report’,
@attach_results = ‘TRUE’
So, if you have the same bad luck in getting those great tools out there or want a centralized way to keep in control of your job outcomes and history, this simple technique can go along way in helping you quickly manage those hundreds of jobs we all seem to accumulate over time.
You can find out more about sysjobs. sysjobhistory and xp_sendmail in my last book Transact-SQL Language Reference Guide.
Copyright 2003 by Randy Dyess, All Rights Reserved www.TransactSQL.Com
]]>