Changeset Detail Report

Question

I'm new to TFS, but I need a report that gives all of the changeset details.  Below is is what i have tried thus far. however the [FILE] table is missing xls and rdl type files.

use tfswarehouse
SELECT Distinct
[File].[File], [File].[Parent Path]

FROM [Changeset]
JOIN [Code Churn] on [Changeset].__Id=[Code Churn].Changeset
JOIN [Person] on [Code Churn].[Checked in By]=[Person].__Id
JOIN [File] on [Code Churn].FileName=[File].__Id

WHERE 1=1
AND [File].[Parent Path] Like '$/DEV/R2009-Oct-16/%'

Answer 1

Hello,

Thanks for your post, but I couldn't reproduce this issue. The xls and rdl type files are correctly returned by this query in my machine.

Are you sure if the those files are not checked in recently and they have been been imported to the TFSWarehouse database? TFS warehouse update has a delay based on the schedule settings, by default, it updates every 1 hour.

Can you use the following query to make sure if the files does missing in File table?

SELECT [File].[File], [File].[Parent Path], [File Extension]
FROM [File]
WHERE [File Extension]='.rdl' OR [File Extension]='.xsl'

If it still returns nothing, one option is to rebuild the relational data warehouse. You can follow the instructions about rebuilding relational data warehouse in the link below:
http://blogs.msdn.com/alanh/archive/2008/05/12/some-helpful-administrative-operations.aspx

However rebuilding relational database may cause data loss. For detail, please refer to:
http://sharepoint/sites/msdnforum/Readiness/Important%20SetupWarehouse%20-rebuild%20can%20cause%20data%20loss.aspx

So please backup the database before you do it. Thanks.

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hello,

How's going on this issue? Do you still need help?

Thanks,
Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

I still was unable to locate the xls files in the file table in the warehouse DB.  What I decided to do was to access the TFSVersionControl DB using the following Query.

SELECT
C.ChangeSetId,
V.FullPath,
V.ParentPath,
V.ChildItem,
C.CreationDate,
I.DisplayName,
C.Comment

FROM tbl_Version(nolock) V
INNER JOIN tbl_File (nolock) F ON V.ItemId = F.ItemId
INNER JOIN tbl_Changeset (nolock) C ON V.VersionTo = C.ChangeSetId
INNER JOIN tbl_Identity (nolock) I ON C.CommitterId = I.IdentityId