Solved

SQL 2005 Maintenance Plan E-mail fails

Posted on 2014-01-20
7
553 Views
Last Modified: 2014-01-22
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
Comment
Question by:tonydav67
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39795670
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
 
LVL 2

Author Comment

by:tonydav67
ID: 39795995
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
 
LVL 2

Author Comment

by:tonydav67
ID: 39798439
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 22

Expert Comment

by:Steve Wales
ID: 39798464
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
 
LVL 2

Author Comment

by:tonydav67
ID: 39798484
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
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39798535
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
 
LVL 2

Author Comment

by:tonydav67
ID: 39798569
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question