Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I send an e-mail when the sql server job fails. I added my e-mail as operator and then in the job properties -> notification -> enabled the email. But it is not working. Any ideas?

share|improve this question
3  
did you set up the mail server and send a test email? –  SqlSandwiches Jul 13 '11 at 3:27
    
Which version of SQL? Also, can you use TSQL (like sp_send_dbmail) to send out mail? –  StanleyJohns Jul 13 '11 at 13:21
add comment

migrated from stackoverflow.com Jul 13 '11 at 0:48

This question came from our site for professional and enthusiast programmers.

2 Answers

If you're running SQL Agent under a domain account it should be possible to create a different step that is only run "on failure" of the step you're worried about. Inside of that new step you can select it to be a PowerShell step for SQL 2008 & SQL 2008 R2.

Inside of the PowerShell step you can either use the native PowerShell 1.0 capabilites or (much more easily) simply call "powershell.exe -noninteractive" and pass it the name of a script file to use. At this point you'll be using full-blown PowerShell 2.0 and you will be able to use the Send-MailMessage cmdlet. Inside the script just include a full set of all the information for the required parameters of Send-MailMessage (things like: to, from, smtpserver) and you're ready to go.

You can use the same approach with SQL Server 2005 Agent Job Steps by simply selecting a type of cmdline (since PowerShell wasn't available in that version).

share|improve this answer
add comment

For 2005 and 2008 you need to configure Database Mail then enable the Agent to use it (SQL Server Agent --> Properties --> Alert System). If problem persists, you can query the system tables related to Database Mail (check BOL for names of tables) for possible more information. If problems still persists you may need to work with your Exchange admin.

For 2000 you need Outlook installed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.