Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2005 Maintenance Plan E-mail fails

Posted on 2014-01-20
7
Medium Priority
?
564 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 23

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 23

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 23

Accepted Solution

by:
Steve Wales earned 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 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