Listing 4, Inside the TFS Databases, Part 2

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
		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]',
		b.*
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

Featured

  • 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.

  • Speed Lines Graphic

    New for Blazor: Azure Static Web Apps Support

    With Blazor taking the .NET web development world by storm, one of the first announcements during Microsoft's Ignite 2020 developer/IT event was its new support in Azure Static Web Apps.

  • Entity Framework Core 5 RC1 Is Feature Complete, Ready for Production

    The first release candidate for Entity Framework 5 -- Microsoft's object-database mapper for .NET -- has shipped with a go live license, ready for production.

Upcoming Events