Hello,

After following the troubleshooting guide and watching the trace files from the warehouse process I get the following error. Apparently I have some bad data in the system on a dimension. I've tried to figure out what's causing this with no luck. No reports are working as a result of this issue. Thank you for your help.

Hi Sacks,

Can you please provide some history on how the system got into this state? For instance, did you recently upgrade to the release version? Did you recently made customizations to work item types? Was the system working before?

Also the system is trying to process Work Item 132, Revision 1 and the exception is saying that we are failing to convert a string to an Int. Can you think of any field in that specific work item that should be an int and it has a value that will fail to be converted to one.

Thanks

Federico

I'd be glad to give more information.

First, this is a release version install (no upgrade).

Second, we did customize the bug work item and added 4 new fields. Later, we decided we didn't need 2 of the fields because they were really already in the system (we just had not noticed them). We then used TFSFieldMapping.exe to delete the extra 2 fields. I'm sure this is the culprit. After spending many hours now analyzing the Team Server tables and structure, I realize this may have not been the best.

Third, yes, the system was working before. We did not, however, notice the problem until days after the changes (so I can't say for sure when in the process it broke). FYI, the core system works without any errors, it's just the WareHouse feature that is failing.

Fourth, I've compared work item 132 Revision 1 to a previous record that has been processed and cannot see any difference in regards to a string value converting to an int. However, I beleive that 2 of the new fields we added started out as int values, then we changed them to strings. In writting this reply, it's triggered my mind that those fields now have strings in them and this could be the problem, the table or structure (which I have not found yet) which tracks what value is in that column may be confused as a result of the change.

A couple of more items of interest. I've noticed the problem is with the "WorkItemsWere" table. I delete work item 132 Revision 1 from that table and it move on to the next workitem in "WorkItemsWere" - work item 134 Revision 1 (it process work item 133 fine because there was no entry in "WorkItemsWere"). I've also analyzed the "Fields" table and it shows the columns we added as being a string type (type = 16). But I'm guessing since "WorkItemsWere" is a history table, that it possibly has them historically defined as int values (somehow, still looking for the table that designates this)? I'm going to set it's values to ints and see if they process just fine.

I'm still looking into it and will post any updates as soon as I can.

--Dave

Hi Dave,

I'm not familiar with the TFSFieldMapping.exe tool so I'll have to talk with some other people here about that. To create the fields I assume that you ran witexport to export the bug template and then ran witimport to import it. To delete a field you can do the same process, you run witexport you delete the fields from the template and then you run witimport but because you are deleting there is an extra step, you need to run witfields delete /s:http://<server>:8080 <field_refname>. Maybe TFSFieldMapping is already doing this.

You say that the fields that stayed were changed from int to string. I'm not aware that we support changing field types on the fly without deleting them and re-adding them with the new type so can you describe the steps you followed to change the field type.

Thanks.

Federico

Federico,

My appologies, I mentioned the wrong tool. I used witfields to delete the fields mentioned above.

However, I've discovered the issue (it's what you mentioned). We had a field that we initially defined as an int in the template. Then we changed our minds and defined it as a string. This is what cause the problem (as you stated above: "I'm not aware that we support changing field types on the fly without deleting them and re-adding them "). Although the fields were changed in TfsWorkItemTracking, I noticed that TFSWareHouse.[Work Item] did not have the types change (still int's). Therein the problem.

So, I'm going to add a new fields as a string, copy all the values over to them in TfsWorkItemTracking and then delete the old field. I'm guessing this will solve the problem.

Lesson learned, don't change the type of a field after creating it. BTW, is this documented somewhere?

--Dave

Hi Dave,

I tried to repro the steps that I imagine you used to change the type of the field and I found the problem. This is a bug in the product that we were not aware of and I logged one so it gets fixed in future versions.

The steps that I think you followed and caused the problem are these:

1) You ran witexport to export the work item type.

2) You added the filed to the work item type XML document as an Integer.

3) You ran witimport.

4) The Warehouse adapters ran and the warehouse schema was updated to include the new field.

At this point the field is in the Warehouse tables as an Integer.

5) You updated the work item type XML deleting the field from it.

6) You ran witimport.

7) You ran witfield to delete the field from the project.

8) You updated the work item type XML re-adding the same field but now as a String.

9) You ran witimport.

10) The Warehouse adapters ran. Here is where the product has a bug. When the Work Item Tracking adapter ran it saw that the field existed in the operational store and it saw that the field also existed in the Warehouse but it didn't compare their types so it didn't force a schema change in the Warehouse. Now when you add new work items and set that field the Warehouse is trying to save the contents of that field into an Integer column and those are the errors you are seeing.

The way to avoid this issue is to run the adapters between steps 7) and 8) in the list I specified above. The product bug was created and this problem should be fixed in future versions but I'll try to get the documentation updated to better explain this scenario and avoid that others find the same problem.

At this point you have two options to fix the problem:

1. You can rebuild the entire warehouse and the schema of the warehouse will be re-created with the new field type. Rebuilding the warehouse is not recommended and it should be used as the last resource.

2. You can do steps 5), 6) and 7), run the warehouse and then do step 8) and 9) again. I guess that the downside of this I that you will lose the values of that field for any revision that was already created in the system. In that sense if you do 1. that won?? happen because when you rebuild the warehouse you are not touching any data in the operational store databases, you are just deleting the Warehouse and having the adapters to re-populated all the data again.

Thanks.

Federico

Federico,

You are right on with the steps. What I did to solve the problem in my case was I create two new fields as strings (I modified the name with a 2 on the end). Then I ran SQL in the database directly to copy the values from the old fields to the new. Then, I set the old fields to NULL. I modified all my template to use the new fields and everything is working just greate. Now, I'm sure this is absolutely not a recommended procedure, but it worked for me in my case.

Thanks for your help on this and hopefully it can get fixed in the next release. For now, I'll just remember to not change the type on a work item field after I've imported it.

--Dave