December 22, 2009 at 8:09 am
I am new to SQL 2005 and am not a DBA. I have questions for setting up Database Mail. I have spent 2 days searching, which has left me with even more questions. The send test email works but job status notifications aren't sent. To start with:
1. I am using 64-bit SQL SP3 and MS Technet KB908360 says 64-bit is not integrated with mail and has to be set up manually. The article is dated 2007 and mentions SP1 so I am wondering if it is still apllicable.
2. I see Database Mail can be configured per instance but is best practice to use a separate SMTP account per instance?
December 22, 2009 at 11:08 am
kfewer (12/22/2009)
I am new to SQL 2005 and am not a DBA. I have questions for setting up Database Mail. I have spent 2 days searching, which has left me with even more questions. The send test email works but job status notifications aren't sent. To start with:1. I am using 64-bit SQL SP3 and MS Technet KB908360 says 64-bit is not integrated with mail and has to be set up manually. The article is dated 2007 and mentions SP1 so I am wondering if it is still apllicable.
2. I see Database Mail can be configured per instance but is best practice to use a separate SMTP account per instance?
1. I would follow the suggestions in the article and setup the mapi.
2. 1 SMTP should do.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2009 at 8:46 am
1. Database Mail IS supported on 64-bit. This article states that it is "fully-supported on 64-bit".
2. It depends on if you want to be able to identify which instance the mail comes from based on account.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2009 at 8:46 am
Have you gone through the steps of setting up an operator under SQL Agent?
After setting up database mail, you have to go under operators in SQL agent and setup an operator. After you setup an operator you have to restart the SQL Agent.
Now, when you go to Notifications on the job properties and check E-Mail your operator should be listed in the drop down box.
December 23, 2009 at 9:19 am
Thanks for your reply, Jack.
1. The KB article does say the issue with 64-bit was addressed in SP1, so it shouldn't be an issue. We do have another 64-bit server where the mail is working. I have been comparing the servers and two differences stand out:
a) The working server has only one instance. The app is using the default instance.
b) The working server's Agent is using the local system account and the non-working server's agent is using a domain service account. I am not sure if/how this affects the notification.
This is the error in the job history: "Could not obtain information about Windows NT group/user 'sa', error code 0xffff0002.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I made some changes yesterday and I did get an email to tell me the email notification failed. Hm. Also, the 'Send Test Email' works.
2. We will have multiple instances on this server so I would prefer to identify the instance. However, if best practice is to only use one SMTP account for all instances, I could identify the instance within the message. One SMTP account for all instances would mean less overhead. What are the disadvantages for this?
December 23, 2009 at 9:26 am
who owns the job?
December 23, 2009 at 9:48 am
SQL sa account for the instance
December 23, 2009 at 9:57 am
I have had similar problems when database mail profile was not set to public. Is the public checkbox checked for your profile?
December 23, 2009 at 10:17 am
Yes. It is set to public. I am also getting an error on all maintenance jobs: "Login failed for user 'sa'.", even though all the steps except email appear to be working.
I recently removed and reinstalled SQL after a hardware change. After the vendor restored the database, I was unable to login to the instance (Management Studio) using the sa account. Although they fixed this, the maintenance jobs (which were created after the re-install) all get sa login errors. I am thinking it may be related. Unfortunately the vendor is closed for the holidays.
December 23, 2009 at 10:42 am
Is the server set for Mixed Authentication or Windows only?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2009 at 11:18 am
It is set for Mixed. Although the owner of the maintenance jobs are all sa, the Activity monitor lists the user of msdb as the domain service account and all other databases has sa as the user. Not sure if this is of any significance.
December 23, 2009 at 1:25 pm
I switched the agent to local system account (restarted it) and ran the job but it still failed.
December 28, 2009 at 9:26 am
I have also set email notification on the job itself and I do receive emails for that. It is the email notification task within a maintenance plan that fails.
December 28, 2009 at 12:05 pm
Finally success! It turned out to be a missed configuration step: Setting the public profile as "Default". Thanks everyone for your help!
December 28, 2009 at 12:11 pm
Good Work! Sorry to not be of more help.
Thanks for posting your solution
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply