FETCH MyCursor into 'at'jobname, 'at'stepname, 'at'message
WHILE 'at''at'FETCH_STATUS = 0
BEGIN
Set nocount on
Set 'at'errormessage = 'at'errormessage + 'Step ' + 'at'stepname + '
Succeeded. Error message: ' + 'at'message
FETCH NEXT <---- there !!!!!
END
-----Original Message-----
From: Guzman
[mailto:mssqldba-ezmlmshield-x88233108.[Email address protected]
Sent: 28 September 2006 16:56
To: LazyDBA Discussion
Subject: RE: Email notifications
Where? Replace FETCH with FETCH NEXT? or does it come in a different
spot?
------------------------------------------------------------------
Dan
Carol Green
<mssqldba-ezmlms
hield-x37032382. To
[Email Address Removed] LazyDBA Discussion
DBA.com> <[Email address protected]
cc
09/28/2006 12:45
AM Subject
RE: Email notifications
You need a fetch next in your loop...
-----Original Message-----
From: Guzman
[mailto:mssqldba-ezmlmshield-x68954331.[Email address protected]
Sent: 28 September 2006 00:06
To: LazyDBA Discussion
Subject: RE: Email notifications
Here is what I tried, but didn't work:
Declare 'at'rc int
Declare 'at'jobname sysname
Declare 'at'stepname sysname
Declare 'at'message nvarchar(1024)
Declare 'at'errormessage nvarchar(1024)
use msdb
Declare MyCursor Cursor read_only for
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 ((dbo.sysjobhistory.sql_severity = 0))
OPEN MyCursor
FETCH MyCursor into 'at'jobname, 'at'stepname, 'at'message
WHILE 'at''at'FETCH_STATUS = 0
BEGIN
Set nocount on
Set 'at'errormessage = 'at'errormessage + 'Step ' + 'at'stepname + '
Succeeded. Error message: ' + 'at'message
END
Close MyCursor
Deallocate MyCursor
exec 'at'rc = master.dbo.xp_smtp_sendmail
'at'FROM = N'SQLSERVERNETWORKADDRESS',
'at'FROM_NAME = N'SQL Server',
'at'TO = N'me',
'at'priority = N'NORMAL',
'at'subject = N'Backup Agent Success',
'at'message = 'at'errormessage,
'at'type = N'text/plain',
'at'server = N'smtp.com'
select RC = 'at'rc
go
The job shows this as the step that is failing, and when I try to run it
in QA, it just hangs. I set it up as a success step so I could run a
test Backup job to send myself an email. What's missing?
Dan
Getzow Stewart
<mssqldba-ezmlms
hield-x7256154.x To
[Email Address Removed] LazyDBA Discussion
BA.com> <[Email address protected]
cc
09/27/2006 01:08
PM Subject
RE: Email notifications
You can do this with a cursor like so: I haven't unit-tested this, but
you'll get the gen'l idea:
Declare @jobname sysname
Declare @stepname sysname
Declare @message nvarchar(1024)
Declare MyCursor Cursor read_only for
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))
OPEN MyCursor
FETCH MyCursor into @jobname, @stepname, @message
WHILE @@FETCH_STATUS = 0
BEGIN
Set nocount on
set stringvariable = stringvariable + 'Step ' + @stepname + ' failed.
Error message: ' + @message
END
Close MyCursor
Deallocate MyCursor
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
_______________________
You can get some cursor examples by looking at some of the ones that
Microsoft uses for some of its stored procedures in master database. For
example, sp_updatestats uses a cursor. Personally, I'm not a big fan of
cursors, because they open up memory structures of an indeterminate size
in the server instance. If the script runs quickly and deallocates the
cursor at the end, fine. If you know the script will run for a while,
though (probably not the case for what you're trying to do, however),
you're better off declaring a variable table, inserting your select
result into that, declaring a loop counter variable and using that to
loop through the variable table. Variable tables will use much less
memory and overhead on the server than cursors. Temp tables would be my
2nd choice in such a scenario. Hope this helps.
Stewart
-----Original Message-----
From: Guzman
[mailto:mssqldba-ezmlmshield-x39223456.[Email address protected]
Sent: Wednesday, September 27, 2006 2:21 PM
To: LazyDBA Discussion
Subject: RE: Email notifications
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
---------------------------------------------------------------------
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
--------------------------------------------------------
Important Notice to Recipients:
It is important that you do not use e-mail to request, authorize or
effect the purchase or sale of any security or commodity, to send fund
transfer instructions, or to effect any other transactions. Any such
request, orders, or instructions that you send will not be accepted and
will not be processed by Morgan Stanley.
---------------------------------------------------------------------
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 email and any files transmitted within it are private and
confidential.
If you are not the intended recipient, this email and any attachments
within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using
the information contained within this email.
Nothing in this email message amounts to a contractual or legal
commitment on the part of Optilan unless confirmed by a communication
signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from
viruses. Although every possible care is taken by Optilan, Optilan does
not accept any liability whatsoever for any loss or damage which may be
caused as a result of the transmission of this message by email.
---------------------------------------------------------------------
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 email and any files transmitted with it are
confidential and intended solely for the use of
the individual or entity to whom they are
addressed. If you have received this email in error
please notify the Systems Manager at [Email address protected]
***************************************************
This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com
MS Sql Server LazyDBA home page