I came across an interesting problem recently when trying to remove computer from a pool in HCK (also known as DTM) server. Every time I try to remove the machine, following message appears:
‘MachineId’ still has data associated with ‘Resource’ in the data store ‘MachineId’.
If I look at the stack trace, it seems like the problem is related to a foreign key reference between several tables:
Message: The DELETE statement conflicted with the REFERENCE constraint "FK_LogoTarget_Resource". The conflict occurred in database "DTMJobs", table "dbo.LogoTarget", column 'MachineId'. The statement has been terminated. Source:.Net SqlClient Data Provider Target site: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action]) Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 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, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.DistributedAutomation.Provider.Sql.SqlProvider.CommitToDataStore(DataStoreObject dso) Additional data: Key='HelpLink.ProdName' Value='Microsoft SQL Server' Key='HelpLink.ProdVer' Value='10.50.1600' Key='HelpLink.EvtSrc' Value='MSSQLServer' Key='HelpLink.EvtID' Value='547' Key='HelpLink.BaseHelpUrl' Value='http://go.microsoft.com/fwlink' Key='HelpLink.LinkId' Value='20476'
There is not so much information about this error. In fact, it seems like I am the only one having this problem in internet :). The quickiest way to fix this problem is to perform the following operations:
1. Install .NET 3.5 on the machine where you have HCK installed (step-by-step instructions)
2. Install SQL Server Management Studio 2008 (step-by-step instructions)
3. Using SQL Server Management Studio 2008 open database DTMJobs and locate table dbo.LogoTarget
4. Delete from dbo.LogoTarget the machine with which you expirience the problem
– in my case I have deleted all entries, since I was doing a clean-up of HCK before snapshot
– so I just run the following SQL command:
DELETE FROM dbo.LogoTarget
Now … It is not clear what led to this situation to happen. I might think of a recent meltdown of v-sphere server hosting my HCK installation, but this should not really affect MSSQL that much since it is transcational db …