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.
I did submit a bug report.
Gilles
Unable to send MAil Alert using DatabaseMail
TomYip
This bug is fixed in the RTM build.
Workaround to make it work in RTM build will be by updating below entries under SQLServerAgent node
DatabaseMailProfile// The name of the profile
UseDatabaseMail - 1 // for DBMail and o for Agent mail
Gabe Halsmer
That was typo in my prev posting.
The workaround i reported was for CTP build . In RTM, it works as expected
Ashok kumar Amara
For instance, try this. Enable Database Mail but don't setup an account or profile. Then go to SQLAgent properties. Go to Alert System and tick Enable Mail Profile. Select Database Mail and leave profile blank (coz you haven't set any up). Click OK, restart SQLAgent and check your error logs. Bingo. Funny thing is, taking a look at your Alert System settings again will show that "Enable Mail Profile" is unticked again however this error message will continue to appear in your SQL Agent Error logs whenever you restart SQLAgent.
I can reproduce this for x86 SQL Server 2005 Developer Edition SP2 (post 5th March) and x64 SQL Server 2005 Standard Edition SP2 (post 5th March).
Mehdi Moshtaghi
What is the value you must enter into the register key 'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent'
Thanks
R Zare
I am getting this error in Work Group edition.
Does any one know how to fix this
Valkrye
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx feedbackid=03588a7d-e715-4a6f-ad81-caf5fea644c4