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

最新推荐文章于 2024-10-03 21:40:23 发布

weixin_34326429

最新推荐文章于 2024-10-03 21:40:23 发布

阅读量248

点赞数

文章标签：数据库 ui

最近遇到这样一个案例，需要修改所有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_FORWARD 
   FOR 
   SELECT 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 = 0 
   BEGIN 
     
       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; 
   END 
     
   CLOSE EmailAccount_Cursor; 
     
   DEALLOCATE EmailAccount_Cursor; 
  