search cancel

How To Mass Update Package Locations; via SQL

book

Article ID: 163527

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

We are working to remove Windows Server 2003 from our environment. To that end, we need to move package locations for a server. The plan is to move the root directory, but that would change the UNC path from \\SERVER\SHARE to either a new \\SERVER\SHARE or [DRIVE]:\[PATH]. How do we accomplish this?

Environment

SMP 7.6 HF7

Resolution

Please ensure you have a current backup of the Symantec_CMDB before making any changes to the database!

The following script changes the location that each package points to, which is what the Notification Server looks at during the NS.PackageRefresh scheduled task. Once complete use Windows Task Scheduler to run "NS.PackageRefresh" and "NS.DistributionPointUpdate" to update the package codebase information within the database:

-- Preview

DECLARE @OldLocation varchar(max)

DECLARE @NewLocation varchar(max)

 

SET @OldLocation = '\\OLDSERVER\' -- Can also be \\OLDSERVER\SHARENAME

SET @NewLocation = '\\NEWSERVER\' -- Can also be \\NEWSERVER\SHARENAME

 

select

       [Name]

       , [Guid]

       , [State]

       , [State_Updated] = cast(replace(cast([State] as nvarchar(max)), 'location="' + @OldLocation, 'location="' + @NewLocation) as ntext)

from [RM_ResourceSoftware_Package]

where cast([State] as nvarchar(max)) LIKE '%location="' + @OldLocation + '%'

order by 1

GO

 

--Update

DECLARE @OldLocation varchar(max)

DECLARE @NewLocation varchar(max)

 

SET @OldLocation = '\\OLDSERVER\' -- Can also be \\OLDSERVER\SHARENAME

SET @NewLocation = '\\NEWSERVER\' -- Can also be \\NEWSERVER\SHARENAME

 

update [RM_ResourceSoftware_Package]

set [State] = cast(replace(cast([State] as nvarchar(max)), 'location="' + @OldLocation, 'location="' + @NewLocation) as ntext)

where cast([State] as nvarchar(max)) LIKE '%location="' + @OldLocation + '%'

GO