October 8, 2009 at 1:33 pm
How to configure sql mail
October 8, 2009 at 1:42 pm
Here's the (anonymized) script I use to set up Database Mail on all new SQL installations. Please change the variables to ones appropriate for your environment.
MJM
--******************************************************************************************
--Purpose : The purpose of this script is to set up database Mail on a server and it enables the server for Database Mail.
-- It also makes the profile 'SQLDefault' Profile the default public profile for users in the msdb database.
-- Please review the variable values before you run this script. There are two sections in the script where
-- the variables are set.
--Date : 08/12/2008
--Usage : To setup the database mail simply run this script once in the master databse. After it is run
-- send a test mail by using a similar example
--EXEC msdb.dbo.sp_send_dbmail @recipients='youre-mail@domain.com',@subject = 'Test Mail', @body = 'Test Succeeded'
--******************************************************************************************
DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);
-- Profile name. Replace with the name for your profile
SET @profile_name = 'SQLDefault';
-- Account information. Replace with the information for your account.
SET @account_name = 'DBMail';
SET @SMTP_servername = 'smtpserver.domain';
SET @email_address = 'smtpaddr@domain.com';
SET @display_name = 'Database Server ' + @@ServerName;
-- 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 (SQLDefault) 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 (DBMail) 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,
@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 (DBMail).', 16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (SQLDefault).', 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 (DBMail).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
DECLARE @account_name sysname,
@profile_name sysname;
SET @account_name = 'DBMail';
SET @profile_name = 'SQLDefault';
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public',
@profile_name = @profile_name,
@is_default = 1 ;
Go
--Enabling Database Mail
exec sp_configure 'show advanced', 1;
GO
RECONFIGURE
GO
exec sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
--Create Operator
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'OperatorName',
@enabled=1,
@pager_days=0,
@email_address=N'smtpaddr@domain.com'
GO
-- Enable Mail Profile for SQL Agent
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'SQLDefault'
GO
October 8, 2009 at 2:17 pm
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply