If you have run the TFS database repair command line utility you have probably run into this issue. I am able to reproduce the issue simply by rerunning the tool against my TFS databases in SQL.
Once you run this tool - when you open the the team explorer in VSTS you will notice that the work items are no longer available. The reason this happens is because when you run this utility - it drops and recreates all the Stored Procedures and UDF's and other stuff that TFS uses to get to the data. To diagnose this problem I used the SQL Profiler to listen to the queries coming into my TFS server. I came up with one query of particular interest...
*note I removed the sensitive data items from the query - they are the values in angle brackets such as <binaryvaluetwo>.
exec sp_executesql N'set nocount on
declare @rebuildOK as int
declare @PersonName as nvarchar(255)declare @userSid as nvarchar(256)
set @userSid=@P1
exec @rebuildOK=dbo.RebuildCallersViews null,@P2,1,0,default,@P1
if @rebuildOK<>0 return
select @PersonName = DisplayPart from dbo.Constants where SID = @P1
declare @bulkUpdateIdList as nvarchar(4000); set @bulkUpdateIdList='''';declare @missingOrUpdatedIdList as nvarchar(4000);set @missingOrUpdatedIdList='''';
select @PersonName
declare @Collation as sysname exec dbo.sp_executesql N''set @Collation =cast(DATABASEPROPERTYEX(DB_NAME(),''''collation'''') as sysname)'',N''@Collation sysname output'',@Collation=@Collation output select collationproperty(@Collation,''LCID'')
select collationproperty(@Collation,''ComparisonStyle'')
select DbStamp from Dbo.GetDbStamp
declare @accountDisplayMode as int;
exec @accountDisplayMode = dbo.GetAccountDisplayMode
select @accountDisplayMode
declare @RowVer0 as binary(8) set @RowVer0=@P3
declare @RowVer1 as binary(8) set @RowVer1=@P4
declare @RowVer2 as binary(8) set @RowVer2=@P5
declare @RowVer3 as binary(8) set @RowVer3=@P6
declare @RowVer4 as binary(8) set @RowVer4=@P7
declare @RowVer5 as binary(8) set @RowVer5=@P8
declare @RowVer6 as binary(8) set @RowVer6=@P9
declare @RowVer7 as binary(8) set @RowVer7=@P10
declare @RowVer8 as binary(8) set @RowVer8=@P11
declare @RowVer9 as binary(8) set @RowVer9=@P12
exec dbo.GetAdminData 0,@RowVer0,@RowVer1,@RowVer2,@RowVer3,@RowVer4,@RowVer5,@RowVer6,@RowVer7,@RowVer8,@RowVer9
set nocount off',N'@P1 nvarchar(45),@P2 nvarchar(8),@P3 binary(8),@P4 binary(8),@P5 binary(8),@P6 binary(8),@P7 binary(8),@P8 binary(8),@P9 binary(8),@P10 binary(8),@P11 binary(8),@P12 binary(8)',@P1=N'<mymachinesid>',@P2=N'WorkItem',@P3=<binaryvalueone>,@P4=<binaryvaluetwo>,@P5=<binaryvaluethree>,@P6=<binaryvaluefour>,@P7=<binaryvaluefive>,@P8=<binaryvaluesix>,@P9=<binaryvalueseven>,@P10=<binaryvalueeight>,@P11=<binaryvaluenine>,@P12=<binaryvalueten>
What's relevant here is when I ran this query in SSMS while connected to the TFS SQL Server I got this message...
could not find stored procedure dbo.GetAccountDisplayMode
Lucky for me I had a staging environment on a virtual machine that I could go back to and compare - it's not a stored procedure it's actually a UDF called "dbo.GetAccountDisplayMode" which does nothing more than act as a constant value (in other words the function does nothing but return the integer value of 2).
Run this query on your TFSWorkItemTracking database and you should get your work items back...
USE [TfsWorkItemTracking]
GO
/****** Object: UserDefinedFunction [dbo].[GetAccountDisplayMode] Script Date: 02/28/2008 09:58:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[GetAccountDisplayMode]
()
RETURNS int
as
begin
return 2
end
It works for me since my work items are back and all is good again - but this still needs to be fixed in the tool... I submitted this to MSDN but not much has been said about it since. I am tempted to see if the tool itself is written in C# and decompile it in reflector and fix the bug so I have my own little hot fix - but that's just something between you me and the wall ;)
If you want to ask or participate in the forum thread (there's not much there) feel free to visit it here...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2922054&SiteID=1