Listing 6, Inside the TFS Databases, Part 2


DECLARE @rev int = [A specific revision number];
DECLARE @id int = [A specific work item id];
DECLARE @project varchar(255) = [A specific team project name];

with tempData as
(
--Get the long text values	
select  x.ID,
	y.Name,
	x.Words,
	c.Rev
from	WorkItemLongTexts x
join	Fields y on x.FldID = y.FldID
join	(select  b.Name,
		 a.ID,
		 max(a.Rev) as Rev
	 from	 WorkItemLongTexts a
	 join	 Fields b on a.FldID = b.FldID
	 where	 a.ID = @id
		 and a.Rev <= @rev="" group="" by="" b.name,="" a.id)="" c="" on="" c.name="y.Name" and="" c.rev="x.Rev" )="" --get="" the="" base="" work="" item="" select="" a.id,="" a.[team="" project],="" c.words="" as="" 'description',="" d.words="" as="" '[any="" other="" custom="" fields="" -="" match="" with="" join="" clause]',="" b.*="" from="" xxtree="" a="" join="" (select="" b.*="" from="" workitemswere="" b="" where="" b.id="@id" union="" all="" select="" null="" as="" 'revised="" date',="" a.*="" from="" workitemsare="" a="" where="" a.id="@id)" as="" b="" on="" a.id="b.AreaID" join="" tempdata="" c="" on="" b.id="c.ID" and="" c.name='Description' join="" tempdata="" d="" on="" b.id="d.ID" and="" d.name='[Any other custom fields - 1 per line]' where="" a.[team="" project]="@project" and="" b.id="@id" and="" b.rev="@rev">

About the Author

Jeff Levinson is the Application Lifecycle Management practice lead for Northwest Cadence specializing in process and methodology. He is the co-author of "Pro Visual Studio Team System with Database Professionals" (Apress 2007), the author of "Building Client/Server Applications with VB.NET" (Apress 2003) and has written numerous articles. He is an MCAD, MCSD, MCDBA, MCT and is a Team System MVP. He has a Masters in Software Engineering from Carnegie Mellon University and is a former Solutions Design and Integration Architect for The Boeing Company. You can reach him at Jeff.Levinson@nwcadence.com.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.