AX- Flush statistics for specific tables to force SQL server to regenerate execution plan

On a newly upgraded CU7 environment, I found that it was taking several minutes to find an exact match when searched through the filter about the grid.

But, if I search it in the table like this, it was instant.

Turns out, SQL server was using a bad execution plan. To fix this, the following code flushes the statistics for specific table which forces SQL server to regenerate the execution plan.

static void gpupdateStats(Args _args)

{

    setPrefix("Updating");

    WinAPIServer::updateStatsTable(tableNum(CustTable));

    WinAPIServer::updateStatsTable(tableNum(DirPartyTable));

    WinAPIServer::updateStatsTable(tableNum(LogisticsPostalAddress));

    WinAPIServer::updateStatsTable(tableNum(LogisticsElectronicAddress));

    WinAPIServer::updateStatsTable(tableNum(DirPartyLocation));

    WinAPIServer::updateStatsTable(tableNum(LogisticsLocation));

    WinAPIServer::updateStatsTable(tableNum(LogisticsAddressCountryRegion));

}

The following method is added to the WinAPIServer class:

 public static server void sqlExecStatement(str _sqlStatement)

{

    SqlStatementExecutePermission   permission;

    Connection                      connection;

    Statement                       statement;

    ;

    connection  = new Connection();

    statement   = connection.createStatement();

    connection.ttsbegin();

    permission = new SqlStatementExecutePermission(_sqlStatement);

    permission.assert();

    //BP deviation documented

    statement.executeUpdate(_sqlStatement);

    CodeAccessPermission::revertAssert();

    connection.ttscommit();

}

public static server void updateStatsTable(tableid _tableId)

{

    str         sqlStatement = "update statistics %1";

    DictTable   dictTable;

    ;

    dictTable = new DictTable(_tableId);

    sqlStatement = strfmt(sqlStatement,dictTable.name(DBBackEnd::Sql));

    setprefix(strfmt("Running sql statement : %1",sqlStatement));

    startLengthyOperation();

    WinAPIServer::sqlExecStatement(sqlStatement);

    info("Done");

    endLengthyOperation();

}

Bonus tip: to see all the uncommitted transaction on a table in sql do a query

set transaction isolation level read uncommitted 
SELECT *
  FROM INVENTTRANSFERPARMLINE
  where INVENTTRANSFERPARMLINE.parmID = 'xxx'

AX 2012 - Send reports as an email without using outlook

I was working for a client and could not find a way to send purchase order confirmation as an email using smtp and not with outlook.

It turns out, AX sends batch mails as smtp, but single mails are sent using outlook, this is decided in SRSReportRunMailer->initMailer.

To send it as email for all invoices, all I needed to do was copy the code as in batch and paste it for non batch. Just make sure it is what they need, as it will effect all sales and purchase documents.