Solved

SQL 2005 Maintenance Plan E-mail fails

Posted on 2014-01-20
7
544 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard 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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now