Geeks With Blogs
Tad Beaty
| Home |
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

 

 

Posted on Thursday, March 19, 2009 1:19 AM TFS , SSRS , SQL | Back to top


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

# re: How to pull all Pending Changes (Checked Out Items) from TFS 2005 or 2008 using SSRS and SQL
Requesting Gravatar...
Thanks,this is exactly what I was missing and never took the time to find it out. Great Post!
Tom
Left by ftom on Mar 19, 2009 2:47 AM

Your comment:
 (will show your gravatar)


Copyright © Tad Beaty | Powered by: GeeksWithBlogs.net