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?
migrated from stackoverflow.com Jul 13 '11 at 0:48
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).
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.