A common topic for questions on SQL Server forums is how to plan and implement upgrades to SQL Server. Moving from old to new hardware or moving from one version of SQL Server to another. There are other circumstances where upgrades of other systems affect SQL Server DBAs.
For example, where I work at the moment there is an Microsoft Exchange (email) server upgrade in progress. It it being handled by a different team so I’m not wholly sure on the details but we are in a situation where there are currently 2 Exchange email servers – the old one and the new one. Users mail boxes are being transferred in a planned process but as we approach the old server being turned off we have to also make sure that our SQL Servers get updated to use the new SMTP server for all of the SQL Agent notifications, SSIS packages etc.
My servers have a number of profiles so that various jobs can send emails on behalf of various departments and different systems. This means there are lots of places that the old server name needs to be replaced by the new one.
Anyone who has set up DBMail and enjoyed the click-tastic odyssey of screens to create Profiles and Accounts and so on and so forth ought to seek some professional help in my opinion. It’s a nightmare of back and forth settings changes and it stinks. I wasn’t looking forward to heading into this mess of a UI and changing the old Exchange server name for the new one on all my SQL Instances for all of the accounts I have set up.
So I did what any Englishmen with a shed would do, I decided to take it apart and see if I can fix it another way. I took a guess that we are going to be working in MSDB and Books OnLine was remarkably helpful and amongst a lot of information told me about a couple of procedures that can be used to interrogate DBMail settings.
USE [msdb] -- It's where all the good stuff is kept
GO
EXEC dbo.sysmail_help_profile_sp;
EXEC dbo.sysmail_help_account_sp;
Both of these procedures take optional parameters with the same name – ID and Name. If you provide an ID or a name then the results you get back are for that specific Profile or Account. Otherwise you get details of all Profiles and Accounts on the server you are connected to.
As you can see (click for a bigger image), the Account has the SMTP server information in the servername column. We want to change that value to NewSMTP.Contoso.com.
Now it appears that the procedure we are looking at gets it’s data from the sysmail_account and sysmail_server tables, you can get the results the stored procedure provides if you run the code below.
SELECT [account_id] ,
[name] ,
[description] ,
[email_address] ,
[display_name] ,
[replyto_address] ,
[last_mod_datetime] ,
[last_mod_user]
FROM dbo.sysmail_account AS sa;
SELECT [account_id] ,
[servertype] ,
[servername] ,
[port] ,
[username] ,
[credential_id] ,
[use_default_credentials] ,
[enable_ssl] ,
[flags] ,
[last_mod_datetime] ,
[last_mod_user] ,
[timeout]
FROM dbo.sysmail_server AS sms
Now, we have no real idea how these tables are linked and whether making an update direct to one or other of them is going to do what we want or whether it will entirely cripple our ability to send email from SQL Server so we wont touch those tables with any UPDATE TSQL. So, back to Books OnLine then and we find sysmail_update_account_sp. It’s exactly what we need. The examples in BOL take the form (as below) of having every parameter explicitly defined.
Not wanting to totally obliterate the existing values by not passing values in all of the parameters I set to writing some code to gather the existing data from the tables and re-write the SMTP server name and then execute the resulting TSQL.
IF OBJECT_ID('tempdb..#sysmailprofiles') IS NOT NULL
DROP TABLE #sysmailprofiles
GO
CREATE TABLE #sysmailprofiles
(
account_id INT ,
[name] VARCHAR(50) ,
[description] VARCHAR(500) ,
email_address VARCHAR(500) ,
display_name VARCHAR(500) ,
replyto_address VARCHAR(500) ,
servertype VARCHAR(10) ,
servername VARCHAR(100) ,
port INT ,
username VARCHAR(100) ,
use_default_credentials VARCHAR(1) ,
ENABLE_ssl VARCHAR(1)
)
INSERT [#sysmailprofiles]
( [account_id] ,
[name] ,
[description] ,
[email_address] ,
[display_name] ,
[replyto_address] ,
[servertype] ,
[servername] ,
[port] ,
[username] ,
[use_default_credentials] ,
[ENABLE_ssl]
)
EXEC [dbo].[sysmail_help_account_sp]
DECLARE @TSQL NVARCHAR(1000)
SELECT TOP 1
@TSQL = 'EXEC [dbo].[sysmail_update_account_sp] @account_id = '
+ CAST([s].[account_id] AS VARCHAR(20)) + ', @account_name = '''
+ [s].[name] + '''' + ', @email_address = N''' + [s].[email_address]
+ '''' + ', @display_name = N''' + [s].[display_name] + ''''
+ ', @replyto_address = N''' + s.replyto_address + ''''
+ ', @description = N''' + [s].[description] + ''''
+ ', @mailserver_name = ''NEWSMTP.contoso.com'''
+ +', @mailserver_type = ' + [s].[servertype] + ', @port = '
+ CAST([s].[port] AS VARCHAR(20)) + ', @username = '
+ COALESCE([s].[username], '''''') + ', @use_default_credentials ='
+ CAST(s.[use_default_credentials] AS VARCHAR(1)) + ', @enable_ssl ='
+ [s].[ENABLE_ssl]
FROM [#sysmailprofiles] AS s
WHERE [s].[servername] = 'SMTP.Contoso.com'
SELECT @tsql
EXEC [sys].[sp_executesql] @tsql
This worked well for me and testing the email function EXEC dbo.sp_send_dbmail afterwards showed that the settings were indeed using our new Exchange server.
It was only later in writing this blog that I tried running the sysmail_update_account_sp procedure with only the SMTP server name parameter value specified. Despite what Books OnLine might intimate, you can do this and only the values for parameters specified get changed. If a parameter is not specified in the execution of the procedure then the values remain unchanged. This renders most of the above script unnecessary as I could have simply specified the account_id that I want to amend and the new value for the parameter I want to update.
EXEC sysmail_update_account_sp @account_id = 1, @mailserver_name = 'NEWSMTP.Contoso.com'
This wasn’t going to be the main reason for this post, it was meant to describe how to capture values from a stored procedure and use them in dynamic TSQL but instead we are here and (re)learning the fact that Books Online is a little flawed in places. It is a fantastic resource for anyone working with SQL Server but the reader must adopt an enquiring frame of mind and use a little curiosity to try simple variations on examples to fully understand the code you are working with. I think the author(s) of this part of Books OnLine missed an opportunity to include a third example that had fewer than all parameters specified to give a lead to this method existing.
