Changing database mail account SMTP servers

SET NOCOUNT ON

DECLARE@SQLVarchar(MAX),
@PWVarchar(50),
@NewSMTPVarchar(15),
@CmdVarchar(50),
@TestPingBit
SELECT@SQL= '',
@PW= NULL,-- Not all users have the same password!!
@NewSMTP= NULL,-- <Name, IP address. NULL to keep using the existing SMTP server.>
@TestPing= 1

-- Make sure the supplied SMTP server is online.
IF @NewSMTP IS NOT NULL AND @TestPing = 1
BEGIN
IF OBJECT_ID('TempDB..#TmpPing') IS NOT NULL
DROP TABLE #TmpPing

CREATE TABLE #TmpPing (PingResponse Varchar(8000))

SET @Cmd = 'Ping ' + @NewSMTP + ' -n 1'
INSERT #TmpPing EXEC xp_CmdShell @Cmd

IF EXISTS(
SELECT* 
FROM#TmpPing 
WHERE   PingResponse LIKE 'Ping request could not find host%' 
OR PingResponse LIKE '%TTL Expired in Transit%'
OR PingResponse LIKE '%Destination Host Unreachable%'
OR PingResponse LIKE '%Request Timed Out%'
OR PingResponse LIKE '%Unknown Host%'
)
BEGIN
PRINT 'The supplied SMTP server cannot be reached. Either it is unreachable, the name or IP is incorrect or pinging the server is not allowed.'
SELECT * FROM #TmpPing
RETURN
END
END

SELECT@SQL = @SQL + -- Remove these @SQLs if have lots of accounts.
'-- Delete the existing account
EXECmsdb.dbo.sysmail_delete_account_sp 
@account_name = '''+A.Name+'''
GO

-- Create an account
EXECmsdb.dbo.sysmail_add_account_sp
@account_name= '''+A.Name+''',
@email_address= '''+A.Email_Address+''',
@display_name= '+CASE WHEN A.display_nameIS NULL THEN 'NULL' ELSE ''''+A.display_name+''''END + ',
@replyto_address= '+CASE WHEN A.ReplyTo_AddressIS NULL THEN 'NULL' ELSE ''''+A.ReplyTo_Address+''''END + ',
@description= '+CASE WHEN A.[Description]IS NULL THEN 'NULL' ELSE ''''+A.[Description]+''''END + ',
@mailserver_name= '''+ISNULL(@NewSMTP,S.ServerName)+''',
@mailserver_type= '''+S.Servertype+''',
@port= '''+CAST(S.Port AS Varchar)+''',
@username= '+CASE WHEN S.UserNameIS NULL THEN 'NULL' ELSE ''''+S.UserName+'''' END + ',
@password= '+CASE WHEN @PWIS NULL THEN 'NULL' ELSE ''''+@PW+'''' END + ',
@use_default_credentials= '''+CAST(use_default_credentials AS Varchar)+''',
@enable_ssl= '''+CAST(enable_ssl AS Varchar)+'''
GO

-- Add the account to a profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name= '''+P.Name+''',
@account_name= '''+A.Name+''',
@sequence_number= '+CAST(PA.Sequence_number AS Varchar) + '
GO
'
--SELECT* 
FROMmsdb.dbo.sysmail_profile P
LEFT JOIN msdb.dbo.sysmail_principalprofile PP
ON P.profile_id = PP.profile_id
INNER JOIN msdb.dbo.sysmail_profileaccount PA
ON P.profile_id = PA.profile_id
INNER JOIN msdb.dbo.sysmail_account A
ON PA.account_id = A.account_id
INNER JOIN msdb.dbo.sysmail_server S
ON A.account_id = S.account_id

PRINT@SQL