Microsoft Dynamics AX Archives - Page 11 of 16

Axapta

Enable batch e-mail alerts from code

The setup of notifications for batch jobs are quite simple if you know where to look. To enable batch e-mail alerts from code all you need to do is create new records under \\Tables\BatchJobAlerts with the right flags for an AX user, who has their e-mail address set in the User Options correctly.

The following X++ Job is self-explanatory on how to enable batch e-mail alerts from code in case of Cancellation or an Error:

static void WIK_enableBatchJobAlerts(Args _args)
{
    #Batch
    Map             alertsMap;
    Map             alertsEntity;
    BatchJob        batchJob;
    BatchJobAlerts  batchJobAlerts;
    
    while select batchJob
        where (batchJob.Status      == BatchStatus::Executing
            || batchJob.Status      == BatchStatus::Waiting)
            // Optional filtering for dedicated batch service account only
            && batchJob.createdBy   == 'axbatchuserid'
        notexists join batchJobAlerts
            where  batchJobAlerts.BatchJobId    == batchJob.RecId
                // Include all batch jobs where e-mail alert is disabled for our account
                && batchJobAlerts.UserId        == batchJob.createdBy
                && batchJobAlerts.Email         == NoYes::Yes
    {
        // This can be called any number of times to alert multiple users even with different settings
        alertsMap = BatchJobAlerts::addAlertsToMap(
            batchJob.createdBy, // Whom to alert
            NoYes::No,          // Ended
            NoYes::Yes,         // Error
            NoYes::Yes,         // Canceled
            NoYes::No,          // Popup
            NoYes::Yes          // E-mail
            );
        
        BatchJobAlerts::saveAlerts(batchJob.RecId, alertsMap);
        
        alertsEntity = alertsMap.lookup(batchJob.createdBy);
        
        info(strFmt('Enabling alert (Ended=%1 Error=%2 Canceled=%3 Popup=%4 E-mail=%5) for user <%6> and batch <%7>',
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobEnded)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobError)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobCanceled)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#popup)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#email)),
            batchJob.createdBy,
            batchJob.Caption
            ));
    }
}

The output will be something like this:

Enabling alert (Ended=No Error=Yes Canceled=Yes Popup=No E-mail=Yes) for user <sa.axbat> and batch <ADMIN – Due date alerts – every 15m>

By DAXRunBase|2016-02-17T16:03:08+01:00February 17th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, batch, job, X++|1 Comment

AX 7 Technical Conference and Virtual Launch Event

We are approaching two important dates within the next couple of weeks to introduce the new major version, namely the AX 7 Technical Conference and Virtual Launch Event for the actual release highlights. Project Rainier is now stepping out from behind the curtains to reveal a new era of what an ERP system of the future looks like, bringing a lot of new technologies to the table, while building on the existing functionality of Microsoft Dynamics AX 2012 R3 CU9.

The Virtual Launch Event will not probably reveal too many additional details on top of what we have already learned at Convergence and various early release channels, but could be interesting to see how the first customers are using it as their current Production environment.

On a side note keep in mind that wave 1 is only for new implementations, since there is no data conversion upgrade path yet for existing AX implementations. It will come second half of 2016, hopefully they will announce the wave 2 release date too.

Make sure you register for the launch event in time using the following link:
https://presentations.inxpo.com/Shows/microsoft/Dynamics/AX/Launch2016/Registration/Registration.html

Keep an eye out for the Technical conference app too, it should be downloadable soon. You do not want to miss any important break-out sessions whether your are doing a new implementation, or upgrading an existing one.

https://www.microsoft.com/en-us/dynamics/techconference/Session-Catalog.aspx

See you in person in Seattle at the AX 7 Technical conference and at the Virtual Launch Event two weeks later!

By DAXRunBase|2016-02-05T17:54:58+01:00February 5th, 2016|Categories: AX 7|Tags: AX 7, Technical conference|1 Comment

Improve AX performace by fixing bad Query plans

Sometimes the Purchase invoicing department has complained that posting took a very long time to complete. We could correlate this with the slow performing SQL statements alert explained in my previous post to identify the cause, now it is time to improve AX performace by fixing bad Query plans. My colleague, Peter Prokopecz provided some great insights on resolving this problem.

Either an AX trace, or the SQL alert could reveal the bad code. In our case it was the standard functionality of matching Tax transactions with the General journal entries, which can be found in \Data Dictionary\Tables\TaxTransGeneralJournalAccountEntry\Methods\create. It is a very large insert_recordset statement with multiple inner and exists joins:

(more…)

By DAXRunBase|2016-01-08T17:57:45+01:00January 8th, 2016|Categories: AX 2012|Tags: AX 2012, cache, fix, kernel, query plan, SQL, tables, trace, X++|4 Comments

E-mail alerts for slow performing SQL statements in AX

There are not that many tools to assist us in proactively monitoring our AX database health. I have created a set of SQL jobs that could generate e-mail alerts for slow performing SQL statements in AX to improve the system predictability.

If you are using DynamicsPerf already, it is very likely that the SQL Trace in the User options got already enabled to capture slow performing statements that take longer than 5 seconds, otherwise you could enable that yourself to start capturing data in SysTraceTableSQL in your AX database.

The information stored in the aforementioned table can be analyzed and aggregated to show what tables have been performing slow since the last execution of my job each day. I am also separating wildcard searches, since the LIKE statements mostly do not reuse query plans and you cannot improve their speed much further. It is however a must to keep an eye on the tables with high number of increments for possible outdated query plans, old statistics, missing indexes or poorly designed AX statements.

These are the requirements and steps for setting alerts for slow performing SQL statements in AX:

Enable SQL Trace with a sensible timeout for the AX users to be monitored.
Create a database where the daily aggregated slow performing statements and the Table name extracting function will be stored, in my sample code as [master].
Make sure there is an e-mail profile set up for sending the mails from under SQL Server Management Studio > SQL instance > Management > Database Mail, in my sample code as MYEXCHANGE.
Create a new SQL Server Agent Job and paste the attached statement as a Transact-SQL body. Make sure you replace the variables and values for the administration database ([master]), the target AX database (MyAXDB), mail profile (MYEXCHANGE) and destination e-mail address values.
Add a daily Recurring schedule to the Job to occur every 2 hours, or any interval to your liking below 24 hours
Run the job manually for the first time to create the table/function and pre-populate the records

Once the setup is successfully completed and a couple of slow statements gets recorded in the SysTraceTableSQL table in AX, the next scheduled run will pick up the values and send out the e-mail alert if you have done everything correctly.

WIK_SlowPerformingStatements_Job

By DAXRunBase|2016-08-16T11:09:44+02:00January 7th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, e-mail, performance, SQL, statements|4 Comments

Default AX model for all users

If you want to set the default AX model for all of your active user accounts on a certain layer, you may run the following job to do so.

Whenever a new developer joins in, you may include running this job in your account creation process to make sure they do not try to check in code to the wrong place, but only to your correct default AX model.

Do not forget to change the layer and the name of the model.

static void WIK_UpdateDefaultModel(Args _args)
{
    UserInfo                userInfo;
    UserInfoStartupModel    userInfoStartupModel;
    UserInfoStartupModel    userInfoStartupModelDB;
    UtilEntryLevel          layer = UtilEntryLevel::cus;
    ModelId                 modelId = any2int((select firstOnly Model from SysModelManifest where SysModelManifest.Name == 'MyModel').Model);

    ttsBegin;

    while select userInfo
        where userInfo.accountType  == UserAccountType::ADUser
        notexists join userInfoStartupModel
            where  userInfoStartupModel.UserId  == userInfo.id
                && userInfoStartupModel.Layer   == layer
                && userInfoStartupModel.ModelId == modelId
    {
        userInfoStartupModelDB.clear();
        userInfoStartupModelDB.initValue();
        userInfoStartupModelDB.Layer            = layer;
        userInfoStartupModelDB.UserId           = userInfo.id;
        userInfoStartupModelDB.ModelId          = modelId;
        
        try
        {
            if (userInfoStartupModelDB.validateWrite())
            {
                userInfoStartupModelDB.insert();
            }
            else
            {
                throw error('We have a problem');
            }
        }
        catch
        {
            exceptionTextFallThrough();
        }
    }

    update_recordset userInfoStartupModel
        setting ModelId = modelId
        where userInfoStartupModel.Layer    == layer;

    ttsCommit;
}

MSDN link for UserInfoStartupModel: https://msdn.microsoft.com/en-us/library/userinfostartupmodel.aspx

By DAXRunBase|2015-12-22T14:47:21+01:00December 22nd, 2015|Categories: AX 2012|Tags: AX 2012, layer, model, users, X++|1 Comment