February 10, 2015 at 5:27 am
Thanks Lowell,
Awesome script. You really saved lots of work.
March 25, 2015 at 12:01 pm
Thank you very much Lowell.
This is the script that keeps giving. Thanks a lot for this. It easily saved me a day of work. :w00t:
March 25, 2015 at 12:24 pm
Glad it helped you Rick!
with 20K+ hits on this thread, it's clear that it's probably helped more than just the folks who were kind enough to post a thank you!
glad it's helping!
Lowell
March 26, 2015 at 8:33 am
Lowell (3/25/2015)
Glad it helped you Rick!with 20K+ hits on this thread, it's clear that it's probably helped more than just the folks who were kind enough to post a thank you!
glad it's helping!
Heh - I have the topic in my briefcase. It's just one of those that's too handy to lose track of. Well done, Lowell.
April 4, 2015 at 10:27 am
Great script.. help me out a lot.
May 21, 2015 at 5:19 am
Lowell (9/8/2010)
As a solution to my own question, one of the real problems is that a CREDENTIAL is created to hold the password for the SMTP user; i do not know of any way to script out the password from the credentials.dayum.
anyway, with the exception of the password, which i would have to change in the generated script, this script below does what i was looking for: this may help others in the future, especially if you are like me and like to script everything for disaster recovery and rebuilding and stuff.
The code below Reverse Engineers Database Mail Settings.
USE msdb
GO
Declare @TheResults varchar(max),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
--CREATE Profile [' + p.name + ']
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ''' + p.name + ''',
@description = ''' + ISNULL(p.description,'') + ''';
END --IF EXISTS profile
'
+
'
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
--CREATE Account [' + a.name + ']
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',
@email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',
@display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',
@replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',
@description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',
@mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',
@mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',
@port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
@username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',
@password = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
@use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
@enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
END --IF EXISTS account
'
+ '
IF NOT EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = ''' + p.name + '''
AND a.name = ''' + a.name + ''')
BEGIN
-- Associate Account [' + a.name + '] to Profile [' + p.name + ']
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ''' + p.name + ''',
@account_name = ''' + a.name + ''',
@sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = ''' + p.name + '''
AND a.name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
END
*/
'
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id
;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
ItemSplit(
ItemOrder,
Item
) as (
SELECT N,
SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
FROM Tally
WHERE N < DATALENGTH(@vbCrLf + @TheResults)
--WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from ItemSplit
I will have you know that this thread is the first link in a google search for scripting out DB mail settings.
Thanks, Lowell. I'll take a look at your script.
May 27, 2015 at 10:47 am
Awesome script by Lowell.
I quick questions, if in a server multiple database mail accounts are configured. How to ensure which are the db mail accounts?
If I run :=
select * from msdb.dbo.sysmail_profile
It will throw details of operators, dbmail.
But if I run the below one, it is showing the mail accounts.
SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_account a ON a.account_id=pa.account_id
=I need to clean up all the duplicate/junk/extra dbmail accounts from multiple servers and looking for a std T sql to identify only the DBMAIL accounts which I 'll use in my procedure to clean them up across servers
Please suggest with your valuable advise.
Thanks.
May 27, 2015 at 11:41 am
SQL-DBA-01 (5/27/2015)
Awesome script by Lowell.I quick questions, if in a server multiple database mail accounts are configured. How to ensure which are the db mail accounts?
If I run :=
select * from msdb.dbo.sysmail_profile
It will throw details of operators, dbmail.
But if I run the below one, it is showing the mail accounts.
SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_account a ON a.account_id=pa.account_id
=I need to clean up all the duplicate/junk/extra dbmail accounts from multiple servers and looking for a std T sql to identify only the DBMAIL accounts which I 'll use in my procedure to clean them up across servers
Please suggest with your valuable advise.
The msdb.dbo.sysmail_profile table contains the database mail profiles.
The msdb.dbo.sysmail_account table contains the accounts.
The msdb.dbo.sysmail_profileaccount is a resolve table between msdb.dbo.sysmail_profile and msdb.dbo.sysmail_account.
The msdb.dbo.sysmail_server is the one that stores the server for the account.
You can join them together to query information about all the rows like this:
SELECT p.name profile_name, a.name account_name, a.email_address, s.servername
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
INNER JOIN msdb.dbo.sysmail_server s ON s.account_id = a.account_id
ORDER BY p.name, a.name;
From there, you can clean up whatever you need to clean up.
June 7, 2015 at 5:37 pm
Great script...makes it real easy to 'copy' settings to another server.
Think Microsoft should have this included as a RMB option in SSMS.
June 15, 2015 at 11:19 am
this worked perfectly! many thanks!
June 17, 2015 at 11:02 am
++HappyCustomer
Thanks, Lowell!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 30, 2015 at 2:37 pm
hi
can u pls tell me if mdf get full by data in it.so is there any solution to put more data in that mdf without adding disk.
July 1, 2015 at 4:38 am
giri10488 (6/30/2015)
hican u pls tell me if mdf get full by data in it.so is there any solution to put more data in that mdf without adding disk.
Please post your question as a brand new thread. Do not post it as a reply to an unrelated thread or you won't get an answer.
February 3, 2016 at 12:00 am
God Bless You SSChampion 🙂
March 6, 2018 at 4:30 am
Thanks +1.
I would recommend doing a print @TheResult for a better view of the script instead of the CTE at the end as some other poster has carefully pointed out.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply