Change all packages from software library that are set to "Package Servers Individual" to "All Package Servers"

Products

• IT Management Suite

Issue/Introduction

Customer has replicated some software packages from Company A to Company B. Now in company B's NS, we have list of all software packages. But some of them are assigned to all Package Servers, and some just to Specific Sites.

Resolution

• Run the following Query

•  To make sure, please get a back up of your database first. 

• After running the query,  run Package Refresh task. 

declare @packageGuid uniqueidentifier

declare Modify_Package CURSOR FOR

select Guid
from RM_ResourceSoftware_Package
where State like '%<packageServers autoAssignToSites="False" method="individual" />%'
and ProductGuid <> 'B1338338-5575-4A27-9808-23BEC40D79FA'

OPEN Modify_Package
FETCH NEXT FROM Modify_Package Into @packageGuid
WHILE @@FETCH_STATUS = 0
begin
UPDATE RM_ResourceSoftware_Package

SET [State] = replace(cast([State] as nvarchar(max)), 

                '<packageServers autoAssignToSites="False" method="individual" />',
                '<packageServers autoAssignToSites="False" method="all" />'),

                ModifiedDate = getdate()
WHERE Guid = @packageGuid

FETCH NEXT FROM Modify_Package Into @packageGuid
end
CLOSE Modify_Package
DEALLOCATE Modify_Package
 

-- Change all packages from software library that are set to "Package Servers Automatically with Manual Prestaging" to "All Package Servers"

declare Modify_Package CURSOR FOR

select Guid
from RM_ResourceSoftware_Package
where State like '%<packageServers autoAssignToSites="True" method="site">%'
and ProductGuid <> 'B1338338-5575-4A27-9808-23BEC40D79FA'

OPEN Modify_Package
FETCH NEXT FROM Modify_Package Into @packageGuid
WHILE @@FETCH_STATUS = 0
begin
UPDATE RM_ResourceSoftware_Package

SET [State] = replace(cast([State] as nvarchar(max)), 

                '<packageServers autoAssignToSites="True" method="site">',
                '<packageServers autoAssignToSites="False" method="all">'),

                ModifiedDate = getdate()
WHERE Guid = @packageGuid

delete from PackageSiteActivityLog where PackageGuid = @packageGuid
delete from SWDPackageSite where PackageId = @packageGuid

FETCH NEXT FROM Modify_Package Into @packageGuid
end
CLOSE Modify_Package
DEALLOCATE Modify_Package
 

-- Change all packages from software library that are set to "Package Servers Assigned by Site" to "All Package Servers"

declare Modify_Package CURSOR FOR

select Guid
from RM_ResourceSoftware_Package
where State like '%<packageServers autoAssignToSites="False" method="site">%'
and ProductGuid <> 'B1338338-5575-4A27-9808-23BEC40D79FA'

OPEN Modify_Package
FETCH NEXT FROM Modify_Package Into @packageGuid
WHILE @@FETCH_STATUS = 0
begin
UPDATE RM_ResourceSoftware_Package

SET [State] = replace(cast([State] as nvarchar(max)), 

                '<packageServers autoAssignToSites="False" method="site">',
                '<packageServers autoAssignToSites="False" method="all">'),

                ModifiedDate = getdate()
WHERE Guid = @packageGuid

delete from SWDPackageSite where PackageId = @packageGuid

FETCH NEXT FROM Modify_Package Into @packageGuid
end
CLOSE Modify_Package
DEALLOCATE Modify_Package