[Setup] Setting up Database Mail on 2005 servers

Just a little script to set up Database Mail on 2005 boxes. I used to use XP_SMTP_MAIL, since the IMAP mail in SQL Server 2000 was a POS. This is considerably better.

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Database_Email',
    @description = 'Mail account for use by all database users.',
    @email_address = 'Database_Email@yourcompanyname.com',
    @replyto_address = 'Database_Email@yourcompanyname.com',
    @display_name = 'Database_Email',
    @mailserver_name = 'mail.yourcompanyname.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database_Email',
    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database_Email',
    @account_name = 'Database_Email',
    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database_Email',
    @principal_name = 'public',
    @is_default = 1 ;

go
--Enable advanced options
sp_configure 'show advanced options',1
go
RECONFIGURE
go
--Now enable the server to send mail
sp_configure 'Database Mail XPs',1
go
reconfigure
go

--test mail
declare @test varchar(50)
select @test = 'Test Email from ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database_Email',
    @recipients = 'you@yourcompanyname.com',
    @subject = @test