Unable to send MAil Alert using DatabaseMail
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
