How to setup database mail in SQL Server 2005?
Here is a small code snippet for setting up database mail.
Please make sure to do following before setting up database mail
- Enable database mail in SQL Server Surface Area configuration by clicking on “Surface area configuration for features”
- Select server instance
- Click on database and enable database mail SPs
- Click on SQL mail and enable SQL Mail SPs
- Now you are all set for creating and managing database mail profiles
Here is a script to do it
-------------------------------------------------------------
-- Create a Database Mail profile, an SMTP account and
-- associates the account to the profile.
--
-------------------------------------------------------------
DECLARE @profile_name sysname,
@account_name sysname,
@description nvarchar(256),
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);
-- Profile name. Replace with the name for your profile
SET @profile_name = 'SQLMAIL';
-- Account information. Replace with the information for your account.
SET @account_name = 'SQLMAIL';
SET @SMTP_servername = 'smtp.MyCompany.com';
SET @email_address = 'sqlsa@MyCompany.com';
SET @display_name = 'SQLMAIL';
SET @description = 'Mail account for Production Maintenance.'
-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile SQLSA already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account SQLSA already exists.', 16, 1) ;
GOTO done;
END;
-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;
DECLARE @rv INT;
-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@description = @description,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account SQLSA.', 16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name,
@description = @description;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile SQLSA.', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;
-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account SQLSA.', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
