How to pull all Pending Changes (Checked Out Items) from TFS 2005 or 2008 using SSRS and SQL

tbeaty — Thu, 19 Mar 2009 06:19:52 GMT

After reading Tom Fischer's blog entry on How to pull all Pending Changes (Checked Out Items) from TFS 2005 or 2008 I thought I would look into how to accomplish the same objective using SSRS and SQL.

With more research on the TfsVersionControl database and also reviewing a query on retrieving data from a TFS database, I put together this sql statement that retrieves a list of users who have items checked out.

/* This query returns the
    list of currently checked out items */
declare @UtcDiff int,@Date datetime
   ,@Offset int

--set the time difference, date, and number of dates back you want to go
select @UtcDiff=datediff(millisecond,getutcdate(), getdate())
   ,@Date=convert(datetime,convert(varchar(20),getdate(),101))
   ,@Offset=5

select i.displayname as UserID
   ,replace(replace(pc.TargetParentPath+replace(pc.targetChildItem,'\',''),'"','-'),'>','_') as TFSFile
   ,pc.PendingCommand as CommandType
   ,dateadd(millisecond,@UtcDiff,pc.CreationDate) as LocalTime
from dbo.tbl_PendingChange pc
inner join dbo.tbl_Workspace w on w.workspaceid=pc.workspaceid
inner join dbo.tbl_Identity i on w.ownerid=i.identityid
where pc.creationdate<=dateadd(day,-convert(int,@Offset),@Date)
   and w.computer is not null
order by i.displayname,pc.creationdate

Please note the second to last line in the query "and w.computer is not null". The tbl_PendingChange stores all checked out and shelved items. If you remove the "not" and add the tbl_workspace columns "WorkspaceName" and "Comment" (see query below) you can get a list of what items have been shelved and when.

/* This query returns the
    list of current shelf sets */
declare @UtcDiff int,@Date datetime
   ,@Offset int

--set the time difference, date, and number of dates back you want to go
select @UtcDiff=datediff(millisecond,getutcdate(), getdate())
   ,@Date=convert(datetime,convert(varchar(20),getdate(),101))
   ,@Offset=5

select i.displayname as UserID
   ,replace(replace(pc.TargetParentPath+replace(pc.targetChildItem,'\',''),'"','-'),'>','_') as TFSFile
   ,pc.PendingCommand as CommandType
   ,dateadd(millisecond,@UtcDiff,pc.CreationDate) as LocalTime
   ,w.WorkspaceName
   ,w.Comment
from dbo.tbl_PendingChange pc
inner join dbo.tbl_Workspace w on w.workspaceid=pc.workspaceid
inner join dbo.tbl_Identity i on w.ownerid=i.identityid
where pc.creationdate<=dateadd(day,-convert(int,@Offset),@Date)
   and w.computer is null
order by i.displayname,pc.creationdate

Tad Beaty

How to pull all Pending Changes (Checked Out Items) from TFS 2005 or 2008 using SSRS and SQL