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'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', 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'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', 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?????