Bulk Modify Dbmail configuration for all servers

DECLARE @EmailAccount sysname;

DECLARE @SmtpServer sysname;

DECLARE @EmailAddress NVARCHAR (120);

DECLARE @EmailSuffix NVARCHAR (32);

DECLARE @NewEamilAddress NVARCHAR (120);

--declare @ActualEmailSuffix NVARCHAR (+) = ' xxxx.com '; SQL Server 2005 does not support this feature and will report 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

, sa.email_address

INNER JOIN [msdb]. [dbo]. [Sysmail_account] SA

On Ss.[account_id]=sa. [account_id];

OPEN Emailaccount_cursor;

FETCH NEXT from Emailaccount_cursor to @EmailAccount, @SmtpServer, @EmailAddress;

While @ @FETCH_STATUS = 0

BEGIN

IF LTRIM (RTRIM (@SmtpServer)) [email protected]

BEGIN

EXECUTE msdb.dbo.sysmail_update_account_sp

@account_name = @EmailAccount

, @[email protected];

PRINT @SmtpServer;

PRINT @EmailAccount;

END;

SET @EmailSuffix =substring (@EmailAddress, CHARINDEX (' @ ', @EmailAddress) +1, LEN (@EmailAddress)-charindex (' @ ', @ EmailAddress))

IF @[email protected]

BEGIN

SET @NewEamilAddress = REPLACE (@EmailAddress, @EmailSuffix, @ActualEmailSuffix);

EXECUTE msdb.dbo.sysmail_update_account_sp

@account_name = @EmailAccount

, @[email protected]

, @[email protected];

PRINT @EmailAccount;

PRINT @NewEamilAddress;

END;

FETCH NEXT from Emailaccount_cursor to @EmailAccount, @SmtpServer, @EmailAddress;

END

CLOSE Emailaccount_cursor;

Deallocate emailaccount_cursor;

Bulk Modify Dbmail configuration for all servers