Renaming a computer and maintenance plans

Published 21 February 10 01:41 PM | MartinBell

If you rename the computer where an instance of SQL Server resides then you have to make some changes to the each instance by dropping the old server and adding the new one. This is described in http://msdn.microsoft.com/en-us/library/ms143799.aspx the following article. To drop and re-add the server I wrote the following which should work if executed on any instance, without having to be edited.

DECLARE @INSTANCENAME sysname
SET @INSTANCENAME = NULLIF(RIGHT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0 THEN LEN(@@SERVERNAME) - CHARINDEX('\',@@SERVERNAME ) ELSE 0 END ),'') 

IF LEFT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0 THEN CHARINDEX('\',@@SERVERNAME ) -1 ELSE LEN(@@SERVERNAME) END ) <> HOST_NAME()
BEGIN 
                        DECLARE @cmd nvarchar(4000) 
                        SET @cmd = N'EXEC sp_dropserver ''' + @@SERVERNAME + N'''; 
                                                EXEC sp_addserver ''' + RTRIM(HOST_NAME()) + ISNULL(N'\' + @INSTANCENAME,'') + ''', local' 
                        EXEC ( @cmd )
END

As this relies on the HOST_NAME() function it has to be run on the server itself.

But if you look at the end of the above article one of the Community Content entries takes about how to fix maintenance plans when you have changed the hosts name. There are comments about loosing line breaks when copying and pasting the script. If you paste the script into MS Word and then copy/paste it from there you will not have this problem. For SQL 2005 systems look at dbo.sysdtspackages90 and not dbo.sysssispackages. The script has been added my MSFT on the SQL 2005 version of the page with this change. There is a disclaimer also posted “The script below was added by a customer to the SQL Server 2008 version of this topic ( http://msdn.microsoft.com/en-us/library/ms143799.aspx ). As it also applies to SQL Server 2005, it is being added here as well. However, please be advised that this method is not supported by Microsoft; use at your own risk.

Looking at the script the first thing I noticed was casting of the PackageData column.

CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))

Although SSIS packages are in XML format, the sysssispackages and sysdtspackages table store the XML in a image column. You can’t directly convert from image to varchar or XML, so first you have to convert to varbinary. I’ll look at trying to using XML to change connection string in another post.

The second issue is the use of the @xml and @packagedata variables which seem superflous as the replacement can be done on the update statement, although using them may make it clearer what is happening. Therefore you can eliminate these variables and just have the following update statement.

UPDATE    sysdtspackages90
SET packagedata = replace(cast(cast(packagedata as varbinary(max)) as varchar(max)),'server=''' + @oldservername + '''','server=''' + @newservername +'''')
WHERE (id= @planid)  -- update the plan

The next thing I would question is - Why is a cursor being used? The only real reason is to output the message about what plan has been updated. This can be done in using and OUTPUT clause:

UPDATE    sysdtspackages90
SET packagedata = REPLACE(CAST(CAST(packagedata AS varbinary(max)) AS varchar(max)),'server=''' + @oldservername + '''','server=''' + @newservername +'''')
OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @oldservername + ' to ' + @newservername 
WHERE CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%'

The result of this rationalisation is that the second cursor loop in the script can also be reduced to a single update statement:

UPDATE    sysdtspackages90
SET packagedata = REPLACE(CAST(CAST(packagedata AS varbinary(max)) AS varchar(max)),'Data Source=''' + @oldservername + '''','Data Source=''' + @newservername +'''')
OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @oldservername + ' to ' + @newservername
WHERE CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=''' + @oldservername + '%'

It is also possible to change all the necessary packages in a single statement, but with all the CAST statements I think it is getting a bit messy. So to tidy things up you can use a CTE:


WITH
PackageCTE(Id, PackageDataString)
AS
(
                        SELECT id, CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))
                        FROM sysdtspackages90
)
UPDATE P
SET PackageData = REPLACE(REPLACE(C.PackageDataString,'Data Source=''' + @oldservername + '''','Data Source=''' + @newservername +''''),'server=''' + @oldservername + '''','server=''' + @newservername +'''')
OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @oldservername + ' to ' + @newservername
FROM sysdtspackages90  P
JOIN PackageCTE C ON C.id = P.id
WHERE C.PackageDataString LIKE '%server=''' + @oldservername +'''%'
OR C.PackageDataString LIKE '%Data Source''' + @oldservername +'''%'
GO

It seems logical to combine my original script and updating the packages which you can fine here. Of course the caveat posted with the original script still applies. Once you have run the script you will need to restart the SQL Server instance.

Filed under: ,

Comments

# Dew Drop February 21-22, 2010 | Alvin Ashcraft's Morning Dew said on February 22, 2010 02:00 AM:

Pingback from  Dew Drop February 21-22, 2010 | Alvin Ashcraft&#039;s Morning Dew

This Blog

SQL Blogs

Syndication