SQL Mail Registry Settings

If you're using SQL Mail on SQL Server 2000, at some point you may have run into this issue. You change the mail profile or maybe you remove the mail profile, and either the profile name won't change in SQL or you can't remove it. If there is no profile, then the option is disabled but still shows up. I have ran into an issue where SQL Agent will not start because it cannot find the mail profile associated with it. There is a work around for this...and it is in the registry. Also, there is a bug in SQL Management Studio for SQL 2005 that does not allow you to see the SQL Mail settings on a SQL 2000 system and will erase SQL Mail settings if you save any changes to the configuration through it.

Normally, you want to stay away from the Registry and avoid touching it as much as possible. But sometimes you have to change settings in it. Before we get to the solution, a quick primer on how SQL Mail works. SQL Mail has two separate pieces that work with SQL Server 2000, one is based on the SQL Service Account and the other is based on the SQL Agent Service Account. The service account being used has to be a Domain account with rights to an Exchange Account/Profile. SQL Server's SQL Mail is accessed through the system extended stored procedure, "xp_sendmail". SQL Server Agent's SQL Mail sends alerts and job notification through Operators. You can configure different profiles for SQL Server and SQL Server Agent or use the same profile for both. There is more information on this from Microsoft. SQL Mail is replaced by Database Mail in SQL Server 2005.

Configuring SQL Mail
http://msdn.microsoft.com/en-us/library/ms189620(SQL.90).aspx

SQL Server Agent Mail
http://msdn.microsoft.com/en-us/library/ms188235(SQL.90).aspx


Caution: Edit the registry at your own risk and test any changes before changing your production system.

Now to resolve the issue and find the registry settings that contain the mail profiles for SQL Server and SQL Agent. These steps are for SQL Server 2000, SQL Server 2005 has similar settings but they are under different registry paths and names. Here are the steps:

1) Go to start, run and type regedt32 to open the registry editor.

2) Navigate to the settings for SQL Server under:
"HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > MSSQLServer > MSSQLServer"

3) Locate the String Value for "MailAccountName" and change the value to the new profile name or remove the value if the profile no longer exists.

4) Navigate to the settings for SQL Server Agent under:
"HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > MSSQLServer > SQLServerAgent"

5) Locate the String Value for "EmailProfile" and change the value to the new profile name or remove the value if the profile no longer exists.