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.