October 16, 2008 at 5:33 am
Hi,
anybody know how to setup an email to send it to my email account with all Job status which run overnight?
Thanks.
October 16, 2008 at 6:47 am
Something like this:
Declare @query varchar(1000)
Set @query ='
Select
J.name as job_name,
JH.step_name,
Case
When JH.run_status = 1 Then ''Succeeded''
When JH.run_status = 2 Then ''Retry''
When JH.run_status = 3 Then ''Canceled''
When JH.run_status = 4 Then ''Running''
Else ''Failed''
End as status,
JH.run_date,
JH.run_time
From
msdb.dbo.sysjobs J JOin
msdb.dbo.sysjobhistory JH ON
J.job_id = JH.job_id
WHere
run_date >= Convert(Int, Replace(Convert(varchar(10), DateAdd(Day, -1, GetDate()), 102), ''.'', '''')) And
run_time >= Convert(Int, Replace(Convert(varchar(10), DateAdd(Day, -1, GetDate()), 108), '':'', ''''))'
Exec xp_sendmail @recipients = 'your email', @query = @query
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 7:06 am
How about a .vbs script that collects the failed jobs to a text file?
MakeLogNonTrusted "UntrustedServer", "sa", "password"
MakeLogTrusted "MyTrustedServer"
sub MakeLogTrusted (ServerName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Driver={SQL Server};Server="& ServerName &";Database=msdb;Trusted_Connection=Yes;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT DISTINCT name FROM msdb.dbo.sysjobs j WHERE j.job_id in (SELECT h.job_id FROM msdb.dbo.sysjobhistory h WHERE enabled = 1 and j.job_id = h.job_id AND h.run_status in (0) AND h.job_id in (SELECT TOP 1 h2.job_id FROM msdb.dbo.sysjobhistory h2 WHERE h.job_id = h2.job_id ORDER BY run_date, run_time desc ))" , objConnection, adOpenStatic, adLockOptimistic
Dim filesys, testfile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set testfile= filesys.OpenTextFile("c:\temp\JobLog.txt", 8,true)
Set objShell = CreateObject("WScript.Shell")
testfile.WriteLine ""
testfile.WriteLine now()
testfile.WriteLine "--- "& ServerName &" --- "
if not (objRecordSet.EOF and objRecordSet.BOF) then
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
testfile.WriteLine objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
end if
testfile.Close
objRecordset.Close
objConnection.Close
end sub
sub MakeLogNonTrusted (ServerName, User, Password)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Driver={SQL Server};Server="& ServerName &";Database=msdb; Uid="& User & ";Pwd="& Password &";"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT DISTINCT name FROM msdb.dbo.sysjobs j WHERE j.job_id in (SELECT h.job_id FROM msdb.dbo.sysjobhistory h WHERE enabled = 1 and j.job_id = h.job_id AND h.run_status in (0) AND h.job_id in (SELECT TOP 1 h2.job_id FROM msdb.dbo.sysjobhistory h2 WHERE h.job_id = h2.job_id ORDER BY run_date, run_time desc ))" , objConnection, adOpenStatic, adLockOptimistic
Dim filesys, testfile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set testfile= filesys.OpenTextFile("c:\temp\JobLog.txt", 8,true)
Set objShell = CreateObject("WScript.Shell")
testfile.WriteLine ""
testfile.WriteLine now()
testfile.WriteLine "--- "& ServerName &" --- "
if not (objRecordSet.EOF and objRecordSet.BOF) then
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
testfile.WriteLine objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
end if
testfile.Close
objRecordset.Close
objConnection.Close
end sub
October 16, 2008 at 8:12 am
Hi Jack,
Do I need to supply run_date and run_time?
Leo
October 16, 2008 at 8:22 am
Leo (10/16/2008)
Hi Jack,Do I need to supply run_date and run_time?
Leo
I'm not sure what you mean. If you only want the status for jobs that run since the last email then the code I provided
will work fine. Of course I am running 2005 not 2000 so there is the possibility that there may be some table changes.
If you want to see the last run status regardless of date you'd probably want a derived table that has the job id and the
max run date and time (You'd want to convert and concatenate into a datetime value).
jsvensson,
You should put some carriage returns\line feeds into your code block so you don't have to scroll horizontally. I have a 22
inch wide screen and I have to scroll.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 9:06 am
Hi Jack,
I am getting 'xp_sendmail : Either there is no default mail client or the current mail client cannot fulfill the messaging request ....'
I think I haven't installed outlook where my SQL Server is...., is that right?
I don't want to install outlook on SQL Server, anyway can I get around this?
Thanks again.
October 16, 2008 at 9:32 am
Yes, to use xp_sendmail you need Outlook installed on the SQL Server. When I was using
SQL 7, 2000 I used xp_smtp_sendmail. I would post a link, but I just went there and there
isn't a way to download or instructions there. I have a saved version of the documentation
and the software somewhere. I will add it to this thread when I find it. It is really a better
way to send email in SQL Server 7, 2000. Although if you want alerts and SQLAgentMail to
work you need to install Outlook anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 1:27 pm
I hade the same problem. Several different SQL servers on different networks. Therfore I scheduled the scripted collection from one computer and read the file there or I can send it thrue mail.
October 16, 2008 at 2:13 pm
Jack Corbett (10/16/2008)
Yes, to use xp_sendmail you need Outlook installed on the SQL Server. When I was usingSQL 7, 2000 I used xp_smtp_sendmail. I would post a link, but I just went there and there
isn't a way to download or instructions there. I have a saved version of the documentation
and the software somewhere. I will add it to this thread when I find it. It is really a better
way to send email in SQL Server 7, 2000. Although if you want alerts and SQLAgentMail to
work you need to install Outlook anyway.
Jack, I think it was you that helped me "re-find" this stored procedure a few months back. I don't recall a lot of documentation but here are the sample SQL scriptes and descriptions of the various parms. Pretty straight-forward. Go to the following to donwload:
http://www.sqldev.net/xp/xpsmtp.htm
-- You can't be late until you show up.
October 16, 2008 at 2:22 pm
Interesting. The xpsmtp page doesn't display correctly in Firefox 3.0.3. It is fine in IE 8 though. So you should
just go to the link Terry provides. This is basically bulletproof. If it were asynchronous likeDB Mail in 2005 it would
be better than DBMail.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 3:01 pm
Hi Terry and Jack,
So, what I need to do? Just run xpstmp.txt in SQL 2000 query analyser? What does it do?
Leo
October 16, 2008 at 3:12 pm
The text file was a sql file (I changed the extension to upload it). Go to the website and download the zip file. You'll get the text file I attached and a dll file. Follow the instructions in the text file (it's based on version specific, i.e 7.0 or 2000) by copying the dll where specified. Then run the exec and grant commands provide in the script. After that, the document contains various samples of executing the extended stored procedure and various options that it supports. Fairly simply and, so far, reliable. Post again if you get stuck somewhere.
-- You can't be late until you show up.
October 23, 2008 at 2:07 am
Hi jsvensson
I used your VB Program to get the failed job on SQL Server but in some reason, I am getting the same result - For example -
I got 4 job need to run at night and 2 was fine and 2 was failed.
I got the result next morning according from your code, it said 2 failed with description. That is good.
But
The next day, when the job was run again with all success. But keep sending me a failed jobs which is failed on the other night. Can you have a look your code please?
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply