TFS Austin

Microsoft Test Manager–Test Cases Not in a Test Plan

2012-05-16T08:21:13-05:00:00

Visual Studio and Team Foundation Server (TFS) 2010 provided support for system testing. Test cases were added as a work item type. But test plans and suites are not work items and this greatly limits the visibility into the management of test artifacts. For example, there is no easy way out-of-the-box to find test cases that don’t belong to a test plan.

Here’s the SQL that creates a list of test cases providing the test case:

ID
Title
State
Assigned To
Area Path
Iteration Path
Plan and Suite Path

You can use the output to identify what test cases are in what plan and suite; and more to the point, which test cases are not in a plan.

Using the Amrein SQL Viewer Web Part you can easily throw this up as a web part on the project portal site where users can globally access, sort and filter the data.

SELECT tco.System_Id as ID
,tco.System_Title as Title
,tco.System_State as State
,ISNULL(p.Name,'null') as Assigned
,tco.AreaPath as Area
,tco.IterationPath as Iteration
,ISNULL(s.SuitePath,'null') as 'Plan'
FROM Tfs_01Warehouse.dbo.vDimTestCaseOverlay tco inner join Tfs_01Warehouse.dbo.DimPerson p
ON tco.System_AssignedTo__PersonSK=p.PersonSK left outer join Tfs_{collection DB}.dbo.tbl_SuiteEntry se ON tco.System_Id=se.TestCaseId left outer join Tfs_01Warehouse.dbo.vDimTestSuiteOverlay s
ON se.SuiteId=s.TestSuiteID
WHERE tco.TeamProjectSK={#} /* team project */
ORDER BY tco.System_Id DESC

Set Lab Management (LM) Permissions for Team Projects Created before LM was Configured

2011-10-07T14:38:00-05:00:00

What a long title! Anyway, I didn't add LM until sometime after I had setup my Team Foundation Server 2010 instance. There are 2 important realities to face when you do this:

The LM permissions in the process template are not applied because LM was configured when the team project was created
There's no UI to set LM permissions (arrrgh)

All you have are the permissions established by the accounts used to setup LM (i.e. TfsSetup, etc...).

Here are the commands I developed to grant permissions to existing team projects after you enable LM for them.

Lookup TfsLabConfig in the MSDN library to get command line details.

Grant permissions for a team project group to have environment read, start, pause and stop permissions:

1. Grant read access for all lab management objects in the collection to all team project members:

TfsLabConfig permissions /collection:{your collection url}

/group:"[{team project namel}]\Team Members" /allow:Read    

2. Grant start, pause and stop permissions to project administrators, team leaders and contributors

TfsLabConfig permissions /collection:{your collection url}

/group:"[{team project namel}]\{project group name}" /allow:Start,Pause,Stop     
 
TfsLabConfig permissions /collection:{your collection url}

/group:"[{collection name}]\{collection group name}" /allow:Start,Pause,Stop

NOTE: you can display permissions simply by omitting the /allow parameter

syntax highlighted by Code2HTML, v. 0.9.1

Team Foundation Server 2010 Real-Time Burndown Charts

2011-09-30T14:36:00-05:00:00

The reality is that project managers need work status in real-time. The TFS Excel reports and web parts are great, but because they are sourced from the TFS SSAS cube the information can be up to 2 hours old.

The User Story goes like this...

"As a project manager, team lead or team member I want to see what work is currently active and what the burndown is by team and by person is for that work so I know when work delays or time reporting failures are taking place. I need to know this 30 seconds before the stand-up meeting every day and the information must be accurate in real time."

Here's how I created a burndown chart web part that's:

Real time
Is from a specific/fixed start to end date
Can be by team or by individual
Skips weekend days

First I developed T-SQL to give me:

A query listing of sprints that have active work
A query listing of team members that have active work
A stored procedure that list burndown chart data as of start and end dates

Then I used the Amrein SQL Query Viewer Web Part (enterprise license for $200) to run the listing queries and display them on a TFS project portal page. As part of the query I assimilated the URL that calls the SharePoint 2010 Query String (URL) Filter web part, which I connected to the Amrein Google Chart Web Part (free). This used the chart data query to display the burndown chart from a common web part page. I also used the same query list for the chart data using the Query Viewer Web Part.
Here's the reports...

Web part page listing the Active Work. Each Team Project needs it's own page and the queries vary by area name.

A burndown chart is displayed in a new browser window by clicking on the "burndown" link.

Here's the code. I developed the T-SQL from SQL Server Management Studio.

Sprint Active Work Report

 
SELECT  task.IterationName as Sprint 
  ,CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101) as Start 
  ,CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101) as [End] 
  ,SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) as Remaining 
  ,SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) as Completed 
  ,SUM(task.Microsoft_VSTS_Scheduling_OriginalEstimate) as Estimate 
  ,'http://[ your TFS SP server ]/sites/tfs/XXXX/SiteAssets/BurndownReport.aspx?start=' 
   + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101))  
   + '&end=' + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101))  
   + '&iName=' + task.IterationName + '&aName=XXXUI-v1&assigned=' as Burndown 
  FROM Tfs_01Warehouse.dbo.CurrentWorkItemView  task, Tfs_01Warehouse.dbo.CurrentWorkItemView sprint 
  WHERE task.ProjectPath LIKE '\XXXX%'  
  AND task.System_WorkItemType = 'Task'  
  AND task.AreaName = 'XXXUI-v1'  
  AND task.System_AssignedTo > ' '  
  AND task.System_State <> 'Closed' 
  AND sprint.System_WorkItemType = 'Sprint' 
  AND sprint.IterationPath = task.IterationPath 
  GROUP BY task.IterationName, sprint.Microsoft_VSTS_Scheduling_StartDate,

sprint.Microsoft_VSTS_Scheduling_FinishDate 
  HAVING (SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) > 0 
    OR SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) >0) 
  ORDER BY task.IterationName DESC

Team Member Active Work Report

SELECT  task.System_AssignedTo as Assigned 
  ,SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) as Remaining 
  ,SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) as Completed 
  ,SUM(task.Microsoft_VSTS_Scheduling_OriginalEstimate) as Estimate 
  ,task.IterationName as Sprint 
  ,'http://[ your TFS SP server ]/sites/tfs/XXXX/SiteAssets/BurndownReport.aspx?start=' 
   + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_StartDate, 101))  
   + '&end=' + (CONVERT(nvarchar(10),sprint.Microsoft_VSTS_Scheduling_FinishDate, 101))  
   + '&iName=' + task.IterationName + '&aName=XXXUI-v1&assigned=' + task.System_AssignedTo as Burndown 
  FROM Tfs_01Warehouse.dbo.CurrentWorkItemView  task, Tfs_01Warehouse.dbo.CurrentWorkItemView sprint 
  WHERE task.ProjectPath LIKE '\XXXX%'  
  AND task.System_WorkItemType = 'Task'  
  AND task.AreaName = 'XXXUI-v1'  
  AND task.System_AssignedTo > ' ' 
  AND task.System_State <> 'Closed' 
  AND sprint.System_WorkItemType = 'Sprint' 
  AND sprint.IterationPath = task.IterationPath 
  GROUP BY task.System_AssignedTo, task.IterationName,

sprint.Microsoft_VSTS_Scheduling_StartDate, sprint.Microsoft_VSTS_Scheduling_FinishDate 
  HAVING (SUM(task.Microsoft_VSTS_Scheduling_RemainingWork) > 0 
    OR SUM(task.Microsoft_VSTS_Scheduling_CompletedWork) >0) 
  ORDER BY task.IterationName DESC, task.System_AssignedTo

Burndown Stored Procedure

SP Call to SharePoint 2010 Query String (URL) Filter web part
-----------------------------------------------------------------------
dbo.WorkItemHistoryView_Burndown;start={start};end={end};iName={iName};aName={aName};assigned={assigned}
dbo.WorkItemHistoryView_BurndownData;start={start};end={end};iName={iName};aName={aName};assigned={assigned}
  
SP Code
----------
USE [Tfs_01Warehouse]
GO
/****** Object:  StoredProcedure [dbo].[WorkItemHistoryView_Burndown]

Script Date: 09/28/2011 09:49:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =

OBJECT_ID(N'[dbo].[WorkItemHistoryView_Burndown]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WorkItemHistoryView_Burndown]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Bob Hardister
-- Create date: 9/27/2011
-- Description: Produces a burndown table
-- =============================================
CREATE PROCEDURE dbo.WorkItemHistoryView_Burndown 
 @i   int    = 0, 
 @Start  date   = '9/16/2011',
 @End  date   = '10/6/2011',
 @dayRange int    = 0,
 @iName  nvarchar(256) = 'Sprint 03',
 @aName  nvarchar(256) = 'XXXUI-v1',
 @assigned nvarchar(256) = '',
 @currentDay date   = '01/01/0001'
AS
BEGIN
 DECLARE @Burndown TABLE ([Date] date, Remaining float, Completed float)
 SET @dayRange = DATEDIFF(DAY, @Start, @End)
    WHILE (@i <= @dayRange) 
 BEGIN
  SET NOCOUNT ON
  IF (DATENAME(WEEKDAY, @Start) <> 'Saturday' AND DATENAME(WEEKDAY, @Start) <> 'Sunday')
   BEGIN
    INSERT INTO @Burndown ([Date], Remaining, Completed) 
    SELECT @Start as Dte
     ,SUM(Microsoft_VSTS_Scheduling_RemainingWork) as Rem 
     ,SUM(Microsoft_VSTS_Scheduling_CompletedWork) as Act 
     FROM Tfs_01Warehouse.dbo.WorkItemHistoryView 
     WHERE ProjectPath LIKE '\XXXX%' 
     AND System_WorkItemType = 'Task' 
     AND (DateSK <= @Start AND DateSK >= @currentDay)
     AND IterationName = @iName 
     AND AreaName = @aName 
     AND System_AssignedTo LIKE @assigned + '%' 
   END 
  SET @Start = DATEADD(day, 1, @Start) 
  SET @i = @i + 1 
  IF @Start >= GETDATE() SET @currentDay = @End 
 END 
 
SELECT * FROM @Burndown 
END
GO
GRANT Execute ON dbo.WorkItemHistoryView_Burndown TO TfsReports

syntax highlighted by Code2HTML, v. 0.9.1

TFS 2010 Web Part for Data Warehouse Views

2011-09-09T14:33:00-05:00:00

Team Foundation Server 2010 includes support for Data Warehouse and Analysis Cube Views.

The company, Amrein Engineering, sells a web part that uses SQL statements to source data.

You can implement this web part very easily to do some very nice reporting of TFS data.

For example, you cannot use "time" as a query criteria in Team Explorer. You can only use the date. That is, you can ask for all work items changed by Joe Black on 9/11/2011. But you cannot ask for changes between 3 and 4 PM on that date. However, you can qualify SQL queries by date and time.

Using the web part from Amrein I was able to:

Develop a SQL query for work items changed between two timestamps
Implement the query in the web part to display on the TFS team portal
Add filters and formating to the web part

I was able to display the work item ID as a hyperlink that brought up the work item in the browser or in the Team Web Access editor.

Here's a couple examples of SQL queries against the TFS_Warehouse DB, dbo.WorkItemHistoryView view. Note: using ASOF queries with this view requires the use of the "System_RevisedDate > [ASOF Date]" and the "RecordCount > 0" conditions because of compensating records. "Whenever a work item is updated, a pair of records is added to the warehouse. The first record negates the previous record. This makes querying faster." See book 'Professional Team Foundation Server 2010'

/****** WORK ITEMS CHANGES IN THE PAST 24 HOURS   ******/
SELECT	 [System_ChangedDate] as Changed
		,[System_ChangedBy] as ChangedBy
		,[System_WorkItemType] as WIT		
		,[Microsoft_VSTS_Common_StackRank] as Seq
		,[System_Id] as ID
		,[System_Title] as Title
		,[System_State] as Ste
		,[System_Reason] as Reason
		,[System_AssignedTo] as Assigned		
		,[Microsoft_VSTS_Scheduling_StoryPoints] as Points
		,[Microsoft_VSTS_Build_FoundIn] as FoundIn
		,[Microsoft_VSTS_Build_IntegrationBuild] as FixedIn
		,[Microsoft_VSTS_Common_Priority] as Pri
		,[Microsoft_VSTS_Common_Severity] as Sev
		,[Microsoft_VSTS_Scheduling_OriginalEstimate] as Est
		,[Microsoft_VSTS_Scheduling_RemainingWork] as Rem
		,[Microsoft_VSTS_Scheduling_CompletedWork] as Act
		,[IterationPath] as iPath
		,[AreaPath] as aPath
  FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
  WHERE [AreaPath] LIKE '\Cw%'
  AND	([System_ChangedDate] BETWEEN DATEADD([DAY], -1, GETDATE()) AND GETDATE())
  AND	RecordCount > 0
  AND	[System_ChangedBy] <> 'Build Admin'
  ORDER BY 	[System_ChangedDate] DESC

/****** WORK ITEMS CHANGED HISTORY  ******/
SELECT System_Id as ID
		,max(System_WorkItemType) as WIT
		,max(System_Title) as Title
		,max(System_ChangedBy) as ChangedBy
		,max(System_ChangedDate) as Changed		
		,max(AreaPath) as aPath
  FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
  WHERE [AreaPath] LIKE '\Cw%'
  AND	[System_ChangedBy] = 'Joe Black' 
  AND  ([System_ChangedDate] BETWEEN '9/1/2011 15:00' AND '9/1/2011 16:00')
  AND	[System_RevisedDate] >= '9/1/2011 16:00'
  AND	RecordCount > 0
  GROUP BY [System_Id]
  ORDER BY [System_Id]

syntax highlighted by Code2HTML, v. 0.9.1

Now using the following URL syntax I was able to format the ID column in the web part as a link to the work item:

Display work item in browser: http://[app server name]:[port number]/tfs/[collection name]/WorkItemTracking/Workitem.aspx?artifactMoniker=[work item id number]
Display work item in Team Web Access editor: http://[app server name]:[port number]/tfs/[collection name/web/wi.aspx?id=[work item id number]

Publish TFS 2010 Last Work Item History Comment to Excel

2011-09-06T14:31:00-05:00:00

Team Foundation Server 2010 does not support publishing history data to Excel. However, there is an easy work around to this problem.

However, this is limited to the last recorded History comment for a work item. This is what displays in the query return set when the History column is included.

Create a query with History as a column
Run the query
Click the pulldown on the "Open in Microsoft Office" button
Select "Send Query to Microsoft Outlook"

An email pops up with the data in a table that includes (unlike Excel) the last history comment for each work item. Simply copy the table to Excel and format.

It's not a comprehesive record, but it's very nice as a CCB meeting record of changes just made.

You can also use OneNote for this.

Microsoft Test Manager (MTM) fails to connect when executing test run

2011-08-24T14:30:00-05:00:00

If you are logged on to the local machine (not the domain) you will be prompted to enter network credentials when you open MTM to connect to TFS. However, if you receive a connection error when you attempt to run a test case, you may want to try the following work around.

Go to the Test Plan
Select properties
Select you Test settings under Manual Runs
Uncheck the ASP.NET Client Proxy collection
Save and use that Test Settings Configuration to run a test case

TFS 2010 Agile Iteration Burndown Chart Error - Could not connect to server...: ensure SQL Server Analysis Services is running

2011-08-08T14:30:00-05:00:00

It seems there are many issues that can cause this error. But in my situation it turned out to be a permissions issue.

It appears that other TFS 2010 reports use the service account (i.e. TFSReports) to access SSAS. However, it seems that the burndown chart in the TFS MSF v5.0 Agile Iteration Backlog Excel file passes the current user credentials to SSAS to get chart data. As only the TFSReports account had access, the burndown chart request was denied resultign in the error noted above.

To fix this I:

Opened SQL Svr Mgmt Studio on the data tier
Connected to Analysis Services
Expaned the TFS Analysis DB
Expanded Roles
Right-clicked on TfsWarehouseDataReader and selected Properties
Clicked on Membership
Add the group (or users) needed

After that I no longer got the error and was able to pull data into the burndaown chart.

Microsoft Visual Studio ALM Rangers Sabbatical for Coded UI Word Plug-in

2010-07-29T14:28:00-05:00:00

Earlier this Summer I had the privilege of working for 16 days onsite at the Microsoft offices in Redmond and Vancouver. The assignment, a "Rangers Sabbatical," is part of the Microsoft Visual Studio ALM Rangers program. It provided me the opportunity to develop a Microsoft Word "Technology Provider" or plug-in for the Visual Studio 2010 ALM Coded UI (CUI) feature. Being onsite I was able to work closely with Microsoft Ranger and Program staff.

The Sabbatical was a great success both professionally and personally. I was the developer on the team. We had a prototype of a Excel plug-in for CUI. I used that as a starting point for the creation of a Word plug-in. The approach involved using the CUI extension classes , a Visual Studio Word 2010 (VSTO) Add-in project and the Microsoft 2010 Word API to enable document specific UI recording, playback and assertions to be developed using the Coded UI Test Builder. I had done some preliminary investigations before going onsite, but the level of effort to actually make this work was still uncertain.

This made for early mornings and late evenings. However, the hard work was quite enjoyable. My accommodations in Redmond were less than 5 minutes’ walk from the office. Bijan Javidi and Chuck Sterling were extremely gracious hosts. They provided me a pleasant work environment and introduced me to many of the Visual Studio program team members. Chuck included me in a test drive of Dev11, and had me over for dinner where I got to meet and talk with one of my all time TFS hero's, Grant Holiday.

As it turned out, the planning, support and hard work paid off. With the help of two key team members, Andrew Whitechapel, VSTO guru, and Mathew Aniyan, the CUI program manager, I was able to get the plug-in working. The plug-in was put to immediate use to create CUI tests for the Rangers Word4TFS product. I have been able to take the knowledge and skills gained during the Sabbatical back to my employer for use and for developing additional plug-ins as needed.

Word CUI plugin document record, playback and selection properties

I wrapped up the Sabbatical by developing training material for the plug-in and leading a training session at the Microsoft Vancouver offices. While in Vancouver I was treated to the special care and hospitality of Willy-Peter Schaub, the Rangers Solution Architect. Willy set me up in the Microsoft offices in Vancouver and showed me around a bit. I could not been better welcomed and treated as a guest.

The Rangers Sabbatical program is a great opportunity to deepen skills and become better equipped as a Ranger and Visual Studio ALM professional. I want to express my sincerest thanks to Bijan, Willy and Chuck for giving me this opportunity and for making it a great experience for me.

What Happens to TFS when an Active Directory account gets deleted?

2011-06-22T14:25:00-05:00:00

There are at least three scenarios that deserve mention:

1. Existing data
It doesn't cause an issue. TFS caches the name->SID mapping in the database. If the accounts go away, existing work items/source control history aren't changed.

2. Checked out files
Bottom-line: we need to ensure that there are no files checked out for an account that is to be deleted. You could run into trouble if files are checked out under an account that is deleted and a new account with the exact same name (but different SID) is created before an admin forces an "undo checkout" on those files. What you got was files that were permanently checked out.

3. Migration to TFS
When the TFS Integration Platform tools are used to migrate source code to TFS and the adaptor cannot find the account ID in the source record, the account running the migration is used as the “default” account. So for example, when migrating the change records made by BGONE from Subversion to TFS, the change records are tagged in TFS with TFSTOOLS because my account was used to run the migration.

SQL Server 2008 R2 Install Failure and Resolution

2011-06-20T14:23:00-05:00:00

I was attempting to install SQL Server 2008 R2 on a sever with Windows Server 2008 R2 SP1. The server had a C:, E: and F: drives for the OS/programs, user DB and TempDB/Logs respectively. With the help of Microsoft support I was able to resolve the problem.

PROBLEM
The first error message was "The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. Error code: 2337"

The second error that popped-up after getting past the first one was "The following error has occurred: Could not find the database engine startup handle."

RESOLUTION
I did a number of things including downloading the media again from TechNet and and copying the setup.rll file from the media location to its installed location. But I believe the effective steps were:

Right-click on setup and select "Run as administrator" to initiate the install
Verify that you do not have corrupt media. In my case the use of Winzip to extract the setup files to a local directory was causing the corruption problem. I used Magic Disc to extract the files that were able to execute the setup without any problem.

DEBUG PROCESS
The steps to debugg the problems were:

SQL Server setup was initially failing with the below error.

: The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. Error code: 2337

Later you have downloaded the setup files again started the installation.
Setup passed the previous error but failed again with the below error.

: The following error has occurred: Could not find the database engine startup handle

Tried to run the services manually but it failed without generating any error log or dump files.
Tried to run the services from the console and it failed with an error to launch the program.
Downloaded and installed DebugDiag and tried to collect the dump for the sqlservr.exe process.
Dump files are not generated from the debugdiag.
Downloaded Dependency walker and found the below when tried to run the services.

: LoadLibraryW("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Resources\1033\sqlevn70.rll") returned NULL. Error: %1 is not a valid Win32 application (193).
LoadLibraryW("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Resources\1033\sqlevn70.rll") called from "SQLSERVR.EXE" at address 0x00000000007CA120.
First chance exception 0xC00000FD (Stack Overflow) occurred in "NTDLL.DLL" at address 0x0000000077853560.
LoadLibraryW("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Resources\1033\sqlevn70.rll") caused an exception.
First chance exception 0xE06D7363 (Microsoft C++ Exception) occurred in "KERNELBASE.DLL" at address 0x000007FEFD86A49D.
First chance exception 0xC0000005 (Access Violation) occurred in "MSVCR80.DLL" at address 0x000000007432A8B5.
Second chance exception 0xC0000005 (Access Violation) occurred in "MSVCR80.DLL" at address 0x000000007432A8B5.
Exited "SQLSERVR.EXE" (process 0xC64) with code -1073741819 (0xC0000005).

Replaced the sqlenv70.dll from a good installation and the services were starting but failing with a different error.
The model database location was a weird location than the default install folder.
It looks like a corrupted media again.
Suggested to download the setup again and extract using Magic ISO.
Install completed successfully after using Magic ISO to extract the setup files.