September 6, 2007 at 1:42 pm
Hi everyone,
I'm new to SQL 2005 and am trying to get Database Mail set up so I can use it to notify me when a task fails in my maintenance plan.
Here are the environment details:
- SQL Server 2005 Standard Edition SP2 (9.00.3054.00 (IntelX86))
- Windows 2003 Server Standard Edition R2 SP 1
I've configured the Database Mail utility through the "Configure Database Mail" wizard. I can successfully send an email through the "Send test email..." option off the "Database Mail" menu item in Object Explorer, but when I try to send an email using msdb.dbo.sp_notify_operator or through the Notify Operator Task I get the following error in the Database Mail Log:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2007-09-06T09:32:23). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
When I view the Maintenance Plan history I find the following error:
Executing the query "EXECUTE msdb.dbo.sp_notify_operator @name=N'OperatorName',@body=N'My notification message'
" failed with the following error: "No global profile is configured. Specify a profile name in the @profile_name parameter.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've read most of the posts SQL Server Central, MSDN and a number of othe places, but all the solutions mentioned there don't seem to work for my problem.
Here are the settings I've currently got configured:
- Database Mail is Enabled in Surface Area Configuration
- The Mail session on the Alert System page of the SQL Server Agent Properties is Enabled; Mail system = Database Mail; Mail profile = MyProfile. Strange thing - "Test" button is disabled for Database Mail setting but not SQL Mail setting????
- Operator is setup
- E-mail name = MyProfile (not sure if this should be the email address or the profile)
- EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' returns the username I've chosen for the User name under Private Profiles
- Account:
Outgoing mail server (SMTP)
E-mail address: email address that exists on SMTP server
Reply e-mail: same as E-mail address
Server name: our SMTP server address
Port Number: 25
SMTP Authentication:
- tried both Windows Authentication using Database Engine services credentials and Anonymous authentication
- Profile
One account associated with the Profile:
Priority = 1, Name = account name, E-mail Address = account e-mail address
- Profile Security
Private Profiles
- Access = checked
- Profile Name = profile name from above
- Default Profile = Yes
Some of the other things I've tried:
1. Trying the sp_send_dbmail procedure from a query window:
DECLARE @intMailID int
EXEC msdb.dbo.sp_send_dbmail @profile_name=N'MyProfile',@recipients='name@domain',@subject='sp_send_dbmail test',@body='Test using sp_send_dbmail',@mailitem_id = @intMailID OUTPUT
SELECT @intMailID
It executes fine and generates a new mail ID, but I don't receive the email.
2. Tried switching the default profile from Private to Public.
- Maintenance Plan History shows no errors, but no email is sent.
3. Stopping and restarting SQL Server Agent service after each change.
Sorry about the length - just wanted to provide as much relevant info as possible.
The frustration factor is growing high and I'm not sure what else to try.
Any help would be greatly appreciated.
Thanks!
Andy
September 7, 2007 at 9:31 am
Is your public profile also the default profile? In the DB Mail Configuration Wizard, select "manage profile security" and for the profile you want to use make sure the "Default Profile" value is set to yes. I don't know if you need a public profile or not, but that's what we're using and it works for us.
Also, I don't think the SQL Agent settings on the Alert properties page will affect what you're trying to do; I believe that just determines which profile to use when responding to an Alert (i.e. nothing to do with mail sent by maintenance plans).
September 10, 2007 at 10:19 am
Hi Grasshopper,
Thanks for the suggestion, but still no joy.
The Maintenance Plan executes successfully (no errors). But I still did not receive an email - I've got the Maintenance Job setup to send a notification to the operator on both success and failure.
Don't suppose you have any ideas why the "Test" button is disabled when the Database Mail option is selected? It would be a useful option for debugging this problem.
Cheers,
Andy
September 10, 2007 at 10:51 am
After re-reading your post. There is something wrong with how you configured Database Mail. Not sure what yet, but the error message below is where I think the issue is.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2007-09-06T09:32:23). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
September 10, 2007 at 11:38 am
I updated the Security Profile to set the Public Profile to default and active. That seemed to fix the error I was getting with the Maintenance Job, but I still don't receive an email. And the Send Test Email sends successfully.
I'll double check the settings in the Database Mail Configuration.
Cheers,
Andy
September 10, 2007 at 1:17 pm
Dooh! I found the problem and fixed the problem. 🙂
Something really stupid - the E-mail name was set to the Database Mail Profile name not the actual email ADDRESS. Once I changed it to an email address there was much rejoicing and dancing.
Cheers,
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply