OK, that makes sense, now how do I do it? The query below gets me the
output I want, but I'm not sure how to add it to the step. In my job,
I've set each step to 'gotoNext' on failure, then use this query to show
all the step failures. It's the syntax i don't know. Can someone
please help? My guess is below.
declare some variables
create cursor ????
set recorsetvariable = SELECT dbo.sysjobs.name,
dbo.sysjobhistory.step_name, dbo.sysjobhistory.message
FROM dbo.sysjobhistory INNER JOIN
dbo.sysjobservers ON dbo.sysjobhistory.job_id =
dbo.sysjobservers.job_id AND dbo.sysjobhistory.run_date =
dbo.sysjobservers.last_run_date AND
dbo.sysjobhistory.run_time >
dbo.sysjobservers.last_run_time INNER JOIN
dbo.sysjobs ON dbo.sysjobhistory.job_id =
dbo.sysjobs.job_id
WHERE (NOT (dbo.sysjobhistory.sql_severity = 0))
set stringvariable = stringvariable + 'Step ' +
recorsetvariable.step_name + ' failed. Error message: ' +
recorsetvariable.step.message
loop cursor
set stringvariable = recorsetvariable.name + ' Job failure! ' +
stringvariable
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'SQLSERVERNETWORKADDRESS',
@FROM_NAME = N'SQL Server',
@TO = N'me',
@priority = N'HIGH',
@subject = N'Backup Agent Failure',
@message =stringvariable,
@type = N'text/plain',
@server = N'smtp.com'
select RC = @rc
GO
Harsh Kumar
<mssqldba-ezmlms
hield-x66180823. To
[Email Address Removed] LazyDBA Discussion
DBA.com> <[Email address protected]
cc
09/27/2006 06:15
AM Subject
RE: Email notifications
Hi Dan,
Take data from msdb db of tables sysjobhistory table for jobs
fail/successes
and pass it body of email msg.
Thanks
HArsh
-----Original Message-----
From: Guzman [mailto:mssqldba-ezmlmshield-x47364159.[Email address
protected]
Sent: Tuesday, September 26, 2006 11:12 PM
To: LazyDBA Discussion
Subject: RE: Email notifications
I loaded the xp_smtp_sendmail procedure last night, and created
sp_smtpmail. As I understand it, I now have to create additional steps
in my jobs to trigger these email events. How do I pass to the step,
which step triggered the failure and email?
Do I have to do:
Step 1 - Backup 1- On failure Step 2, on success step 3
Step 2 - Email Admin - Backup 1 failed, on failure Quit, on success step
3
Step 3 - Backup 2- On failure Step 4, on success step 5
Step 4 - Email Admin - Backup 2 failed, on failure Quit, on success step
5
Step 5 - Backup 3- On failure Step 6, on success step 7
Step 6 - Email Admin - Backup 3 failed, on failure Quit, on success step
7
Step 7 - Backup 4- On failure Step 2, on success Quit
Step 8 - Email Admin - Backup 4 failed, on failure Quit, on success Quit
Dan
Eric Emrich
<mssqldba-ezmlms
hield-x19279019. To
[Email Address Removed] LazyDBA Discussion
DBA.com> <[Email address protected]
cc
09/26/2006 08:21
AM Subject
RE: Email notifications
You'll need to have an SMTP server available to bounce messages through.
-----Original Message-----
From: Guzman
[mailto:mssqldba-ezmlmshield-x50200454.[Email address protected]
Sent: Tuesday, September 26, 2006 11:11 AM
To: LazyDBA Discussion
Subject: Re: Email notifications
SQL2K
------------------------------------------------------------------
Dan
Mike Akin
<mssqldba-ezmlms
hield-x4089602.x To
[Email Address Removed] LazyDBA Discussion
BA.com> <[Email address protected]
cc
09/25/2006 06:12
PM Subject
Re: Email notifications
what version of sql server to you have? They are handled differently in
different versions.
On 9/25/06, Guzman <mssqldba-ezmlmshield-x70621064.[Email address
protected]
wrote:
>
> Hey guys,
>
> How do I setup email notifications for my scheduled jobs? I don't
have
> exchange or outlook, we're using Lotus Notes. How do I make this
work?
>
> Dan
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
--
Michael Akin
Cell: 816-935-5944
Home: 816-350-0506
Email: [Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
_________________________________________________________________________
This message and any attachments may be confidential or privileged and
are for the exclusive use of the intended recipient. If you are not the
addressee or intended recipient, please do not read, copy, use, or
disclose this communication to others. If you have received this in
error, please notify the sender by replying to this message, and then
delete it entirely from your system. Delivery of this message and any
attachments to any person other than the intended recipient(s) is not
intended in any way to waive confidentiality or a privilege. All
personal messages express views only of the sender, which are not to be
attributed to Finish Line and/or any of its subsidiaries or affiliates,
and may not be copied or distributed without this statement.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page