DECLARE @SavesetID varchar(68) SET @SavesetID = '502000000000000~200701311855520000~0~21FA80B7467D46BD85D3A9790004B0F'
 
--Description: Designed to return the names of the DA customer databases that have a Legal Hold on an individual item by SavesetID
 
--Requires:             
-- Replace the SavesetID in Line 1 (use the single quotes) 
-- Run against the appropriate Vault Store
--Declare vars to hold data as we add the dashes in the proper locations
 
DECLARE @OriginalTransactionID varchar(31)
DECLARE @strTemp varchar(36)
DECLARE @strTemp2 varchar(36)
DECLARE @NewTransactionID varchar(36)
 
--Now we have the TransactionID time to add the dashes
SET @OriginalTransactionID = RIGHT(@SavesetID, 31)
SET @strTemp2 = LEFT(@OriginalTransactionID, 8) + '-'
SET @strTemp = RIGHT(@OriginalTransactionID,(len(@OriginalTransactionID) - 8))
 
SET @strTemp2 = @strTemp2 + LEFT(@strTemp, 4) + '-'
SET @strTemp = RIGHT(@OriginalTransactionID,(len(@OriginalTransactionID) - 12))
SET @strTemp2 = @strTemp2 + LEFT(@strTemp, 4) + '-'
SET @strTemp = RIGHT(@OriginalTransactionID,(len(@OriginalTransactionID) - 16))
SET @strTemp2 = @strTemp2 + LEFT(@strTemp, 4) + '-'
SET @strTemp = RIGHT(@OriginalTransactionID,(len(@OriginalTransactionID) - 20))
SET @strTemp2 = @strTemp2 + LEFT(@strTemp, 11) + '0'
SET @NewTransactionID = @strTemp2 
 
DECLARE @TransID uniqueidentifier SET @TransID = @NewTransactionID
 
--This Script is designed to determine which DA customer databases have a hold on an individual SavesetID 
DECLARE @CustomerDB nvarchar(100)
DECLARE @FullLength int
DECLARE @TrimmedLength int
 
CREATE TABLE #HGtmp (
HUGIdentity nvarchar(100))
DECLARE TBLCursor CURSOR FOR
  SELECT HoldGroupIdentity, SavesetIdentity FROM HoldSaveset
 
OPEN TBLCursor 
  DECLARE @HGIdentity int, @SSID int
FETCH NEXT FROM TBLCursor INTO @HGIdentity, @SSID
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
IF @SSID = 
(SELECT SavesetIdentity FROM Saveset
WHERE idTransaction = @transID)
	BEGIN
		DECLARE TBLCursor1 CURSOR FOR
		  SELECT HoldGroupIdentity, HoldUserGroupID FROM HoldGroup
		OPEN TBLCursor1 
		  DECLARE @HGIdentity1 int, @HUGIdentity nvarchar(100)
 
		FETCH NEXT FROM TBLCursor1 INTO @HGIdentity1, @HUGIdentity
 
		WHILE @@FETCH_STATUS = 0
		BEGIN		
		IF @HGIdentity = @HGIdentity1
			BEGIN
			 SET @FullLength = (SELECT LEN(@HUGIdentity))
             SET @TrimmedLength = (@FullLength - 40)
             SET @CustomerDB = SUBSTRING(@HUGIdentity, 4, @TrimmedLength)
             INSERT INTO #HGtmp (HUGIdentity) VALUES (@CustomerDB)
			END
		FETCH NEXT FROM TBLCursor1 INTO @HGIdentity1, @HUGIdentity
		END
		CLOSE TBLCursor1
		DEALLOCATE TBLCursor1
	END
 
FETCH NEXT FROM TBLCursor INTO @HGIdentity, @SSID
END
 
SELECT HUGIdentity AS 'DA db with Legal Holds' FROM #HGtmp
 
CLOSE TBLCursor
DEALLOCATE TBLCursor
DROP TABLE #HGtmp