Updated in december 2008 (see at the end)
I've been playing with Database Mail in my SQL 2005 Cluster for a few days now. I always get the following errors :
Database Mail Log:
Log Database Mail (Database Mail Log)
Last Modified By sa
Message : Activation failure.
EventViewer Application Log: Event Type: Information
Event ID: 9724
User: N/A
Description: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Last night I found a solution and I though it would be a good idea to share it.
As you know SQL Server use an external program to process mail : DatabaseMail90.exe. As I understand it, when SQL launch DatabaseMail90.exe it connect to the msdb database and look in the system tables for it's configuration and for messages to process.
First Problem : DatabaseMail90.exe connection
Looks like DatabaseMail90.exe is trying to connect to SQL using Localhost or ComputerName as it's SQL Server name. In a cluster you can't do that, you should use the Virtual SQL Server Name as define in your Cluster resources. Can we change that ? Not really.
But there is a workaround. Microsoft added a new feature in SQL SP2 to allow you to
send mail from a remote server. We are going to use that feature to make DataBaseMail90.exe connect to the Virtual SQL Server (Cluster).
First we have to create the text file DataBaseMail90.exe.config in the \MSSQL\Binn folder of your SQL Instance (ex: \Program Files\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\MSSQL\Binn). with the following content :
<configuration>
<appSettings>
<add key="DatabaseServerName" value ="VirtualServerName\InstanceName" />
<add key="DatabaseName" value ="msdb" />
</appSettings>
</configuration>
Replace
VirtualServerName\InstanceName by the valid names from your environment.
Don't forget your are in a cluster, so you should copy that new file on every node of your cluster.
Next we have to modify the DatabaseMail config to tell SQL Server to use the new file. We'll do that by running the following code in SQL Server Management Studio :
USE msdb;
GO
INSERT INTO [msdb].[dbo].[sysmail_configuration]
(
[paramname]
,[paramvalue]
,[description]
)
VALUES
(
N'ReadFromConfigurationFile'
,N'1'
,N'Send mail from mail server in configuration file'
);
GO
Second problem : the 'sp_sysmail_activate' stored procedure
'sp_sysmail_activate' is the procedure that launch the DatabaseMail90.exe process. Normally when you updated your SQL Server to SP2 that procedure got updated to support the new DataBaseMail.exe.confg file. Don't ask me why but when you are in a cluster this is not the case (thanks
FreemanRU for the tip).
So you'll have to update it by yourself. I have taken a copy of the 'sp_sysmail_activate' procedure from a working SQL server (non-cluster) SP2 with CU8 (build 3257). You can simple run the code in SQL Server Management Studio :
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_sysmail_activate] Script Date: 08/13/2008 11:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
ALTER PROCEDURE [dbo].[sp_sysmail_activate]
AS
BEGIN
DECLARE @mailDbName sysname
DECLARE @mailDbId INT
DECLARE @mailEngineLifeMin INT
DECLARE @loggingLevel nvarchar(256)
DECLARE @loggingLevelInt int
DECLARE @parameter_value nvarchar(256)
DECLARE @localmessage nvarchar(max)
DECLARE @readFromConfigFile INT
DECLARE @rc INT
SET NOCOUNT ON
EXEC sp_executesql @statement = N'RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue'
EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime',
@parameter_value = @parameter_value OUTPUT
IF(@rc <> 0)
RETURN (1)
--ConvertToInt will return the default if @parameter_value is null or config value can't be converted
--Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600)
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ReadFromConfigurationFile',
@parameter_value = @parameter_value OUTPUT
--Try to read the optional read from configuration file:
SET @readFromConfigFile = dbo.ConvertToInt(@parameter_value, 1, 0)
--Try and get the optional logging level for the DatabaseMail process
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel',
@parameter_value = @loggingLevel OUTPUT
--Convert logging level into string value for passing into XP
SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2)
IF @loggingLevelInt = 1
SET @loggingLevel = 'Normal'
ELSE IF @loggingLevelInt = 3
SET @loggingLevel = 'Verbose'
ELSE -- default
SET @loggingLevel = 'Extended'
SET @mailDbName = DB_NAME()
SET @mailDbId = DB_ID()
EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @readFromConfigFile,
@mailEngineLifeMin, @loggingLevel
IF(@rc <> 0)
BEGIN
SET @localmessage = FORMATMESSAGE(14637)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
END
ELSE
BEGIN
SET @localmessage = FORMATMESSAGE(14638)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
END
RETURN @rc
END
There's only one final step, you should restart the mail process with the follwing SQL command :
EXEC msdb.dbo.sysmail_start_sp;
You could always restart the SQL Server if you prefer.
You should now be able to send a test email from the Database Mail object in SQL Server Management Studio from your SQL Server 2005 SP2 in a cluster.
December 2008 update :
I missed something, in the ‘Second problem :’, ‘sys_mail_activate’ is not the only stored procedure that you have to update. There is a few others :
- dbo.sp_ExternalMailQueueListener
- dbo.sp_RunMailQuery
- dbo.sp_send_dbmail
- dbo.sp_sysmail_activate
- dbo.sysmail_delete_mailitems_sp
- dbo.sysmail_delete_profile_sp
- dbo.sysmail_update_account_sp
- dbo.sp_ProcessResponse
- dbo.sp_validate_user
I’ve created a script to update those procedures to the same level as SQL Server 2005 SP2 with Cumulatives Updates 8. You can download my script
here.
May I suggest you backup the msdb database before playing with dbmail procs.
Great post by 
