Renamed a SQL Server, however maintenance plans still have old name
The Microsoft link given above does not address the "Local server connection" server names in maintenance plans. These queries let you check the maintenance plan connection strings. All use the msdb database.
(SQL 2008, also SQL 2005 if sysssispackages is changed to sysdtspackages90)
SELECT x.*,
LocalServerConnectionString = cm.n.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ConnectionString"])[1]', '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:ConnectionManager[DTS:Property[@DTS:Name="ObjectName"]="Local server connection"]') cm(n)
(SQL 2012)
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)
If you examine the packageXML and determine it is safe to replace every instance of OldServerName with NewServerName, this would do it:
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'
If necessary you can make the changes manually. You could also destroy the package if you're not careful.
DECLARE @packageXML XML;
SET @packageXML = 'Copy and paste the packageXML data, then edit it manually';
UPDATE dbo.sysssispackages SET packagedata = CAST(@packageXML AS VARBINARY(MAX))
WHERE id = 'package GUID'
How did the colleague rename the server? Did they follow this procedure:
http://msdn.microsoft.com/en-us/library/ms143799.aspx
If not, they need to, The procedure should be the same regardless of SQL version.