April 19, 2010 at 4:08 am
Hi,
I need to setup database mail configuration profile in more than 50 servers.It will make the job easier if i do that through script. Can you share me the proper T-Sql script or give me the link where i can find the script ?
Thanks in Advance.
April 19, 2010 at 11:21 am
You might want to search BOL for "Database Mail [SQL Server], configuration scripts".
There are links to sample procs you could use/modify.
April 19, 2010 at 1:33 pm
You'll need to change the 'X''s appropriately with your information, but this should work for you:
IF (SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb') = 0
BEGIN
ALTER DATABASE msdb
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
END
IF (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Show Advanced Options') = 0
BEGIN
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE WITH OVERRIDE
END
IF (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Database Mail XPs') = 0
BEGIN
EXEC sp_configure 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
END
IF NOT EXISTS(SELECT name FROM msdb.dbo.sysmail_account WHERE name = 'X')
BEGIN
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'X',
@email_address = 'X',
@replyto_address = 'X',
@display_name = 'X',
@mailserver_name = 'X'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'X'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'X',
@account_name = 'X',
@sequence_number = 1
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'X',
@principal_name = 'public',
@is_default = 1;
END
April 20, 2010 at 3:54 am
Hi. thanks for your reply and query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply