| |
 |
|
SQL Server Tips by Burleson |
XP_NET_SEND and Jobs
Popping up a window every minute is not very useful but a practical
application of a job could be to make a backup, every day at
midnight. The job could email, page or net send a message to the
System Administrator if the backup failed. Changing the MinuteTest
job to perform such tasks is very straightforward.
However there are limitations
1. Jobs cannot contact more than one operator.
2. Jobs can notify the operator of the job’s success or failure
(either one exclusively), but the operator might want to be notified
that the job ended no matter how. For example, lack of notification
of the job’s failure does not mean success; it could mean that the
job never started.
3. raiserror sets a system flag to indicate that an error occurred,
even when it is used to log a plain warning.
The solution to the first three limitations is to use XP_NETSEND.
For the first one, the solution is to not use the notifications from
the job and have the error management code in the TSQL command to
handle the notifications. The code should have a variable to store
the error status and notify the interested parties, when all the
code has been executed.
For the second problem, the solution would be to have the job
notifying the operator in case of success and the TSQL command
calling XP_NET_SEND in case of failure:
EXEC master..XP_NET_SEND
@To='Administrator', @Message='Backup 2835 – status: failure
date:5/7/2004'
EXEC master..XP_NET_SEND @To='John', @Message='Backup 2835 failed –
please remove tape and try it again manually'
This solution bypasses the operator in case of success and it
assumes that the Administrator should be notified only when there is
a failure, which makes sense.
The third case requires an XP that writes in any of the Windows
logs, please check XP_NTLOG chapter.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
