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

Products

• IT Management Suite

Issue/Introduction

You have replicated some software packages from Company A to Company B. Now in company B's SMP Server, you have list of all software packages. But some of them are assigned to all Package Servers, and some just to Specific Sites.

Environment

ITMS 8.x

Resolution

Before attempting the following steps, make sure you have a recent backup of your database.

1. Run the following Query

2.  After running the query,  run Package Refresh task. 

3. verify that the affected packages are now assigned properly

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