August 30, 2010 at 2:51 pm
Any input will be greatly appreciated in advance.
August 30, 2010 at 3:23 pm
Check out this link
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 8:12 pm
Thank you so much WayneS!
Is it possible to generate the script from an existing profile?
August 30, 2010 at 8:16 pm
SQL ORACLE (8/30/2010)
Thank you so much WayneS!Is it possible to generate the script from an existing profile?
Not that I'm aware of. I'd like this also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 9:31 pm
SQL ORACLE (8/30/2010)
Thank you so much WayneS!Is it possible to generate the script from an existing profile?
I just wrote this script to get all of the database mail profiles and accounts, and generates the statements to add this to a server. Since I just wrote it, it only has limited testing.
/******************************************************************************
Script out the statements required to build the database mail accounts and
profiles, and connect the accounts to the profiles.
*******************************************************************************
Usage:
1. execute this script.
2. Copy the results to a new query window.
3. In the lines that add the accounts (sysmail_add_account_sp), set the password as appropriate.
*******************************************************************************
MODIFICATION LOG
08/30/2010 Wayne Sheffield Initial Creation.
******************************************************************************/
-- get list of profiles
if object_id('tempdb..#tmp_sysmail_help_profile') IS NOT NULL DROP TABLE #tmp_sysmail_help_profile;
create table #tmp_sysmail_help_profile(profile_id int, name nvarchar(128), description nvarchar(256) null);
insert into #tmp_sysmail_help_profile(profile_id, name, description) exec msdb.dbo.sysmail_help_profile_sp;
-- get list of accounts
if object_id('tempdb..#tmp_sysmail_help_account') IS NOT NULL DROP TABLE #tmp_sysmail_help_account;
create table #tmp_sysmail_help_account(account_id int, name nvarchar(128), description nvarchar(256) null, email_address nvarchar(128) null, display_name nvarchar(128) null, replyto_address nvarchar(128) null, servertype nvarchar(128), servername nvarchar(128), port int, username nvarchar(128), use_default_credentials bit, enable_ssl bit);
insert into #tmp_sysmail_help_account(account_id, name, description, email_address, display_name, replyto_address, servertype, servername, port, username, use_default_credentials, enable_ssl) exec msdb.dbo.sysmail_help_account_sp;
-- get list of profile accounts
IF object_id('tempdb..#tmp_sysmail_help_profileaccount') IS NOT NULL DROP TABLE #tmp_sysmail_help_profileaccount;
create table #tmp_sysmail_help_profileaccount (profile_id int, profile_name nvarchar(128), account_id int, account_name nvarchar(128), sequence_number int);
insert into #tmp_sysmail_help_profileaccount (profile_id, profile_name, account_id, account_name, sequence_number) exec msdb.dbo.sysmail_help_profileaccount_sp;
WITH CTE AS
(
-- Add the accounts
SELECT OrderID = 1,
RN = row_number() OVER (ORDER BY (SELECT 0)),
Sql_Cmd =
'IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = ' + quotename(name, char(39)) +')
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ' + quotename(name, char(39)) + ',
@description = ' + quotename(description, char(39)) + ',
@email_address = ' + quotename(email_address, char(39)) + ',
@display_name = ' + quotename(display_name, char(39)) + ',
@replyto_address = ' + quotename(replyto_address, char(39)) + ',
@mailserver_type = ' + quotename(servertype, char(39)) + ',
@mailserver_name = ' + quotename(servername, char(39)) + ',
@port = ' + convert(varchar(4), port) + ',
@username = ' + IsNull(quotename(username, char(39)), 'NULL') + ',
@use_default_credentials = ' + convert(char(1), use_default_credentials) + ',
@enable_ssl = ' + convert(char(1), enable_ssl) + ',
@password = NULL;'
FROM #tmp_sysmail_help_account
UNION ALL
-- Add the profiles
SELECT OrderId = 2,
RN = row_number() OVER (ORDER BY (SELECT 0)),
'IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ' + quotename(name, char(39)) + ')
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ' + Quotename(name, char(39)) + ',
@description = ' + quotename(description, char(39)) + ';'
FROM #tmp_sysmail_help_profile AS tshp
UNION ALL
-- Add the profile accounts
SELECT OrderID = 3,
RN = row_number() OVER (PARTITION BY profile_id ORDER BY (sequence_number)),
'IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_profileaccount spa
JOIN msdb.dbo.sysmail_profile sp ON spa.profile_id = sp.profile_id
JOIN msdb.dbo.sysmail_account sa ON spa.account_id = sa.account_id
WHERE sp.name = ' + quotename(profile_name, char(39)) + '
and sa.name = ' + quotename(account_name, char(39)) + '
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ' + quotename(profile_name, char(39)) + ',
@account_name = ' + quotename(account_name, char(39)) + ',
@sequence_number = ' + convert(varchar(10), sequence_number)
FROM #tmp_sysmail_help_profileaccount
)
SELECT Sql_Cmd
FROM CTE
ORDER BY OrderID, RN
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply