批量修改所有服务器的dbmail配置

如果使用SSMS客户端的UI界面去修改的话， 那么多服务器一台一台去修改，不仅费时费力，而且枯燥无聊。只能使用脚本，一旦写好一个脚本，而后使用Multiple Server Query Execution（极力推荐使用这个管理、维护数据库），执行一次脚本，全部搞定。剩下的时间你可以喝喝茶、学习下新知识！

<span style="color: #0000ff">DECLARE</span> @EmailAccount sysname;

<span style="color: #0000ff">DECLARE</span> @SmtpServer sysname;

<span style="color: #0000ff">DECLARE</span> @EmailAddress NVARCHAR(120);

<span style="color: #0000ff">DECLARE</span> @EmailSuffix NVARCHAR(32);

<span style="color: #0000ff">DECLARE</span> @NewEamilAddress NVARCHAR(120);

--<span style="color: #0000ff">DECLARE</span> @ActualEmailSuffix NVARCHAR(32)=<span style="color: #006080">‘xxxx.com‘</span>;  <span style="color: #0000ff">SQL</span> Server 2005不支持此功能，会报Cannot assign a <span style="color: #0000ff">default</span> <span style="color: #0000ff">value</span> <span style="color: #0000ff">to</span> a <span style="color: #0000ff">local</span> <span style="color: #0000ff">variable</span>.

<span style="color: #0000ff">DECLARE</span> @ActualEmailSuffix NVARCHAR(32);

<span style="color: #0000ff">DECLARE</span> @ActualSmtpServer sysname;

<span style="color: #0000ff">SET</span> @ActualEmailSuffix=<span style="color: #006080">‘xxx.com‘</span>;

<span style="color: #0000ff">SET</span> @ActualSmtpServer=<span style="color: #006080">‘192.168.xxx.xxx‘</span>;

<span style="color: #0000ff">DECLARE</span> EmailAccount_Cursor <span style="color: #0000ff">CURSOR</span> FAST_FORWARD

<span style="color: #0000ff">FOR</span>

<span style="color: #0000ff">SELECT</span> sa.[name]        

      ,ss.[servername] 

      ,sa.email_address

  <span style="color: #0000ff">FROM</span> [msdb].[dbo].[sysmail_server] ss 

  <span style="color: #0000ff">INNER</span> <span style="color: #0000ff">JOIN</span> [msdb].[dbo].[sysmail_account] sa

  <span style="color: #0000ff">ON</span> ss.[account_id]=sa.[account_id];

<span style="color: #0000ff">OPEN</span> EmailAccount_Cursor;

<span style="color: #0000ff">FETCH</span> <span style="color: #0000ff">NEXT</span> <span style="color: #0000ff">FROM</span> EmailAccount_Cursor <span style="color: #0000ff">INTO</span> @EmailAccount, @SmtpServer,@EmailAddress;

<span style="color: #0000ff">WHILE</span> @@FETCH_STATUS = 0

<span style="color: #0000ff">BEGIN</span>

    <span style="color: #0000ff">IF</span> LTRIM(RTRIM(@SmtpServer))!=@ActualSmtpServer

    <span style="color: #0000ff">BEGIN</span>

        <span style="color: #0000ff">EXECUTE</span> msdb.dbo.sysmail_update_account_sp

             @account_name = @EmailAccount

            ,@mailserver_name=@ActualSmtpServer;

        <span style="color: #0000ff">PRINT</span> @SmtpServer;

        <span style="color: #0000ff">PRINT</span> @EmailAccount;

    <span style="color: #0000ff">END</span>;

    <span style="color: #0000ff">SET</span> @EmailSuffix=<span style="color: #0000ff">SUBSTRING</span>(@EmailAddress,CHARINDEX(<span style="color: #006080">‘@‘</span>,@EmailAddress)+1, LEN(@EmailAddress) -CHARINDEX(<span style="color: #006080">‘@‘</span>,@EmailAddress))

    <span style="color: #0000ff">IF</span> @EmailSuffix!=@ActualEmailSuffix

    <span style="color: #0000ff">BEGIN</span>

        <span style="color: #0000ff">SET</span> @NewEamilAddress= REPLACE(@EmailAddress,@EmailSuffix,@ActualEmailSuffix);

        <span style="color: #0000ff">EXECUTE</span> msdb.dbo.sysmail_update_account_sp

             @account_name = @EmailAccount

            ,@email_address=@NewEamilAddress

            ,@mailserver_name=@SmtpServer;

        <span style="color: #0000ff">PRINT</span> @EmailAccount;

        <span style="color: #0000ff">PRINT</span> @NewEamilAddress;

    <span style="color: #0000ff">END</span>;

    <span style="color: #0000ff">FETCH</span> <span style="color: #0000ff">NEXT</span> <span style="color: #0000ff">FROM</span> EmailAccount_Cursor <span style="color: #0000ff">INTO</span> @EmailAccount, @SmtpServer,@EmailAddress;

<span style="color: #0000ff">END</span>

<span style="color: #0000ff">CLOSE</span> EmailAccount_Cursor;

<span style="color: #0000ff">DEALLOCATE</span> EmailAccount_Cursor;

批量修改所有服务器的dbmail配置

标签：max   direct   fetch   ack   als   from   学习   out   cannot