Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter