replaced http://serverfault.com/ with https://serverfault.com/
Source Link

I used this code from this answerthis answer to the Server Fault question Renamed a SQL Server, however maintenance plans still have old nameRenamed a SQL Server, however maintenance plans still have old name:

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

And to rename:

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = 'package GUID'

I used this code from this answer to the Server Fault question Renamed a SQL Server, however maintenance plans still have old name:

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

And to rename:

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = 'package GUID'

I used this code from this answer to the Server Fault question Renamed a SQL Server, however maintenance plans still have old name:

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

And to rename:

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = 'package GUID'
Moved the part that should have been a suggested edit to Edward's answer. Improved link on the remainder.
Source Link
Paul White
  • 65.3k
  • 25
  • 364
  • 566

Sorry I don't have enough rep onused this site to post a comment @Edward Dortland: The link iscode from NancySon and I would also recommend posting the context of the article or the answer in case things get broken and lost:

Maintenance Plans Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

use msdb

DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'

-- set the new server name to the current server name

declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT    id
FROM         sysssispackages
WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix


declare @planid uniqueidentifier
fetch next from PlansToFix into @planid

while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

end
fetch next from PlansToFix into @planid  -- get the next plan

end

close PlansToFix
deallocate PlansToFix


----- This will also handle the packages that have a tag such as 
----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>


  
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername = @@servername
declare @xml as varchar(max)
declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor FOR 
SELECT id
FROM sysssispackages
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

OPEN PlansToFix
declare @planid uniqueidentifier
fetch next from PlansToFix into @planid 
while (@@fetch_status<>-1) -- for each plan 
begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) 
from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
end
fetch next from PlansToFix into @planid -- get the next plan
end
close PlansToFix
deallocate PlansToFix

From: https://msdn.microsoft.com/en-us/library/ms143799(SQL.100).aspx

However I used the following linkthis answer from serverfaultto the Server Fault question Renamed a SQL Server, however maintenance plans still have old name:

Sorry I don't have enough rep on this site to post a comment @Edward Dortland: The link is from NancySon and I would also recommend posting the context of the article or the answer in case things get broken and lost:

Maintenance Plans Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

use msdb

DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'

-- set the new server name to the current server name

declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT    id
FROM         sysssispackages
WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix


declare @planid uniqueidentifier
fetch next from PlansToFix into @planid

while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

end
fetch next from PlansToFix into @planid  -- get the next plan

end

close PlansToFix
deallocate PlansToFix


----- This will also handle the packages that have a tag such as 
----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>


  
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername = @@servername
declare @xml as varchar(max)
declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor FOR 
SELECT id
FROM sysssispackages
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

OPEN PlansToFix
declare @planid uniqueidentifier
fetch next from PlansToFix into @planid 
while (@@fetch_status<>-1) -- for each plan 
begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) 
from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
end
fetch next from PlansToFix into @planid -- get the next plan
end
close PlansToFix
deallocate PlansToFix

From: https://msdn.microsoft.com/en-us/library/ms143799(SQL.100).aspx

However I used the following link from serverfault:

I used this code from this answer to the Server Fault question Renamed a SQL Server, however maintenance plans still have old name:

Source Link
AhsenBaig
  • 183
  • 1
  • 7

Sorry I don't have enough rep on this site to post a comment @Edward Dortland: The link is from NancySon and I would also recommend posting the context of the article or the answer in case things get broken and lost:

Maintenance Plans Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

use msdb

DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'

-- set the new server name to the current server name

declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT    id
FROM         sysssispackages
WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix


declare @planid uniqueidentifier
fetch next from PlansToFix into @planid

while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

end
fetch next from PlansToFix into @planid  -- get the next plan

end

close PlansToFix
deallocate PlansToFix


----- This will also handle the packages that have a tag such as 
----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>


  
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername = @@servername
declare @xml as varchar(max)
declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor FOR 
SELECT id
FROM sysssispackages
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

OPEN PlansToFix
declare @planid uniqueidentifier
fetch next from PlansToFix into @planid 
while (@@fetch_status<>-1) -- for each plan 
begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) 
from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
end
fetch next from PlansToFix into @planid -- get the next plan
end
close PlansToFix
deallocate PlansToFix

From: https://msdn.microsoft.com/en-us/library/ms143799(SQL.100).aspx

However I used the following link from serverfault:

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

And to rename:

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = 'package GUID'