Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Script Out Database Mail Settings? Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 2:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 9:16 AM
Points: 4, Visits: 242
Very helpful, script allows me to uniformly add SMTP mail to various SQL servers from one original source - thank you
Post #1446719
Posted Monday, June 24, 2013 5:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:49 AM
Points: 18, Visits: 477
I love Powershell

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$Server = "ServerName";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Server
$srv.Mail.Script();


http://sqldbawithabeard.com Its where I blog

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1466695
Posted Monday, June 24, 2013 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 AM
Points: 12,923, Visits: 32,282
mrrobsewell (6/24/2013)
I love Powershell

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$Server = "ServerName";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Server
$srv.Mail.Script();


That's interesting; i bet with some enhancements, that could do the same thing my script was;
i see quite a few differences, so with a 1:1 comparison, my little script is still better, but i'd have to wrestle with powershell and see if I can coax all the same settings out that my script does;

i bet the If Not Exists() is just a setting in SMO.

The scripting of an account in powershell needs a lot of work;
EXEC msdb.dbo.sysmail_add_account_sp 
@account_name=N'sqlnotifications@gmail.com',
@email_address=N'sqlnotifications@gmail.com',
@display_name=N'SQL Notifications',
@replyto_address=N'sqlnotifications@gmail.com',
@description=N'Google gMail account'

but my script gets quite a few more settings:

  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = 'sqlnotifications@gmail.com')
BEGIN
--CREATE Account [sqlnotifications@gmail.com]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'sqlnotifications@gmail.com',
@email_address = 'sqlnotifications@gmail.com',
@display_name = 'SQL Notifications',
@replyto_address = 'sqlnotifications@gmail.com',
@description = 'Google gMail account',
@mailserver_name = 'smtp.googlemail.com',
@mailserver_type = 'SMTP',
@port = '587',
@username = 'sqlnotifications@gmail.com',
@password = 'NotTheRealPassword',
@use_default_credentials = 0 ,
@enable_ssl = 0 ;
END --IF EXISTS account



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1466720
Posted Thursday, February 20, 2014 10:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 10:27 PM
Points: 1, Visits: 7
This is brilliant, thanks Lowell.
Post #1543820
Posted Friday, April 4, 2014 5:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 25, Visits: 277
Very nice, Lowell!! Thank you!

Blog: http://sqlexchange.wordpress.com
Post #1558449
Posted Friday, May 30, 2014 7:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:03 AM
Points: 94, Visits: 31
Thank you for this extremely useful script. I was just setting up our new server and had disabled some notices in procedures pending learning (again) how to set up email and which accounts we had used when we set up the other server three years ago.

I found this thread and your script. Copied it. Ran it. Ran the result. Done.

That's a nice thing you did. Many thanks.
Post #1576050
Posted Friday, May 30, 2014 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 AM
Points: 12,923, Visits: 32,282
Thanks for the feedback Charles! It still gives me that warm feeling of a job well done when someone says i helped them.

I think this one has been a script a lot of folks have added to their toolbelt;
So glad it worked out well for you; I guess the only thing you had to do was find and change the password for your SMTP settings?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1576258
Posted Tuesday, September 2, 2014 9:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:20 PM
Points: 19, Visits: 203
A good script that I have used a few times. Just found out today that because it is based on profiles (inner join ...) it does not script accounts that don't have a profile. I added the following ...

-- Accounts without profiles (added 4-Sep-2014)
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + a.name + ' (no 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
--#################################################################################################
-- Drop Settings For ' + a.name + '
--#################################################################################################
/*
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
*/
'
FROM msdb.dbo.sysmail_account a
LEFT JOIN msdb.dbo.sysmail_profileaccount pa ON a.account_id = pa.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
WHERE pa.profile_id IS NULL
Post #1609870
Posted Saturday, November 1, 2014 12:49 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 3:31 PM
Points: 98, Visits: 368
This is an awesome script. I am adding it to my box of scripts and putting a reference to this thread int he comment block so that I can't forget where it came from.
Dave
Post #1630315
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse