TFS 2010 DefaultCollection offline after SP1 applied successfully

Question

I've installed SP1 on a 2010 TFS installation and it seemed to finish successful, but the DefaultCollection now shows that it is offline.  When I try and start the collection, it says that the "Host is in the process of being serviced", but when I view the Status tab, the "Servicing Collection" states that it has failed.  Viewing the logs shows the following error:

[Error] TFSVersionControl_UpgradeTov3SP1.sql Line 4461 Error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.func_PreparePattern", or the name is ambiguous.
[Informational] Microsoft.TeamFoundation.Framework.Server.CollectionServicingException: TFSVersionControl_UpgradeTov3SP1.sql Line 4461 Error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.func_PreparePattern", or the name is ambiguous. ---> System.Data.SqlClient.SqlException: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.func_PreparePattern", or the name is ambiguous.
[Informational] Clearing dictionary, removing all items.
The servicing operation failed.

Running "tfsconfig diagnose /scope:updates" produces the following error, and reapplying the updates produces the same error above:

TF255491: The following database is not at the same service level at the application tier: Collection database for collection DefaultCollection. The database can be updated on this application tier by running the command "TfsConfig updates /reapply".
Node Warning (DIAGNOSESERVICINGONCOLLECTIONS): DiagnoseServicingOnCollections

I tried to uninstall the SP1, but that failed also.  Does anyone know how to get past this error so the SP1 will install correctly?

Answer 1

Hi rleblanc,

Have you tried to run the TfsConfig updates /reapply command to reapply SP1? And you can also consider temporarily kill all SQL services and then re-run TFSJobAgent. And I think the TFS2010 SP1 installation trouble-shooting guide can be helpful to you: http://support.microsoft.com/kb/2516423

And these two cases are related to your issue:
http://social.msdn.microsoft.com/Forums/en-US/tfssetup/thread/9799303a-6ada-4512-8018-025700f17935
http://social.msdn.microsoft.com/Forums/en-US/tfssetup/thread/ee0120d6-8df7-4be9-8d5d-fe39d446d337

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Tried the trouble-shooting guide before I posted my question and nothing there helped and neither one of the cases offered any solutions.

I think this is the problem, which no one else seems to have run into as I can't find any reference to this error anywhere.

[Error] TFSVersionControl_UpgradeTov3SP1.sql Line 4461 Error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.func_PreparePattern", or the name is ambiguous.

In addition:

In researching the func_PreparePattern in the SQL Server Management Studio, I found it inside the "Tfs_DefaultCollection" database under the user "tfsservice".  Further to this I noticed that the "tfsservice" account on the "Tfs_DefaultCollection" database has the Default schema set to "tfsservice".  Should this really be "dbo", which would result in the function being created in the correct schema? If so, is it safe to just change it or is there a utility that does this change?

This is a TFS installation that was originally started out in 2005 and has been upgraded to 2008 and then to 2010.

Answer 3

Hi rleblanc,

I am sorry that I do not encountered the same issue as yours, so could you please provide me the complete log you got?

In addition, with which user account you install TFS2010 SP1? Please make sure that user account is in the administrator group on your machine, and also make sure that user account has a server level login in the SQL Server.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi,

the default schema for tfsservice account must be dbo. How many objects where created in tfsservice schema?

Thanks,

--Vladimir