Send mail when sql job fails

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I have a SQL job. If the job fails / succeeds, I want to send the mail to user.

For this, I created an operator under SQL Server Agent and put down my email address in the E-mail name text box under Notification Options.

Then, on the Properties of my created SQL job, under the Alerts section, I created new alert. Checked the Notify Operators check box and selected the newly created Operator.

I think this is what we have to do to receive mail if the job fails/succeeds.

But I am not able to receive any mail. When I checked the Error Log, it showed me this error message, "Error,[264] An attempt was made to send an email when no email session has been established"

I referred this http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/32a2c09a-156f-43b2-9076-b11ba9ccdafb/[^]

link for the help but start/ stop/ restart of SQL Server Agent is of no help.

Can anybody help me to receive mail after the sql job completes???

Any help appreciated.

Thanks,

Lok..

Posted 19-Dec-12 0:27am

Lokesh Zende

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Answer 1 · 2012-12-19T00:40:00

Solution 1

This CP article might help you
SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database[^]

Posted 19-Dec-12 0:40am

__TR__

Comments

Lokesh Zende 19-Dec-12 8:09am

Sir I used the same procedure to create my profile n account to send the mail.
I tried sending the test mail as well, it worked. but now I want to send the mail if any SQL job fails / succeeds.
And btw, I like your blog as well. Its very helpful for newbies like me.

__TR__ 19-Dec-12 8:27am

The CP article i refereed here is not written by me. So i am not taking that credit.
To send an email you need to call the stored procedure sp_send_dbmail after a job is done.

Lokesh Zende 19-Dec-12 8:33am

Will I not need to specify anything int the Notification or Alert section of the Job Properties? If I have to call the procedure "sp_send_dbmail" after a job is done, then what is the use of setting the mail account and creating the profile as described in the post? I was expecting that the mail id I put while creating the mail account, will get the notification mails.

Prasad_Kulkarni · Answer 2 · 2012-12-19T01:19:00

Solution 2

Check out:
SQL Server 2008 -Create and Schedule Job
Scheduling automated backup using SQL server 2008
How to: Schedule a Job (SQL Server Management Studio)

Scheduling Administrative Tasks Using SQL Server Agent[^]
Scheduling SQL Server Agent Jobs[^]
Step by Step process of adding a sql job in SQL Server[^]

Posted 19-Dec-12 1:19am

Prasad_Kulkarni

Comments

Lokesh Zende 19-Dec-12 8:15am

Thanks for your reply Prasad.
I know how to create and schedule a job. I want to receive a mail when my scheduled job succeeds or fails.

Lokesh Zende · Answer 3 · 2012-12-19T18:14:00

I had set up everything as needed and as I already described in my question, but don't know why I was not receiving any mail. I had also restarted me machine so many times yesterday but to no luck.
Today when I opened my mailbox, I started receiving mails for the sql job completion.

I still have few more questions about it, but I will start a new thread instead.

Thanks everyone.