Listing 4, Inside the TFS Databases, Part 2

with tempData as
--Get the long text values	
select  x.ID,
from	WorkItemLongTexts x
join	Fields y on x.FldID = y.FldID
join	(select  b.Name,
				max(a.Rev) as Rev
		from	WorkItemLongTexts a
		join	Fields b on a.FldID = b.FldID
		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 '[Whatever other custom fields you have]',
from	xxTree a
join	WorkItemsAre 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 = '[whatever other custom field names you have]'
where	a.[Team Project] = @Project

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 [email protected].

comments powered by Disqus


  • Customize Your Own Audio Test Cues in Visual Studio 2019 v16.9 Preview 3

    Yes, developers can be alerted to a failed test with a fart sound.

  • Progress Touts New Third-Party Blazor UI Components

    Third-party dev tool specialist Progress announced an update to its .NET-centric offerings, touting new controls for Blazor, Microsoft's red-hot project for creating web apps with C#.

  • Entity Framework Core 6: What Developers Want

    Microsoft outlined its plan for Entity Framework Core 6, which in November will take its place as the data access component of the landmark .NET 6, a long-term support (LTS) release that will mark Microsoft's transition from the Windows-only .NET Framework to an open source, cross-platform umbrella offering of all things .NET.

  • AWS Open Sources .NET Porting Assistant GUI

    After previously open sourcing components of its Porting Assistant for .NET, Amazon Web Services open sourced the tool's GUI.

Upcoming Events