Pages

Friday, August 8, 2014

AX 2012 TFS: Solving update conflicted FK_ModelElementData_HasModelId_LayerId and XU_Update error with SQL profiler

When using AX 2012 with TFS, sometimes you'll get a cryptic error during a TFS synchronization like this:

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_ModelElementData_HasModelId_LayerId". The conflict occurred in database "Dev5_AX2012_model", table "dbo.Model".
SQL statement: { CALL [Dev5_AX2012_model].[dbo].[XU_Update](?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
Cannot execute a stored procedure.




And that's hardly enough information to solve it.

Most likely cause of error for those who don't want to read the entire article:
You probably have a USR layer (or some other layer) that was mistakenly created by any number of things that you should delete.


How to identify/solve:
First, I have to give credit to this post by Fred Shen that got me started down the right path.  He received the same FK_ModelElementData_HasModelId_LayerId error, but with a different root cause.  I couldn't use his steps verbatim though because my TFS sync would take 30+ minutes and that would generate tons of SQL profiler data that I couldn't easily sift though.

  1. Find the error code (547 for me).  SystemAdministration>Inquiries>Database>SQL statement trace log

  2. Launch SQL Profiler and connect to the SQL server
  3. Create new trace, name it whatever you'd like, set a stop time just in case you leave it running, then click on "Events Selection" tab
  4. Uncheck everything
  5. Check "Show all events" and "Show all columns"
  6. Under "Errors and Warnings" check "Exception"
  7. Under "Stored Procedures" check "SP:Starting"
  8. Verify for both of these rows that the columns "TextData", "DatabaseName", and "Error" (where applicable) are checked
  9. Click column filters and on DatabaseName, put your database name or %DBName% for wildcards
  10. Click on the Error filter and put the error code from step 1 (547 in my case)
  11. Lastly, click on TextData and put in "%XU_Update%" and "%FOREIGN KEY%"
  12. Run it, then execute a TFS sync or whatever it is you do to cause the exception to be thrown in AX.
  13. Stop it after you receive the exception and you should see a bright red "Exception" in the profiler.
  14. Press Ctrl+F, and type "exception" and choose the column "EventClass" to search and it'll help you get right to the bad call
Hopefully this helps somebody identify exactly what is throwing their error!


1 comment: