[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

SQL 2005 Maintenance Plan E-mail fails

Hello,

I have setup a maintenance plan using these instructions http://www.techrepublic.com/blog/the-enterprise-cloud/create-database-maintenance-plans-in-sql-server-2005-using-ssis/#.

The backup works fine, however the e-mail notification fails with -1073548784

Would appreciate advice on how to troubleshoot
0
tonydav67
Asked:
tonydav67
  • 4
  • 3
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
Have you configured Database Mail ?  By default, DB Mail is disabled.

Concepts:
http://technet.microsoft.com/en-us/library/ms175887%28v=sql.90%29.aspx

The Database Mail Config Wizard:
http://technet.microsoft.com/en-us/library/ms175951%28v=sql.90%29.aspx

If you haven't done the above, Database Mail won't work.

Once that's done, have you setup your Agent job to use Database Mail ?

http://technet.microsoft.com/en-us/library/ms186358%28v=sql.90%29.aspx

That would be the first two things to look at.
0
 
tonydav67Author Commented:
Hello sjwales

Mail Config was done, SQL Agent was not configured to use Database Mail, so hopefully when the job runs tonight I will receive an e-mail.

I'll update this question tomorrow.

Thank you.
0
 
tonydav67Author Commented:
No change, same error as before.

This is not entirely surprising to me, because the instructions that I followed did not mention the need to configure the SQL Agent.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Steve WalesSenior Database AdministratorCommented:
How have you set up the notification ?  To notify an operator ?  

If so is the operator set up ?

Does email work at all ?  Have you tried just calling sp_send_dbmail and seeing if that works ?
0
 
tonydav67Author Commented:
I don't know the answer to your first two questions. What I have done is follow the instructions in the link in my question.

E-mail does work. And by that I mean that I have run the Database Mail Configuration Wizard, created a profile and an account. Using Database Mail I can send a test e-mail to myself.
0
 
Steve WalesSenior Database AdministratorCommented:
In your SQL Server Agent job setup, when you right click on the job and select properties you have a list of pages on the left.  General / Steps / Schedules / Alerts / Notifications / Targets.

If you click on Notifications you have options to EMail / Page / NetSend / Write to Windows App Log and Auto delete job.

If you select Email, there's a drop down box there.  Those are operators.  In order to have the job email you a success / fail notification you need to set up an operator there.

Cancel out of the job property and expand SQL Server Agent in SSMS.  You'll see Jobs / Job Activity Monitor / Alerts / Operators.

Expand Operators.  These are the options that appear in the drop down discussed above.

Do you have yourself configured as an operator ?  If not, right click on Operators, select new operator, give it a name, check enabled, put in your email address and if you want, your text message to cell email address in Pager.  (mine is xxxxxxxxx@txt.att.net since my cell provider is AT&T).

Then, go back to the job and configure the operator for the job notification, set up to send on success / fail / completion and you should be set up.

See the pictures below to illustrate what I mention above
Setup of OperatorSetup of job NotificationAre your jobs set up that way ?
0
 
tonydav67Author Commented:
No.

An Operator was not defined, as that was not in the instructions that I was following.

I have created an Operator and gone back to the job and configured the notification. It looks like those instructions I were following were a crock regarding the notification.

I'll update you tomorrow.

Thanks
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now