Changing a TeamPulse project’s template

This KB article will describe how to change the project template used by a TeamPulse project.This may be necessary if you are attempting to synchronize with Team Foundation Server and the TFS project’s stories and tasks do not have the same states and state transitions as your TeamPulse project.

For more information about TeamPulse project templates, see the project templates documentation.

Warning! Direct database manipulate ahead.Please backup your TeamPulse database prior to continuing.

Changing a TeamPulse project template involves three main steps:

  1. Updating the template xml for the project
  2. Fixing the audit records
  3. Moving all stories and tasks back to the initial state

Updating the template xml for the project

  1. Open SQL Server Management Studio and connect to the TeamPulse database
  2. Find the ID for your project
    • Method #1: Check the URL in the browser when your project is opened in TeamPulse:
      Project ID in URL
    • Method #2: Run the following SQL query and find your project:

      USE TeamPulse
      SELECT
      * FROM Project

  3. Get a list of all the available project templates by running the following query:

    SELECT * FROM ProjectTemplate

    Project Template Query

  4. Choose one of the templates by clicking on the link in the Template column.

    The template xml should open in a new tab.

    NOTE: If you are changing your project template because you’re trying to sync with TFS, be sure to choose the template that matches the one your TFS project was created with (i.e. Agile, Agile 2012, CMMI, CMMI 2012, EMC Scrum 2010, Scrum, or Scrum 2012).

  5. Change the template of your TeamPulse project.
    1. Copy and paste the template xml from step 4 into the following query:


      UPDATE
       Project
      SET
       Settings = 'template xml goes here'
      WHERE
       ProjectID = <Your Project ID goes here>

    2. Fix any single quotes in the template xml by adding another single quote beside them.
    3. Run the query.
    4. The template for your project should now be changed.

Fix Audit Records for Status field rows

In order for TFS sync to work, some Audit records need to be updated so that the sync engine will ignore invalid state changes that were made when project was using the old template.

Run the following query:

UPDATE Audit
SET
 RevisionStatus = 4
WHERE
 ProjectID = <Your Project ID goes here>
  AND TableName = 'WorkItem'
  AND FieldName = 'Status'
  AND RevisionStatus IN (0,2,3)

 

Reset all TeamPulse work items back to the initial status for the new template

If you don’t reset all the statuses, the stories and tasks will remain in their current status from the old template which may be invalid in the new template. From TeamPulse you will only be able to change items back to their initial state.

To reset the statuses of all stories and tasks, run the following queries:

UPDATE WorkItem
SET
[Status] = 'New initial Status for Story'
WHERE
[ProjectID] = <Your Project ID goes here> AND [Type] = 'Story'

UPDATE WorkItem
SET
[Status] = 'New initial Status for Task'
WHERE
[ProjectID] = <Your Project ID goes here> AND [Type] = 'Task'

UPDATE WorkItem
SET
[Status] = 'New initial Status for Bug'
WHERE
[ProjectID] = <Your Project ID goes here> AND [Type] = 'Bug'

UPDATE WorkItem
SET
[Status] = 'New initial Status for Feedback'
WHERE
[ProjectID] = <Your Project ID goes here> AND [Type] = 'Feedback' 

UPDATE WorkItem
SET
[Status] = 'New initial Status for Issue'
WHERE
[ProjectID] = <Your Project ID goes here> AND [Type] = 'Issue' 

UPDATE WorkItem
SET
[Status] = 'New initial Status for Risk'
WHERE
[ProjectID] = <Your Project ID goes here> AND [Type] = 'Risk'

 

You can find what are the initial status for different projects types from the Project Templates documentation. The initial statuses are from the Unexecuted group.

This completes the process of changing your TeamPulse project's template.  If you changed the template to sync with TFS, you can now perform a synchronization.