--Declare vars to hold data as we add the dashes in the proper locations
DECLARE @SavesetID varchar(68)
DECLARE @OriginalTransactionID varchar(31)
DECLARE @strTemp varchar(36)
DECLARE @strTemp2 varchar(36)
DECLARE @NewTransactionID varchar(36)
 
--Enter the SavesetID below: (Note, keep the single quotes around the id '')
SET @SavesetID = '984000000000000~200611030142270000~0~22586790904E4FF5A4930EFB659A245'
SET @OriginalTransactionID = RIGHT(@SavesetID, 31)
 
--Now we have the TransactionID time to add the dashes
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 
 
--Print the results of our conversion to the Messages Tab so we can verify everything looks right
PRINT 'The SavesetID given:                ' + @SavesetID
PRINT 'The piece of the saveset '
PRINT 'that represents the TransactionID:  ' + @OriginalTransactionID
PRINT 'New TransactionID:                  ' + @NewTransactionID
 
--------------------------------------------------------------------------------------------------
--Use the new TransactionID to get the data
 
--This will check that we can find the transaction in the saveset table
--If this is null then something was wrong with our conversion of the TransactionID
--Check the Messages tab to see what our transactionID looks like to determine if
--it is in the proper format
	SELECT	IdTransaction AS 'TransactionID'
	FROM	Saveset
	WHERE	(CONVERT(varchar(36), IdTransaction)) = @NewTransactionID 
 
--The following will show ClipID for Centera if we are NOT using collections
	SELECT	ss.StoreIdentifier AS 'Centera ClipID'
	FROM	SavesetStore ss JOIN Saveset s ON
				ss.SavesetIdentity = s.SavesetIdentity
	WHERE	(CONVERT(varchar(36), s.IdTransaction)) = @NewTransactionID 
 
--The following will show ClipID for Centera if we are using collections
	SELECT	c.RelativeFileName AS 'Centera ClipID using Collections'
	FROM	Collection c JOIN Saveset s ON
				c.CollectionIdentity = s.CollectionIdentity
	WHERE	(CONVERT(varchar(36), s.IdTransaction)) = @NewTransactionID