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 this blog post.
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
1. Open SQL Server Management Studio and connect to the TeamPulse database
2. Find the ID for your project
a. Method #1: Check the URL in the browser when your project is opened in TeamPulse:
b. Method #2: Run the following SQL query and find your project:
Select * from Project use TeamPulse
3. Get a list of all the available project templates by running the following query:
SELECT * FROM ProjectTemplate
4. Choose one of the templates by clicking on the link in the Template column.
a. The template xml should open in a new tab.
b. 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.
a. 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>
b. Fix any single quotes in the template xml by adding another single quote beside them.
c. Run the query.
d. The template for your project should now be changed.
6. Fix Audit Records for Status field rows.
a. 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.
b. Run the following query:
UPDATE Audit
SET RevisionStatus = 4
WHERE ProjectID = <Your Project ID goes here>
AND (TableName = 'Story' OR TableName = 'Task')
AND FieldName = 'Status'
AND RevisionStatus IN (0,2,3)
7. Reset all TeamPulse work items back to the initial status for the new template.
a. 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.
b. To reset the statuses of all stories and tasks, run the following queries:
UPDATE Story
SET Status = 'New initial Status for stories'
WHERE ProjectID = <Your Project ID goes here>
UPDATE Task
SET Status = 'New initial Status for tasks'
WHERE ProjectID = <Your Project ID goes here>
-- for Bugs
UPDATE Problem
SET Status = 'New initial Status for tasks'
WHERE ProjectID = <Your Project ID goes here>
and ProblemType = 0
-- for Issues
UPDATE Problem
SET Status = 'New initial Status for tasks'
WHERE ProjectID = <Your Project ID goes here>
and ProblemType = 1
-- for Risks
UPDATE Problem
SET Status = 'New initial Status for tasks'
WHERE ProjectID = <Your Project ID goes here>
and ProblemType = 2
| Initial Statuses | | |
| Template | Story | Task | Bug | Issue/Risk |
| Agile | Active | Active | Active | Active |
| Agile 2012 | New | new | Active | Active |
| CMMI | Proposed | Proposed | Proposed | Proposed |
| CMMI 2012 | Proposed | Proposed | Proposed | Proposed |
| EMC Scrum 2010 | Not Started | Not Started | Active | Open |
| EMC Scrum 2008 | Not Done | Not Done | Not Done | Not Done |
| Scrum | New | To Do | New | Open |
| Scrum 2012 | New | To Do | New | Open |
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 syncrhronization.