Solved

SQL 2005 Maintenance Plan E-mail fails

Posted on 2014-01-20
7
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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