服务器
最近遇到这样一个案例，需要修改所有sql server的database mail的smtp，原来的smtp为10.xxx.xxx.xxx, 现在需要修改为192.168.xxx.xxx， 另外需要规范邮件地址，以前这类邮件servername@yoursqldba.com的后缀需要修改为servername@xxxx.com(信息做了脱敏处理)。
如果使用ssms客户端的ui界面去修改的话， 那么多服务器一台一台去修改，不仅费时费力，而且枯燥无聊。只能使用脚本，一旦写好一个脚本，而后使用multiple server query execution（极力推荐使用这个管理、维护数据库），执行一次脚本，全部搞定。剩下的时间你可以喝喝茶、学习下新知识！
declare @emailaccount sysname;declare @smtpserver sysname;declare @emailaddress nvarchar(120);declare @emailsuffix nvarchar(32);declare @neweamiladdress nvarchar(120);--declare @actualemailsuffix nvarchar(32)=\\\'xxxx.com\\\'; sql server 2005不支持此功能，会报cannot assign a default value to a local variable.declare @actualemailsuffix nvarchar(32);declare @actualsmtpserver sysname;set @actualemailsuffix=\\\'xxx.com\\\';set @actualsmtpserver=\\\'192.168.xxx.xxx\\\';declare emailaccount_cursor cursor fast_forwardforselect sa.[name] ,ss.[servername] ,sa.email_address from [msdb].[dbo].[sysmail_server] ss inner join [msdb].[dbo].[sysmail_account] sa on ss.[account_id]=sa.[account_id];open emailaccount_cursor;fetch next from emailaccount_cursor into @emailaccount, @smtpserver,@emailaddress;while @@fetch_status = 0begin if ltrim(rtrim(@smtpserver))!=@actualsmtpserver begin execute msdb.dbo.sysmail_update_account_sp @account_name = @emailaccount ,@mailserver_name=@actualsmtpserver; print @smtpserver; print @emailaccount; end; set @emailsuffix=substring(@emailaddress,charindex(\\\'@\\\',@emailaddress) 1, len(@emailaddress) -charindex(\\\'@\\\',@emailaddress)) if @emailsuffix!=@actualemailsuffix begin set @neweamiladdress= replace(@emailaddress,@emailsuffix,@actualemailsuffix); execute msdb.dbo.sysmail_update_account_sp @account_name = @emailaccount ,@email_address=@neweamiladdress ,@mailserver_name=@smtpserver; print @emailaccount; print @neweamiladdress; end; fetch next from emailaccount_cursor into @emailaccount, @smtpserver,@emailaddress;endclose emailaccount_cursor;deallocate emailaccount_cursor;