Hi,
I needed to update a Machine (Dev) from Application version 8 PU21 to PU24. The LCS gave the error on step 19 (from total 35).
lcs GlobalUpdate script for service model: AOSService on machine: Dev
I downloaded the log file, inside folder RunbookExecution-ExecuteParallelRunbook-** I got the log file of all steps. As in my case the error was on step 19 I directly checked the error log on step 19. It can differ in your case. I found multiple table errors in the file which stated:
Table Sync Failed for Table: *TheTableName. Exception:
System.InvalidOperationException: Database execution failed: Violation of PRIMARY KEY constraint 'I_65518FIELD'. Cannot insert duplicate key in object 'dbo.SQLDICTIONARY'. The duplicate key value is (TableId, FieldId, 1, 0).
The error explains there already exist a field in the table with same field Id. Also the error took to explore SqlDictionary Table. The table contains all tables and their fields with their Id. There is another known table which stores such information and that is TableFieldIdTable.
I wrote following query to check if the fields in these table share consistent information:
First Query:
Select T.ID, T.NAME, T.TABLEID, S.FIELDID, S.Name,
(SELECT TOP 1 Q.NAME from SQLDICTIONARY Q WHERE Q.TABLEID = T.TABLEID AND Q.FIELDID = 0) AS TableName
from TABLEFIELDIDTABLE T
JOIN SQLDICTIONARY S ON T.TABLEID = S.TABLEID AND T.NAME = s.NAME
WHERE S.FIELDID <> 0 AND S.FIELDID <> T.ID
and T.TABLEID in (4451,14445,12235)
ORDER BY T.TABLEID
The above query returns all the fields which don't have same field Id on SqlDictionary and TableFieldIdTable. 4451,14445 and 12235 are the table Ids which I was getting error in log file. Yours may be different.
Hence, I needed to update the SQLDictionary table field Ids according with the field Ids in TableFieldIdTable. I wrote following queries:
Second Query:
Select S.FIELDID
from TABLEFIELDIDTABLE T
JOIN SQLDICTIONARY S ON T.TABLEID = S.TABLEID AND T.NAME = s.NAME
WHERE S.FIELDID <> 0 AND S.FIELDID <> T.ID
and T.TABLEID = 12235
and T.ID = 24
In the above query 12235 is table Id I was getting error. 24 is the field Id in TableFieldIdTable which I got from
first query the blog. Second query is just for verification of field to exist. I needed to change the field Id value in SqlDictionary which was 19 then. So wrote following query to update SqlDictionary field Id to 24.
Third Query:
Update SQLDICTIONARY
set SQLDICTIONARY.FIELDID = 24
where SQLDICTIONARY.FIELDID = (
Select S.FIELDID
from TABLEFIELDIDTABLE T
JOIN SQLDICTIONARY S ON T.TABLEID = S.TABLEID AND T.NAME = s.NAME
WHERE S.FIELDID <> 0 AND S.FIELDID <> T.ID
and T.TABLEID = 12235
and T.ID = 24)
and SQLDICTIONARY.TABLEID = 12235
In the above query 12235 is table Id I was getting error. 24 is new field. The second query in blog is used as it is inside the third query (highlighted). I had to repeat the query carefully with the all faulty field Ids (there were 5 in total in my case). It can be different in your case.
Finally, I resumed the PU update process from LCS and it succeeded.
