On a Team Foundation Server instance hosting its Databases on a SQL 2008 R2 instance, If a copy of SQL 2008 Express edition is installed, the Team Foundation Server 2010 might lose connectivity to its SQL database, taking down the whole Team Foundation Server instance. The Team Foundation Server administration console would also be inaccessible.

When you launch the TFS administration console, you would see the following Event log exception:

Date XX/XX/2012 17:13:04

Log Windows NT (Application)

Source TFS Services

Category (0)

Event 3075

Computer XX.XX.Com

Message

TF53010: The following error has occurred in a Team Foundation component or extension:

Date (UTC): 12/01/2012 16:13:04

Machine: XX

Application Domain: TfsMgmt.exe

Assembly: Microsoft.TeamFoundation.Framework.Server, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727

Service Host: db75bd45-5580-4203-85f6-a930f4b990c7 (TEAM FOUNDATION)

Process Details:

Process Name: TfsMgmt

Process Id: 3348

Thread Id: 10556

Account name: Domain\User

Detailed Message: Error occurred while updating service host db75bd45-5580-4203-85f6-a930f4b990c7 (ApplicationServiceHost).

Exception Message: TF246017: Team Foundation Server could not connect to the database. Verify that the server that is hosting the database is operational, and that network problems are not blocking communication with the server. (type DatabaseRuntimeException)

Exception Stack Trace: at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.TranslateException(Int32 errorNumber, SqlException sqlException, SqlError sqlError)

at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.TranslateException(SqlException sqlException)

at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.MapException(SqlException ex, QueryExecutionState queryState)

at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.HandleException(SqlException ex)

at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior)

at Microsoft.TeamFoundation.Framework.Server.CatalogComponent.QueryCatalogNodes(IEnumerable`1 pathSpecs, IEnumerable`1 resourceTypeFilters, IEnumerable`1 artifactIdFilters, CatalogQueryOptions queryOptions)

at Microsoft.TeamFoundation.Framework.Server.TeamFoundationCatalogService.QueryNodesInternal(TeamFoundationRequestContext requestContext, IEnumerable`1 pathSpecs, IEnumerable`1 resourceTypeFilters, IEnumerable`1 artifactIdFilters, CatalogQueryOptions queryOptions)

at Microsoft.TeamFoundation.Framework.Server.ApplicationServiceHost.EnsureMachineExists()

at Microsoft.TeamFoundation.Framework.Server.ApplicationServiceHost.Start()

at Microsoft.TeamFoundation.Framework.Server.ApplicationServiceHost.Startup(String connectionString, Boolean initialStartup)

Inner Exception Details:

Exception Message: Failed to load Msxmlsql.dll.

Could not find prepared statement with handle 0.

sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

S100K8E01.Tfs_Configuration..prc_QueryCatalogNodes: Database Statement Failure - Error %error="8179";% executing INSERT statement for @pathSpec

The statement has been terminated. (type SqlException)

SQL Exception Class: 16

SQL Exception Number: 6610

SQL Exception Procedure: sp_xml_preparedocument

SQL Exception Line Number: 1

SQL Exception Server: XX

SQL Exception State: 1

SQL Error(s):

SQL Error[1]: System.Data.SqlClient.SqlError: Could not find prepared statement with handle 0.

Class: 16

Number: 8179

Server:XX

Source: .Net SqlClient Data Provider

State: 5

Procedure: prc_QueryCatalogNodes

Line Number: 44

SQL Error[2]: System.Data.SqlClient.SqlError: sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

Class: 16

Number: 6607

Server: XX

Source: .Net SqlClient Data Provider

State: 3

Procedure: sp_xml_removedocument

Line Number: 1

SQL Error[3]: System.Data.SqlClient.SqlError: XX.Tfs_Configuration..prc_QueryCatalogNodes: Database Statement Failure - Error %error="8179";% executing INSERT statement for @pathSpec

Class: 16

Number: 800001

Server: XX

Source: .Net SqlClient Data Provider

State: 1

Procedure: prc_QueryCatalogNodes

Line Number: 64

SQL Error[4]: System.Data.SqlClient.SqlError: The statement has been terminated.

Class: 0

Number: 3621

Server: XX

Source: .Net SqlClient Data Provider

State: 0

Procedure: prc_QueryCatalogNodes

Line Number: 44

Exception Data Dictionary:

HelpLink.ProdName = Microsoft SQL Server

HelpLink.ProdVer = 10.50.1797

HelpLink.EvtSrc = MSSQLServer

HelpLink.EvtID = 6610

HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink

HelpLink.LinkId = 20476

Exception Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior)

Root cause:

The issue happens due to the SQL installation order. When you Install SQL 2008 Express edition after SQL 2008 R2 installation, the "Msxmlsql.dll" file gets over written with the SQL 2008 Edition. This file is responsible of parsing XML and once its over written with the Express version, the issue start with any application.

Solution/Workaround:

The safest approach is to Uninstall the SQL 2008 Express edition.

If that is not an option, then copy the two files (MSXMLSQL.rll and Msmxlsql.ddl) to its hard disk location.

From the SQL 2008 R2 installation media \1033_ENU_LP\x64\Setup\sql_engine_core_shared_loc_msi\PFiles\SqlServr\100\Shared\Res\1033\msxmlsql.rll

To : \Program Files\Microsoft SQL Server\100\Shared\Resources\1033\

From the SQL 2008 R2 installation media \x64\Setup\sql_engine_core_shared_msi\PFiles\SqlServr\100\Shared\msxmlsql.ddl

To : \Program Files\Microsoft SQL Server\100\Shared

Note: you have to stop SQL services while replacing the files.

The downside of this workaround would be, in the future if you perform a repair on SQL 2008 Express edition, the files will get overwritten.

Content developed by: ArunRama

Content reviewed by: Lakhminder Singh