Shulou(Shulou.com)06/02 Report--

Recently encountered such a case, need to modify all SQL Server Database Mail SMTP, the original SMTP to 10.xxx.xxx.xxx, now need to change to 192.168.xxx.xxx, and also need to standardize e-mail address, previously this kind of mail ServerName@yoursqldba.com suffix needs to be changed to ServerName@xxxx.com (the information has been desensitized).

If you use the UI interface of the SSMS client to modify, then many servers to modify one, it is not only time-consuming, but also boring. You can only use a script, once you have written a script, then use Multiple Server Query Execution (which is highly recommended to manage and maintain the database), execute the script once, and get it all done. The rest of the time you can drink tea and learn new knowledge!

DECLARE @ EmailAccount sysname;DECLARE @ SmtpServer sysname;DECLARE @ EmailAddress NVARCHAR; DECLARE @ EmailSuffix NVARCHAR (32); DECLARE @ NewEamilAddress NVARCHAR (120);-- DECLARE @ ActualEmailSuffix NVARCHAR (32) = 'xxxx.com'; SQL Server 2005 does not support this feature, 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.s. [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 @ NewEamilAddress= REPLACE Suffix BEGIN SET @ NewEamilAddress= REPLACE EXECUTE msdb.dbo.sysmail_update_account_sp @ account_name = @ EmailAccount, @ email_address=@NewEamilAddress, @ mailserver_name=@SmtpServer; PRINT @ EmailAccount; PRINT @ EmailAccount, @ SmtpServer,@EmailAddress;ENDCLOSE EmailAccount_Cursor;DEALLOCATE EmailAccount_Cursor

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.