Rename an AX company on SQL

This is something I will never recommend to anyone, but I had to do it for a client and desperate time called for desperate measure. I did this on AX 2012 R3 and it has worked well.

Make sure to change the name of from and to company in the following. 

Step 1)  (Taken and modified http://www.artofcreation.be/2010/02/24/rename-an-ax-company-on-sql/)

EXEC sp_MSforeachtable 'update ? set DataAreaID = "TOCOmpany" where ?.DataAreaID = "FROMCompany"'

UPDATE DataArea SET ID = 'TOCOmpany' WHERE DataArea.ID = 'FROMCompany'

Step 2) This is the script I manually created to overwrite the company name across all relevant instances including number sequences, Account structures and what not.

  declare @fromCompany VARCHAR(MAX);
  set @fromCompany = 'FROM COMPANY';
    declare @toCompany VARCHAR(MAX);
  set @toCompany = 'TO COMPANY';

EXEC sp_MSforeachtable 'update ? set DataAreaID = @toCompany where ?.DataAreaID = @fromCompany 

  update [AX_Dynamics_DMTesting].[dbo].[BANKACCOUNTTABLE]
set [dbo].[BANKACCOUNTTABLE].[COMPANYPAYMID] = @toCompany 
where [dbo].[BANKACCOUNTTABLE].[COMPANYPAYMID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[ECORESSTORAGEDIMENSIONGROUPITEM]
set [dbo].[ECORESSTORAGEDIMENSIONGROUPITEM].ITEMDATAAREAID = @toCompany 
where [dbo].[ECORESSTORAGEDIMENSIONGROUPITEM].ITEMDATAAREAID = @fromCompany 

update [AX_Dynamics_DMTesting].[dbo].[USERINFO]
set [dbo].[USERINFO].[COMPANY] = @toCompany 
where [dbo].[USERINFO].[COMPANY] = @fromCompany 

update [AX_Dynamics_DMTesting].[dbo].[TAXUNCOMMITTED]
set [dbo].[TAXUNCOMMITTED].[COMPANY] = @toCompany 
where [dbo].[TAXUNCOMMITTED].[COMPANY] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].BATCH
set [dbo].BATCH.COMPANY = @toCompany 
where [dbo].BATCH.COMPANY = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].DATAAREA
set [dbo].dataarea.ID = @toCompany 
where [dbo].DATAAREA.ID = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[DIRPARTYRELATIONSHIP]
set [dbo].[DIRPARTYRELATIONSHIP].[LEGALENTITYDATAAREAID] = @toCompany 
where [dbo].[DIRPARTYRELATIONSHIP].[LEGALENTITYDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTMODELGROUPITEM]
set [dbo].[INVENTMODELGROUPITEM].[MODELGROUPDATAAREAID] = @toCompany 
where [dbo].[INVENTMODELGROUPITEM].[MODELGROUPDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTMODELGROUPITEM]
set [dbo].[INVENTMODELGROUPITEM].[ITEMDATAAREAID] = @toCompany 
where [dbo].[INVENTMODELGROUPITEM].[ITEMDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTTRANSORIGINJOURNALTRANS]
set [dbo].[INVENTTRANSORIGINJOURNALTRANS].[INVENTJOURNALDATAAREAID] = @toCompany 
where [dbo].[INVENTTRANSORIGINJOURNALTRANS].[INVENTJOURNALDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[LEDGERJOURNALTRANS]
set [dbo].[LEDGERJOURNALTRANS].[OFFSETCOMPANY] = @toCompany 
where [dbo].[LEDGERJOURNALTRANS].[OFFSETCOMPANY] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[EVENTINBOX]
set [dbo].[EVENTINBOX].[COMPANYID] = @toCompany 
where [dbo].[EVENTINBOX].[COMPANYID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[INVENTDIMIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[INVENTDIMIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].ITEMIDDATAAREA = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].ITEMIDDATAAREA = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[PROJIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[PROJIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[PROJTAXGROUPIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[PROJTAXGROUPIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[PROJTRANSIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[PROJTRANSIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[TAXITEMGROUPDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[TAXITEMGROUPDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[VENDACCOUNTDATAAREA] = @toCompany
where [dbo].[PURCHREQLINEHISTORY].[VENDACCOUNTDATAAREA] = @fromCompany


  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQTABLE]
set [dbo].[PURCHREQTABLE].[PROJIDDATAAREA] = @toCompany
where [dbo].[PURCHREQTABLE].[PROJIDDATAAREA] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQTABLEHISTORY]
set [dbo].[PURCHREQTABLEHISTORY].[PROJIDDATAAREA] = @toCompany
where [dbo].[PURCHREQTABLEHISTORY].[PROJIDDATAAREA] = @fromCompany

 update  [AX_Dynamics_DMTesting].[dbo].[SUBLEDGERJOURNALENTRY]
set [dbo].[SUBLEDGERJOURNALENTRY].[VOUCHERDATAAREAID] = @toCompany
where [dbo].[SUBLEDGERJOURNALENTRY].[VOUCHERDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[SUBLEDGERVOUCHERGENERALJOURNALENTRY]
set [dbo].[SUBLEDGERVOUCHERGENERALJOURNALENTRY].[VOUCHERDATAAREAID] = @toCompany
where [dbo].[SUBLEDGERVOUCHERGENERALJOURNALENTRY].[VOUCHERDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTITEMGROUPITEM]
set [dbo].[INVENTITEMGROUPITEM].[ITEMGROUPDATAAREAID] = @toCompany 
where [dbo].[INVENTITEMGROUPITEM].[ITEMGROUPDATAAREAID] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[INVENTITEMGROUPITEM]
set [dbo].[INVENTITEMGROUPITEM].[ITEMDATAAREAID] = @toCompany 
where [dbo].[INVENTITEMGROUPITEM].[ITEMDATAAREAID] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[INVENTITEMSETUPSUPPLYTYPE]
set [dbo].[INVENTITEMSETUPSUPPLYTYPE].[ITEMDATAAREAID] = @toCompany 
where [dbo].[INVENTITEMSETUPSUPPLYTYPE].[ITEMDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[LEDGERINTERCOMPANY]
set [dbo].[LEDGERINTERCOMPANY].[COMPANY] = @toCompany 
where [dbo].[LEDGERINTERCOMPANY].[COMPANY] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[LEDGER]
set [dbo].[LEDGER].[NAME] = @toCompany 
where [dbo].[LEDGER].[NAME] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[LEDGERJOURNALTRANS]
set [dbo].[LEDGERJOURNALTRANS].[COMPANY] = @toCompany 
where [dbo].[LEDGERJOURNALTRANS].[COMPANY] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[INVENTDIMIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[INVENTDIMIDDATAAREA] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[ITEMIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[ITEMIDDATAAREA] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[PROJTAXGROUPIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[PROJTAXGROUPIDDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[PROJTRANSIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[PROJTRANSIDDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[PURCHIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[PURCHIDDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[TAXGROUPDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[TAXGROUPDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[TAXITEMGROUPDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[TAXITEMGROUPDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[VENDACCOUNTDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[VENDACCOUNTDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHTABLEVERSION]
set [dbo].[PURCHTABLEVERSION].[PURCHIDDATAAREAID] = @toCompany 
where [dbo].[PURCHTABLEVERSION].[PURCHIDDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[WORKCALENDAREMPLOYMENT]
set [dbo].[WORKCALENDAREMPLOYMENT].[CALENDARDATAAREAID] = @toCompany 
where [dbo].[WORKCALENDAREMPLOYMENT].[CALENDARDATAAREAID] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[WORKFLOWTABLE]
set [dbo].[WORKFLOWTABLE].[DATAAREA] = @toCompany 
where [dbo].[WORKFLOWTABLE].[DATAAREA] = @fromCompany

update  [AX_Dynamics_DMTesting].[dbo].[ECORESTRACKINGDIMENSIONGROUPITEM]
set [dbo].[ECORESTRACKINGDIMENSIONGROUPITEM].[ITEMDATAAREAID] = @toCompany 
where [dbo].[ECORESTRACKINGDIMENSIONGROUPITEM].[ITEMDATAAREAID] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[GENERALJOURNALENTRY]
set [dbo].[GENERALJOURNALENTRY].[SUBLEDGERVOUCHERDATAAREAID] = @toCompany 
where [dbo].[GENERALJOURNALENTRY].[SUBLEDGERVOUCHERDATAAREAID] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[INVENTTRANSORIGINPURCHLINE]
set [dbo].[INVENTTRANSORIGINPURCHLINE].[PURCHLINEDATAAREAID] = @toCompany 
where [dbo].[INVENTTRANSORIGINPURCHLINE].[PURCHLINEDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[NUMBERSEQUENCESCOPE]
set [dbo].[NUMBERSEQUENCESCOPE].[DATAAREA] = @toCompany 
where [dbo].[NUMBERSEQUENCESCOPE].[DATAAREA] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[DirPartyTable]
set [dbo].[DirPartyTable].[DATAAREA] = @toCompany 
where [dbo].[DirPartyTable].[DATAAREA] = @fromCompany

  UPDATE [AX_Dynamics_DMTesting].[dbo].[NUMBERSEQUENCEHISTORY]
SET [NUMBERSEQUENCEHISTORY].[FORMAT] = @toCompany + SUBSTRING([FORMAT], 5, Len([FORMAT]) - 4)
WHERE [FORMAT] LIKE @fromCompany + '%' 

  UPDATE
[AX_Dynamics_DMTesting].[dbo].[NUMBERSEQUENCETABLE]
SET
[NUMBERSEQUENCETABLE].[FORMAT] = @toCompany + SUBSTRING([FORMAT], 5, Len([FORMAT]) - 4)
WHERE
[FORMAT] LIKE @fromCompany + '%' 
  

Step 3)  Search for all remaining instances by adding the fromCompany name to this query
Step 4) For some reason, the DirPartyTable creates a new record for a new company instead of updating it. You might need to Select to 1000 Rows in there and see if new company is created. There will be two rows where the DATAAREA column will have the new company name and the other one with the old company name. Manually delete the new company name and update the old one using the following command. ONLY RUN THIS IF THERE IS AN EXTRA RECORD WITH NEW COMPANY!

delete
from [AX_Dynamics_DMTesting].[dbo].[DIRPARTYTABLE]
  where [dbo].[DIRPARTYTABLE].DATAAREA = 'new company'

update [AX_Dynamics_DMTesting].[dbo].[DIRPARTYTABLE]
set [dbo].[DIRPARTYTABLE].DATAAREA = 'NewCompany'
where [dbo].[DIRPARTYTABLE].DATAAREA = 'OLDCompany'

Search everywhere (all tables and all fields) in a SQL database for a field

The following has been taken from thesiteDoctor.co.uk. Since the website server is not available anyore, here is a copy of it. All credit goes to the original creator.

I used this script to search for all instances of a legal entity in the database

DECLARE @SearchStr nvarchar(100)SET @SearchStr = '## Company name HERE##'

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.    -- Purpose: To search all columns of all tables for a given search string    -- Written by: Narayana Vyas Kondreddi    -- Site: http://vyaskn.tripod.com    -- Updated and tested by Tim Gaunt    -- http://www.thesitedoctor.co.uk    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010    -- Date modified: 03rd March 2011 19:00 GMT    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    SET NOCOUNT ON
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)    SET  @TableName = ''    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
    WHILE @TableName IS NOT NULL    
    BEGIN        SET @ColumnName = ''        SET @TableName =         (            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))            FROM     INFORMATION_SCHEMA.TABLES            WHERE         TABLE_TYPE = 'BASE TABLE'                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName                AND    OBJECTPROPERTY(                        OBJECT_ID(                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)                             ), 'IsMSShipped'                               ) = 0        )
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)            
        BEGIN            SET @ColumnName =            (                SELECT MIN(QUOTENAME(COLUMN_NAME))                FROM     INFORMATION_SCHEMA.COLUMNS                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName            )    
            IF @ColumnName IS NOT NULL            
            BEGIN                INSERT INTO #Results                EXEC                (                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2                )            END        END  
    END
    SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results

Enable SSL in Emails

Recently, the requirement was to enable SSL for emails. There is a hidden but easy parameter that controls it.

Navigate to Classes->SysEmailDistributor->processEmails

and add the following code


CodeAccessPermission::revertAssert();

                    mailer.smtpRelayServer(relayServer,portNumber,userName,password,ntlm);
mailer.enableSsl(parameters.EnableSSL); // locus90 edit 09/11/2015
i.e.

You should also add code to catch any new exception because of this in the catch block.

 e = ClrInterop::getLastException();//where e is of the type System.exception
                    while (e)
                    {
                        info(e.get_Message());
                        e = e.get_InnerException();
                    }
i.e.

That should enable SSL.

Bonus: To add an attachment to an email, follow this link https://blogs.msdn.microsoft.com/czdaxsup/2008/12/03/howto-sending-mail-from-ax-using-net-framework/