When attachments are added to a Change Request in StarTeam it is extremely difficult to relate the physical files stored in the StarTeam Repository (Changes_Attachments folder) to the attachments and their corresponding Change Requests.

This SQL script (SQL Server only) will analyse the database for Change Requests in the specified Project that have attachments added and will return the following details for each CR found;

• Change Request #
• Project
• View
• Last Modified Time

And for each CR listed above will also return;

• Change Attachment Filename (filename stored in Repository)
• Actual filename (as attached to CR)

Some known caveats / issues:

• The 'HexStrToVarBin' function (included in script) MUST be created first and MUST reside on the database you are querying
• It can only be run for one project at a time
• It cannot tell if the physical attachment file may have already been deleted
• It cannot tell if the CR has already been deleted from StarTeam (but not yet purged), so you may not be able to actually see the CR(s) through the CPC
• It only reports on tip revisions; if an attachment is attached, then removed in a subsequent revision, the physical file may still remain.

-- *****
-- NOTE:
-- ***** FIRST ensure you have created the function dbo.HexStrToVarBin (supplied at end of script)
-- *****
-- CR details returned MAY or MAY NOT have been deleted (in a non-purged View)
-- THIS RETURNS DETAILS OF THE CR and corresponding ATTACHMENTS
-- (i.e. the corresponding actual file name in the Change_Attachments folder
--
-- The primary use of this script is to determine the actual encoded phyisical name of the file(s) stored
-- in the Change_Attachments for CR(s), and the origianl attachment filename.
--

DECLARE @proj VARCHAR(50) = 'PROJECT NAME'

-- ** DO NOT EDIT BELOW THIS LINE **
DECLARE @crnumber INT
DECLARE @view VARCHAR(MAX)
DECLARE @hexstring VARBINARY(MAX)
DECLARE @hexstringactual VARBINARY(MAX)
DECLARE @lastmod DATETIME
DECLARE @string VARCHAR(MAX)
DECLARE @s VARCHAR(8)
DECLARE @hex VARBINARY(MAX)
DECLARE @intID INT

DECLARE crs CURSOR FOR
SELECT
c.ChangeNumber,
v.Name,
dbo.ConvertSTTimestamp(c.ModifiedTime),
c.AttachmentIDs
FROM
syn_Change c, syn_View v, syn_Project p
WHERE
c.ViewID = v.ID AND
v.ProjectID = p.ID AND
p.ID = (SELECT ID FROM syn_Project WHERE Name = @proj) AND
c.ModifiedTime <> 0 AND
c.EndmodifiedTime = 0 AND
c.AttachmentCount > 0 -- CRs with attachments ONLY
ORDER BY
c.ChangeNumber, c.ModifiedTime

OPEN crs
FETCH NEXT FROM crs INTO @crnumber, @view, @lastmod, @hexstringactual
IF (@@ROWCOUNT = 0)
BEGIN
SELECT '*** No CHANGE REQUESTS with ATTACHMENTS found ***'
END

WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @hexstring = CONVERT(VARBINARY(MAX), REVERSE(CONVERT(VARBINARY(MAX),@hexstringactual)))
SET @string = CONVERT(VARCHAR(MAX), @hexstring,1)
SET @string = RIGHT(@string, LEN(@string)-2) -- remove the '0x' part of the hex number

SELECT
@crnumber [CR] ,
@proj [PROJECT],
@view [view] ,
@lastmod [CR LAST MODIFIED]

WHILE LEN(@string) > 0
BEGIN
SET @s = LEFT(@string, 8)
SET @string = RIGHT(@string, LEN(@string) - 8)
-- make sure you have created the following function beforehand (source is below)
SET @hex = dbo.HexStrToVarBin(@s)
SET @intID = CONVERT(INT, @hex)

SELECT
@crnumber [CR] ,
@s [CHANGE_ATTACHMENT FILE NAME] ,
Name [ACTUAL FILE NAME]
FROM
syn_Changes_Attachments WHERE ID = @intid
END
FETCH NEXT FROM crs INTO @crnumber, @view, @lastmod, @hexstringactual
END

DEALLOCATE crs

-- *** SOURCE for HexStrToVarBin function *** --
-- ** copy into a new query window, remove the comment markers and execute BEFORE running script above ** --
----CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(8000))
----RETURNS varbinary(8000)
----AS
----BEGIN
---- DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(8000), @odd BIT, @start bit
---- SET @count = LEN(@hexstr)
---- SET @start = 1
---- SET @b = CAST('' AS varbinary(1))
---- IF SUBSTRING(@hexstr, 1, 2) = '0x'
---- SET @i = 3
---- ELSE
---- SET @i = 1
---- SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)
---- WHILE (@i <= @count)
---- BEGIN
---- IF @start = 1 AND @odd = 1
---- BEGIN
---- SET @hex = '0' SUBSTRING(@hexstr, @i, 1)
---- END
---- ELSE
---- BEGIN
---- SET @hex = SUBSTRING(@hexstr, @i, 2)
---- END
---- SET @b = @b
---- CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]'
---- THEN CAST(SUBSTRING(@hex, 1, 1) AS INT)
---- ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT)
---- END * 16
---- CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]'
---- THEN CAST(SUBSTRING(@hex, 2, 1) AS INT)
---- ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT)
---- END AS binary(1))
---- SET @i = @i (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))
---- IF @start = 1
---- BEGIN
---- SET @start = 0
---- END
---- END
---- RETURN @b
----END
----GO