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
- 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.
- Clear all user’s email addresses, except for a specific subset.
- 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)
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) = 'firstname.lastname@example.org'
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX - DEVELOPMENT'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'email@example.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')
---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---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 = 'firstname.lastname@example.org'
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!