Getting Step history info into job notification emails

In SQL Agent, under the properties of a job, there is a notification tab
where you can direct it to send you an email if a job fails. However, that
email appears to contain the history for the overall job, but not the
history detail for the particular step that failed.
For example, the email text I get from my failed job (which has only 1 step)
is:
JOB RUN: 'svCON Check WS02' was run on 10/27/2003 at 10:21:31 AM
DURATION: 0 hours, 0 minutes, 1 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by User sa. The last step to
run was step 1 (check WS02).
Not very useful. If I right click on the job, choose "View Job History..."
and then check the box for "Show Step Details", then I can click on the row
for Step 1 and see a more precise message:
Executed as user: sa. WS02 workstation did not check in. Last check in was:
Sep 11 2000 7:05AM [SQLSTATE 42000] (Error 50000). The step failed.
Does anyone know how I can add this step history info to the job's email
notification?You can't do it unless you write your own code.
Besides, the purpose of email notification is to let you
know what has failed and then manual intervention by an
operator or dba will be required to fix it.
>--Original Message--
>In SQL Agent, under the properties of a job, there is a
notification tab
>where you can direct it to send you an email if a job
fails. However, that
>email appears to contain the history for the overall job,
but not the
>history detail for the particular step that failed.
>For example, the email text I get from my failed job
(which has only 1 step)
>is:
>JOB RUN: 'svCON Check WS02' was run on 10/27/2003 at
10:21:31 AM
>DURATION: 0 hours, 0 minutes, 1 seconds
>STATUS: Failed
>MESSAGES: The job failed. The Job was invoked by User
sa. The last step to
>run was step 1 (check WS02).
>Not very useful. If I right click on the job,
choose "View Job History..."
>and then check the box for "Show Step Details", then I
can click on the row
>for Step 1 and see a more precise message:
>Executed as user: sa. WS02 workstation did not check in.
Last check in was:
>Sep 11 2000 7:05AM [SQLSTATE 42000] (Error 50000). The
step failed.
>Does anyone know how I can add this step history info to
the job's email
>notification?
>
>.
>

Getting Notification / alert when failover occurs

Hi all,

What kind of notification / alert can I get when a failover occurs?

I need to refresh SqlDependency after a failover.

Thanks,

Avi

Hello Avi,

You want to stop the SqlDependency and start again with a different connection string or what?

One possible notification is the profiler trace event, Database Mirroring State Change event: http://msdn2.microsoft.com/en-us/library/ms191502.aspx. This event is fired more often than when failover occurs, but two of the states are indicating failover (7 - Manual Failover and 8 - Automatic Failover). To programatically receive these I would go with event notifications, http://msdn2.microsoft.com/en-us/library/ms189453.aspx. :

create event notification failover

on server

for database_mirroring_state_change

to service 'myservicename', 'current database';

In your application you must post a WAITFOR(RECEIVE ...) on the 'myservicename' queue to get the failover notifications. For this, you'll need to have some basic skills for programming Service Broker.

BTW, note that Service Broker routing has built-in awarness of mirroring (see 'Mirrorr Address' in http://msdn2.microsoft.com/en-us/library/ms166052.aspx). But you cannot use this for SqlDependency's messages (SqlDependency uses Service Broker to receive the query notifications) because the Query Notifications are per SQL instance and do not failover with the database.

HTH,

~ Remus

Getting error 22022: SQLServerAgent Error

Hi,
I'm trying to configure the mail notification on SQL Server and I'm
getting the following error "Error 22022: SQLServerAgent error: The
SQLServerAgent mail session is not running; check the mail profile
and/or the SQLServerAgent service startup account ...". PLEASE HELP!!!!!
I really don't know where to look at, what to configure. I'm an Oracle
DBA, and sometimes SQL Server DBA, I know stuff on SQL Server but not
how to configure the mail notification. So if someone could help me to
solve this issue that would be great.
Thanks in advance
Patrick
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hello Patrick
There are several steps in configuring SQLMail to be used with SQL and
SQLAgent. Basically SQLAgent is the scheduling engine for SQL Server and
runs as a separate service (shows up under Windows Services as
SQLServerAgent (or SQLAgent$<InstanceName> for named instances). This
service needs to be starting under a domain account which has access to the
mailbox (if using Exchange).
For more information I would advise you to look at the following articles :
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Getting error 22022: SQLServerAgent Error

Hi,
I'm trying to configure the mail notification on SQL Server and I'm
getting the following error "Error 22022: SQLServerAgent error: The
SQLServerAgent mail session is not running; check the mail profile
and/or the SQLServerAgent service startup account ...". PLEASE HELP!!!!!
I really don't know where to look at, what to configure. I'm an Oracle
DBA, and sometimes SQL Server DBA, I know stuff on SQL Server but not
how to configure the mail notification. So if someone could help me to
solve this issue that would be great.
Thanks in advance
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Hello Patrick
There are several steps in configuring SQLMail to be used with SQL and
SQLAgent. Basically SQLAgent is the scheduling engine for SQL Server and
runs as a separate service (shows up under Windows Services as
SQLServerAgent (or SQLAgent$<InstanceName> for named instances). This
service needs to be starting under a domain account which has access to the
mailbox (if using Exchange).
For more information I would advise you to look at the following articles :
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Getting error 22022: SQLServerAgent Error

Hi,
I'm trying to configure the mail notification on SQL Server and I'm
getting the following error "Error 22022: SQLServerAgent error: The
SQLServerAgent mail session is not running; check the mail profile
and/or the SQLServerAgent service startup account ...". PLEASE HELP!!!!!
I really don't know where to look at, what to configure. I'm an Oracle
DBA, and sometimes SQL Server DBA, I know stuff on SQL Server but not
how to configure the mail notification. So if someone could help me to
solve this issue that would be great.
Thanks in advance
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Hello Patrick
There are several steps in configuring SQLMail to be used with SQL and
SQLAgent. Basically SQLAgent is the scheduling engine for SQL Server and
runs as a separate service (shows up under Windows Services as
SQLServerAgent (or SQLAgent$<InstanceName> for named instances). This
service needs to be starting under a domain account which has access to the
mailbox (if using Exchange).
For more information I would advise you to look at the following articles :
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.