Listing 5, 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,
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
	 where	 a.ID = @id
		 and a.Rev <= @rev="" group="" by="",="""" c="" on="""y.Name" and="" c.rev="x.Rev" )="" --get="" the="" base="" work="" item="" select="",="" a.[team="" project],="" c.words="" as="" 'description',="" d.words="" as="" '[any="" other="" custom="" fields="" -="" match="" with="" join="" clause]',="" b.*="" from="" xxtree="" a="" join="" workitemswere="" b="" on="""b.AreaID" join="" tempdata="" c="" on="""c.ID" and=""'Description' join="" tempdata="" d="" on="""d.ID" and=""'[Any other custom fields - 1 per line]' where="" a.[team="" project]="@project" and="""@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 [email protected].

comments powered by Disqus


  • ML.NET Improves Object Detection

    Microsoft improved the object detection capabilities of its ML.NET machine learning framework for .NET developers, adding the ability to train custom models with Model Builder in Visual Studio.

  • More Improvements for VS Code's New Python Language Server

    Microsoft announced more improvements for the new Python language server for Visual Studio Code, Pylance, specializing in rich type information.

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

  • Visual Studio 2019 v16.8 Preview Update Adds Codespaces

    To coincide with the Microsoft Ignite 2020 IT pro/developer event, the Visual Studio dev team shipped a new update, Visual Studio 2019 v16.8 Preview 3.1, with the main attraction being support for cloud-hosted Codespaces, now in a limited beta.

Upcoming Events