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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy