Breaking (and fixing) SQLDictionary

 Repairing SQLDictionary can be a pain when it gets corrupt for any reason. Let's go over how to break, but also fix, SQLDictionary. First, let's take a look at SQLDictionary for table SalesTable

SELECT *
FROM dbo.SQLDICTIONARY
WHERE TABLEID = 366 --SalesTable
AND FIELDID <> 0 --FieldId of 0 is for table record

As you can see, we have some fields that we all know and love, namely SalesId. That's a fairly important field for AX to keep on working with sales orders. Next, we're going to do something we should never do. We are going to manually delete a record from SQLDictionary.

DELETE FROM dbo.SQLDICTIONARY
WHERE TABLEID = 366 --SalesTable
  AND FIELDID = 1 

Now, if we run our query from earlier, SalesId is gone from SQLDictionary.

As you can see, it is no longer in SQLDictionary but it is still in the database. 

Now, we have to get the SQLDictionary record back. If we do a normal database sync, AX will think because the SQLDictionary record isn't there that the field in the table also isn't there. This has the meta database schema and the database schema out of sync and AX will try to get the database schema to match the meta database schema when it creates the records for the table it is inspecting. However, AX will error our when creating the field SalesId on table SalesTable because it is already there. That's what check/synchronize is for. Go to System Administration > Periodic > Database > SQL Administration.

From there, go to Table Actions then Check/Synchronize

Doing this will kick off what looks like a normal database sync. However, at the confirmation step that sometimes pops up when full tables are going to be deleted and re-created, you get an additional option. This screen will always popup for a check/syncronize.

Now we have the option to "Export Script". This means we can export the SQL AX was going to execute. We will get a nasty looking message when we click "Export script" that is basicaly just saying we are straying into supoorted territory if we get it wrong. We will be asked where we would like to save it as a txt file

 

Open the text file and copy/paste into SSMS. Now that we have the SQL, we have the opportunity to take control and remove unwanted stuff and execute the SQL ourselves to get SQLDictionary back to a known good state without changing the actual structure of the database. Below is a snippet for our example:

ALTER TABLE SALESTABLE ADD SALESID NVARCHAR(20) NOT NULL DEFAULT ''

DELETE FROM "DBO".SQLDICTIONARY WHERE TABLEID = 366

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 0, 0, 'SALESTABLE', 'SALESTABLE', 0, 166, 0, 0, 0, 0, 5641115631)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 1, 1, 'SALESID', 'SALESID', 0, 20, 0, 0, 0, 0, 5641115632)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 2, 1, 'SALESNAME', 'SALESNAME', 0, 60, 0, 0, 0, 0, 5641115633)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 3, 1, 'RESERVATION', 'RESERVATION', 4, 0, 0, 0, 0, 0, 5641115634)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 4, 1, 'CUSTACCOUNT', 'CUSTACCOUNT', 0, 20, 0, 0, 0, 0, 5641115635)

...

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 61448, 1, 'DATAAREAID', 'DATAAREAID', 0, 4, 0, 0, 0, 0, 5641115794)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 61450, 1, 'RECVERSION', 'RECVERSION', 1, 0, 0, 0, 0, 0, 5641115795)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 61454, 1, 'PARTITION', 'PARTITION', 49, 0, 0, 0, 0, 0, 5641115796)

INSERT INTO "DBO".SQLDICTIONARY (TABLEID, FIELDID, ARRAY, NAME, SQLNAME, FIELDTYPE, STRSIZE, SHADOW, RIGHTJUSTIFY, NULLABLE, FLAGS, RECID) VALUES ('366', 65534, 1, 'RECID', 'RECID', 49, 0, 0, 0, 0, 0, 5641115797)