TF246017: Team Foundation Server could not connect to the database, “Failed to load Msxmlsql.dll".
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 = https://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