Database Mail and Activation Error

Hi All,

I'm having some issues getting Database Mail to work with SQL Agent Job's which is doing my fruit !

I'm configuring the server using the following script:

DECLARE

@.DataFile varchar(1000),

@.LogFile varchar(1000),

@.BackupFile varchar(1000),

@.cmd Varchar(8000),

@.ServerName varchar(1000),

@.AccountName Varchar(1000),

@.EmailAddress Varchar(1000),

@.MailSubject VARCHAR(500),

@.FullPath varchar(400),

@.Drop varchar(100)

--Configure SQL Server DBMail

SET

@.AccountName = (SELECT REPLACE(@.@.ServerName, '\','-') + ' Email Profile')

SET

@.EmailAddress = (SELECT REPLACE(@.@.ServerName, '\','-') + '@.OurDomain.com')

SET

@.ServerName = (SELECT REPLACE(@.@.ServerName, '\','-'))

IF EXISTS

(SELECT Name FROM msdb.dbo.sysmail_profile WHERE NAME = @.AccountName)

BEGIN

PRINT 'Database Mail Profile Already Exists - Skiping Step'

END

ELSE

BEGIN

EXECUTE msdb.dbo.sysmail_add_account_sp

@.account_name = @.AccountName,

@.description = 'Mail account for administrative e-mail.',

@.email_address = @.EmailAddress,

@.replyto_address = 'SQL_Notify@.OurDomain.com',

@.display_name = @.ServerName,

@.mailserver_name = 'MailServer.net' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@.profile_name = @.AccountName,

@.description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@.profile_name = @.AccountName,

@.account_name = @.AccountName,

@.sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@.profile_name = @.AccountName,

@.principal_name = 'Public',

@.is_default = 1 ;

EXEC

msdb.dbo.sp_set_sqlagent_properties @.email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @.AccountName

SET

@.MailSubject = 'Database Mail Test Message from ' + @.@.SERVERNAME

PRINT

'Created DBMail Profile'

END

GO

DECLARE @.MailSubject VARCHAR(500)

DECLARE @.AccountName Varchar(1000)

SET

@.AccountName = (SELECT REPLACE(@.@.ServerName, '\','-') + ' Email Profile')

EXEC

msdb.dbo.sp_set_sqlagent_properties @.email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Email Profile'

SET

@.MailSubject = 'Database Mail Test Message from ' + @.@.SERVERNAME

--Check if DB Operator alredy exists; Create if not.

IF EXISTS (Select Name FROM MSDB.dbo.SysOperators WHERE NAME = 'SQL Notify')

BEGIN

PRINT 'Database Operator Already Exists - Skipping Step'

END

ELSE

BEGIN

EXEC msdb.dbo.sp_add_operator @.name=N'SQL Notify',@.enabled=1, @.pager_days=0, @.email_address=N'SQL_Notify@.OurDomain.com'

END

EXEC

msdb.dbo.sp_send_dbmail

@.profile_name = @.AccountName,

@.recipients = 'SQL_Notify@.OurDomain.com',

@.subject = @.MailSubject,

@.body = 'This is a Database Mail Test Message'

GO

OK - I get the test message, however when I setup a SQL Agent job later in the same script to alert me on failure; I dont get any mails through.

When I check the SysMail Event Log; I see the followign error:

error 2007-07-20 15:58:07.623 Activation failure. NULL NULL NULL 2007-07-20 15:58:07.623 sa

The SQLAgent is running under the same account as SQLServer; neither are local admin's on the machine. I've restatred SQLAgent using SSCM a number of times yet nothing comes through.

Mail Profile is started; Service Broker is enabled in MSDB

SQL Agent is configured with the correct profile; an operator is set as a failsafe operator.

Does anyone have any suggestions?

Try stopping and re-starting the SQL Agent.

I had the same issue.