Tuesday, June 10, 2014

Synchronization error when setting up AX2012 R3

This will be a quick post on an error I had when setting up a fresh AX2012 R3 installation. I was getting a bit ahead of myself and got stuck with an error when synchronizing the database.


And to help search engines find this post, the error in clear text:

Cannot create a record in Inheritance relation (SysInheritanceRelations). MainTableId: 12345.
The record already exists. 

The error is due to the fact that I didn't restart the AOS after compiling the application. Lesson is; don't rush and do it the right way the first time.
Furthermore, if you DO get stuck, try do a quick search on Life Cycle Services for a solution:
https://fix.lcs.dynamics.com/Issue/Results?q=SysInheritanceRelations

Restarted the AOS and continued the installation.

Thursday, June 5, 2014

Delete Company in AX 2009 using SQL

One of the potential tasks when upgrading to a new version of Dynamics AX (like from AX2009 to AX2012) is getting rid of obsolete companies. Microsoft shared a SQL for this a few years back. I enhanced it a little bit and added some additional statements.

Just one important remark - DO NOT RUN THIS AGAINST AX2012!

In the interest of sharing, here it is:

/********************************************************
 REMOVE COMPANYID IN DYNAMICS AX 2009

 USE AT OWN RISK! 

 MAKE SURE YOUR TRANSACTION LOG IS PERMITTED TO GROW
 
 Inspired by: 
 http://blogs.msdn.com/b/emeadaxsupport/archive/2010/12/09/how-to-delete-orphaned-data-remained-from-deleted-company.aspx

 Tommy Skaue http://yetanotherdynamicsaxblog.blogspot.com/
*********************************************************/ 

DECLARE @_TABLENAME NVARCHAR(40)
DECLARE @_COMPANYID NVARCHAR(4)

SET @_COMPANYID = N'TST';  -- COMPANY TO DELETE

DECLARE CURSQLDICTIONARY CURSOR FOR
SELECT A.SQLNAME
 FROM SQLDICTIONARY A
  INNER JOIN SQLDICTIONARY X ON X.TABLEID = A.TABLEID AND X.FIELDID = 61448
   WHERE A.FIELDID = 0
    AND A.FLAGS = 0

OPEN CURSQLDICTIONARY

FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME

WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE @_SQL NVARCHAR(4000)
 SET @_SQL = N'DELETE FROM ' + QUOTENAME(@_TABLENAME) + N' WHERE DATAAREAID = @_DATAAREAID'
 PRINT (CHAR(13) + 'Removing ' + @_COMPANYID + ' from ' + @_TABLENAME + '...')
 EXEC SP_EXECUTESQL @_SQL, N'@_DATAAREAID NVARCHAR(4)', @_DATAAREAID = @_COMPANYID  
 
 FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME
END

PRINT (CHAR(13) + 'Finalizing...')
DELETE FROM DATAAREA WHERE DATAAREA.ID = @_COMPANYID
DELETE FROM COMPANYDOMAINLIST WHERE COMPANYDOMAINLIST.COMPANYID = @_COMPANYID
DELETE FROM VIRTUALDATAAREALIST WHERE VIRTUALDATAAREALIST.ID = @_COMPANYID
PRINT (CHAR(13) + 'Done!')
CLOSE CURSQLDICTIONARY
DEALLOCATE CURSQLDICTIONARY

Use at own risk (of course), and let me know if you find any issues with it.