AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

In my series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, we have so far covered various configuration and setting changes to Dynamics AX. Today I will move on to some suggested data changes that you may wish to consider making when doing database restores. These changes won’t affect the functioning of AX, but may prevent some awkward or confusing situations like clients or user’s receiving emails etc..

These data cleanup scripts are really meant as guidelines to help you in creating your own scripts, rather than a comprehensive set of “must-do” scripts.

The two changes for today are as follows

  1. Setting your email templates to clearly indicate that they originate from a testing system. It may be perfectly legitimate for users to receive emails from your testing or development environments, e.g. for testing workflows, however it is very helpful for the users to clearly see that they are viewing test data rather than production data. So setting the senders name and address to indicate “Development” will assist.
    Email templates are found under: Organisation administration -> Setup -> Email templates
    2. Setting user email addresses. We obviously don’t want to send emails to users who have no interest in our development systems so one may, depending on your needs, want to do one of the following.

    1. Clear all user’s email addresses, except for a specific subset.
    2. Set some or all user’s email addresses to a single address (e.g. You want emails to still be generated to see that they are working, but you would like to see the contents of them yourself and not expose the user to the test data)

These settings can be found under System Administration -> Users -> Options (ribbon button) ->  Email

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

---Email Template Settings---Declare @WFAPPROVER_ADD varchar(50) = ''
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX - DEVELOPMENT'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = ''

---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

---Update Email Templates---
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

Note, the above example clears all user email addresses except for a specific set, you could easily adjust this to set them all to a specific address as follows:

update sysuserinfo set sysuserinfo.EMAIL = ''


I hope this assists you and will be useful in your management of your AX environments. Keep an eye out for new posts in this series!

View Previous – AX DB Restore Scripts #6 – SMTP Server and DMF Folder

View Next – AX DB Restore Scripts #8 – Disable users

Back to List

Setup DB Logging in X++ (Updating events)

Setting up AX Database logging via the user interface wizard can at times be a bit of a cumbersome and slow task, especially for tables that are not very common. The job below will simply set database logging for all fields on a specific table. Simply replace “InventSalesSetup” with the table name of your own choosing. As always, test this in a non-production environment to confirm that it performs suits your own needs.

static void setDBLogOnTable(Args _args)
    TableId tableId = tableNum(InventItemSalesSetup);
    DatabaseLog log;
    SysDictTable dictT = new SysDictTable(tableId);
    SysDictField dictF;
    Set fields;
    SetEnumerator   se;

    log.logType = DatabaseLogType::Update;
    log.logTable = tableId;
    fields = dictT.fields(false,false,true);
    se = fields.getEnumerator();
    while (se.moveNext())
        dictF = se.current();

        log.logField =;
        info(strFmt("Adding field %1",;


Mass Reassign Workflows

Challenge/Problem: Many Dynamics AX Workflow work-items that need to be reassigned at once.

Description: At times we have had the request from clients to reassign many workitems from one person to another. My first reaction is why weren’t delegation parameters setup on the user so that one doesn’t need to manually reassign. However I have come to realise over time that there are a couple of good reasons to do so such as a person falling ill suddenly, a mistake in the workflow config, failure to set delegation parameters in time etc…

Solution: The following script/job designed for AX workflow will reassign workitems from one user to another. You can modify your query a bit to restrict the items to just the one that you want. The attached one simply reassigns all items currently assigned to user “123456” to user “654321”.

NOTE: This will not affect items in your workitem list as a result of queues

static void workflowMassReassign(Args _args)
    UserId fromUser = "123456"
    UserId toUser = "654321";
    //Comment for workflow history
    str comment = "Auto-Reassign 2014/11/26 08:00";
    WorkflowWorkItemTable workitems;
    int i;
    while select workitems where workitems.Status == WorkflowWorkItemStatus::Pending && workitems.UserId == fromUser 
        WorkflowWorkItem::delegateWorkItem(workitems.Id, toUser, comment);
    info(strFmt("Items re-assigned: %1",i));

Sync System Email Templates to all companies.

Challenge / Problem: Maintaining email templates across multiple companies.

Descritpion: Dynamics AX makes use of email templates for various bits of functionality in Dynamics AX, including workflow notifications and alert notifications. If you are using workflow in multiple companies and want to keep the same workflow template across the board, it can be quite frustrating to have to make the same changes in every company. The following script/job in X++ will help sync all (or some) system email templates and their respective languages into every company in Dynamics AX.

/// Copies All System Email Template to all companies
/// WARNING: Will create or overwrite existing templates in other companies
static void syncWorkflowTemplates(Args _args)
    DataArea  DataArea;
    SysEmailSystemTable email;
    SysEmailTable local;
    SysEmailMessageSystemTable message;
    SysEmailMessageTable localMess;
    WorkflowParameters params;

    void FindOrCreate()
        local = SysEmailTable::find(email.EmailId, true);
        if (local.RecId)
            info(strFmt("Deleting %1 (%2)", local.EmailId, curext()));
        local.DefaultLanguage = email.DefaultLanguage;
        local.Description       = email.Description;
        local.EmailId           = email.EmailId;
        local.Priority          = email.Priority;
        local.SenderAddr        = email.SenderAddr;
        local.SenderName        = email.SenderName;
        info(strFmt("Adding %1 (%2)", email.EmailId, curext()));

    void FindOrCreateMessage()
        localMess = SysEmailMessageTable::find(message.EmailId, message.LanguageId, true);
        if (localMess.RecId)
            info(strFmt("Deleting %1 %2 (%3)", localMess.EmailId, localMess.LanguageId, curext()));
        localMess.EmailId       = message.EmailId;
        localMess.LanguageId    = message.LanguageId;
        localMess.LayoutType    = message.LayoutType;
        localMess.Mail          = message.Mail;
        localMess.Subject       = message.Subject;
        localMess.XSLTMail      = message.XSLTMail;
        info(strFmt("Adding %1 %2 (%3)", localMess.EmailId, localMess.LanguageId, curext()));
    // Restrict to specific templates in a where clause if necessary
    while select email
        while select DataArea where !DataArea.isVirtual && != email.dataAreaId
            changecompany (DataArea.Id)
            while select message where message.EmailId == email.EmailId
                changecompany (DataArea.Id)