Maintenance plan job unable to send Mail Alert

Our configuration :
SQL Server 2005 June CTP Enterprise Edition version : 9.00.1187.07
OS : Windows 2003

The Problem:
After configuring Database Mail account and profile, maintenance plan or job generated with the maintenance plan Wizard is unable to send mail.

The SQL Agent error log contains those messages pointing on a bad profile name.

Error Messages
[264] An attempt was made to send an email when no email session has been established

[355] The mail system failed to initialize; check configuration settings

[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.Data.SqlClient.SqlException: profile name is not valid
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   a)

This is what we get when we generate the script to enable the mail profile:

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''
GO
USE [master]
GO

After verification, it seem that there is a least two problems
1. The script does not run!
2. The register path passed to the stored procs is not valid

The valid register path instead is
'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent',
And we need to manually put valid information in the 'DatabaseMailProfile' key and in the 'EmailProfile' key

After making those changes it was possible to send mail when running maintenance plan or job generated with the maintenance plan Wizard.

But every time that we configure a new DatabaseMail Profile, the 'DatabaseMailProfile' key is reset to blank and the registry has to be manually modified again.

I would appreciate if someone could confirm this situation and if there is a permanent solution to it.

Answer this question

Maintenance plan job unable to send Mail Alert

Oscar_Ruiz
This problem was with June CTP. It was no longer there with Sept CTP.
renard

Sorry, I think you mean to ask a question of the SQLMail folks not the Notification Services folks. You should repost you query here: http://forums.microsoft.com/msdn/ShowForum.aspx ForumID=84

-Lukasz
Brian Kinder
All u have to do is to restart the SQLAgent service after u have u created the database mail profile.
I faced the similar prob and restartin the service worked fine for me.

When the agent service is started and it does not get a mail profile then it gives the error [260]

And after that whenever u try to send a mail thru jobs u get the [264] error

As initially the mail profile was not present in the sqlagent it gave an error and in order for the mail profile to be accepted by the sqlagent u need to restart the sqlagent after enabling the mail profile.

Brian OByrne

Attention!!! If you have SQL Server 2005 64-bit does not work .

Read carefully this kb article http://support.microsoft.com/kb/908360/en-us and have fun.

Almost one complete day of work trying to resolve this unresolvable issue.Why this is undocumented in SQL BOL

Michael MacGregor wrote:

What I did and what I found wasn't very clear in BOL is that first you have to create a Database Mail Account, then you create a Database Mail Profile that includes at least the Database Mail Account you previously created. Then you have to specify that the SQL Server Agent uses the Database Mail Profile, and you need to create an Operator for the email to be sent to. I found after doing all of that, it worked.

If you have to manually edit the registry, put the name of the Database Mail Profile in the key specified.

HTH

tirpitz3
Two questions are asked repeatly in this thread and then, never answered directly.

1. If there a hot fix or work around (or only a *beta* SP) If it's beta, this will be a problem for folks (like us) using 2005 in production
2. If a patch is required, i.e. no work around, where can it be downloaded from

All of our SQL Server 2005 boxes are dumping this error all day and all night. If there's a fix, we'd like to have it.

Thanks,

Joe
Arvin
Ok, I've tried restarting the SQL Server Agent service and that didn't work, I even tried shutting down SQL Server itself and that didn't work. So if I have to hack the registry what value do I put in the DatabaseMailProfile key
GreggM

What I did and what I found wasn't very clear in BOL is that first you have to create a Database Mail Account, then you create a Database Mail Profile that includes at least the Database Mail Account you previously created. Then you have to specify that the SQL Server Agent uses the Database Mail Profile, and you need to create an Operator for the email to be sent to. I found after doing all of that, it worked.

If you have to manually edit the registry, put the name of the Database Mail Profile in the key specified.

HTH
meterman
Got it to work eventually but not until after having to read a lot of information that wasn't very well presented in BOL. Anyway, works now and that's what counts, and I learnt a lot in the process.
Christian Paparelli

Step wise:

1) create a database mail profile.

2)try to send a test mail with the same

3) if that works then change in the properties of the sqlagent,in the alerts tab...the mailing to database mail.

4) restart the sqlagent service alone....

5) I guess after that u shall be able to send ur mails via jobs.....
Rosaria
Will the 64 bit version problem with mail notifications solved with SP1 or not
donno20
Well it's back in the RTM edition, and stopping and restarting the SQL Server Agent service didn't fix it, not for me anyway.
Jason Lin
You must enter the name of an existing Database Mail Profile
Pandiarajan.net

Folks I've moved this thread over to the Tools General discussion. The NS forum just doesn't have the right folks reading it to answer this question.

The thread is now here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=54892&SiteID=1
I'm going to lock this thread in this forum. You can search for the title of the thread in the Tools General forum, it comes up as the first hit.

Take care,

-Lukasz
---
This posting is provided "AS IS" with no warranties, and confers no rights.
mconnag
I am having the same problem. Where did you find some more information and whats value are enter in the Register

Thanks
Maintenance plan job unable to send Mail Alert

Answer this question