Get InventSite Address from InventSite ID in AX 2012

I was trying to find the primary address stored for inventSite and almost all articles pointed to complicated queries or views and it looked like it shouldn't be that difficult.

After wasting about 2 hours, it turns out, there is an easy way to get the address. The LogisticsPostalAddress takes the table record as it is and returns the address which is pretty cool. 

The following job takes inventsiteID and returns the delivery address specified in the invent site. Hope it will help someone.

static void addressTest(Args _args)

{

    InventSite site = InventSite::find("S02");

    LogisticsPostalAddress address = LogisticsLocationEntity::findPostalAddress(site, LogisticsLocationRoleType::None);

    info(address.Address);

}

I could not find a simpler way to get the contact details from here. I am currently using the following, but there should be a better way to achieve it.

private Phone getSitePhone(InventSiteId _inventSiteID)

{

    InventSite site = InventSite::find(_inventsiteID);

    LogisticsElectronicAddress  logisticsElectronicAddress;

    LogisticsLocationEntity locationEntity = LogisticsLocationEntity::findLocation(site, LogisticsLocationRoleType::Delivery, DirUtility::getCurrentDateTime(),true);

    RecId parentRecID= locationEntity.parmLocationRecId();

   LogisticsLocation           logisticsLocation;

   if (parentRecID )

   {

      select logisticsLocation

           where  logisticsLocation.ParentLocation == parentRecID

           join logisticsElectronicAddress

               where logisticsElectronicAddress.Type     == LogisticsElectronicAddressMethodType::Phone &&

                   logisticsElectronicAddress.location == logisticsLocation.RecId;

   }

   return logisticsElectronicAddress.Locator;

}

SQL info Script - get details about what sql database is doing.

I recently wanted to see if SQL was not over eating the resources. Apart from checking the disks, the task manager and resource monitor, the following script helped me. Just copying and pasting it to the sql server management studio and running it shows
"SQLServer:Buffer Manager-Page life expectancy" whose value should not be too low.

-- SQL INFO TEMPDB CONFIG AND SQL INFO SNAPSHOT 

USE Master
GO

SELECT getdate() as myCurrentDateTime, 
@@SERVERNAME as myServerName, 
os.Cores, df.Files 
FROM 
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os, 
(SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df; 
GO 

SELECT 
   name AS FileName, 
   size*1.0/128 AS FileSizeinMB, 
   type_desc, 
   CASE max_size 
       WHEN 0 THEN 'Autogrowth is off.' 
       WHEN -1 THEN 'Autogrowth is on.' 
       ELSE 'File will grow to a maximum size of 2 TB.' 
   END, 
   growth AS 'GrowthValue', 
   'GrowthIncrement' = 
       CASE 
           WHEN growth = 0 THEN 'Size is fixed and will not grow.' 
           WHEN growth > 0 AND is_percent_growth = 0 
               THEN 'Growth value is in 8-KB pages.' 
           ELSE 'Growth value is a percentage.' 
       END 
FROM tempdb.sys.database_files ORDER BY FileName ASC; 
GO 

begin 
     select getdate() as myDateTime, 
     @@SERVERNAME as myServer, 
     SERVERPROPERTY('ProductVersion') as myVersion, 
     SERVERPROPERTY('Edition') as myEdition 
     exec sp_readerrorlog 0, 1, 'using locked pages for buffer' 
     exec sp_readerrorlog 0, 1, 'significant part of sql server process memory' 
     exec sp_readerrorlog 0, 1, 'taking longer than 15 seconds to complete' 
     dbcc tracestatus 
     exec sp_configure 'show advanced options', 1 
     reconfigure 
     exec sp_configure 'max server memory (MB)' 
     exec sp_configure 'max degree of parallelism' 
     select object_name,counter_name,cntr_value 
     from   master..sysperfinfo 
     where  counter_name IN ('Total Server Memory (KB)','Target Server Memory (KB)', 'Page life expectancy', 'User Connections') 
     AND instance_name = '' 
end 

Getting lines grid in parent table like sales lines grid in sales table

When creating a child table to create information of the lines of the header and joining it to the form, AX does not like the standard relations of outerjoin.

The only way to do it is to Add the data source to the form and link it as delayed. Then in the execute query method, clear dynalinks and add the link again as mentioned here.

How to get a field directly from args when opening a form.

The standard code I used to get a field from args was

InventTrans inventTrans = element.args().record() as InventTrans;

info(int642str( inventTrans.recID));

Recently found a way to just use

info(int642str(element.args().record().(fieldNum(InventTrans, RecId)))); 

which save all the work of type casting and does it all in one line.

Time Consumed method

If you want to get the total time consumed by a method, you can use the timeConsumed method in the following way which works great to show how long a method took to run.


FromTime startTime = timeNow();
//blahblah.doiT()
    info(strFmt("Total time consumed is  %1", timeConsumed(startTime, timeNow())));

Get the directory button working for EDT FilePath

Just copy and paste these methods as it is to forms -> methods to get the FilePath EDT file directory button working.

str filePathLookupTitle()

{

     return "Select Directory";

}

str fileNameLookupTitle()

{

     return "Select a folder for export";

}

str filenameLookupInitialPath()

{

    return "";

}

container fileNameLookupFilter()

{

      #File

      Filename filepath;

      Filename filename;

      Filename fileExtention;

[filepath, filename, fileExtention] = Global::fileNameSplit("");

     if (!fileExtention)

     {

         fileExtention = #txt;

     }

      return [WinAPI::fileType(fileExtention),#AllFilesName+fileExtention, #AllFilesExt, #AllFilesType];

}

str fileNameLookupFilename()

{

     Filename filepath;

     Filename filename;

     Filename fileType;

     [filepath, filename, fileType] = fileNameSplit("");

     return filename + fileType;

}

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/