How does one get a copy of your company logo saved to file? (if you forgot to archive it like you should have originally)
One mechanism is using X++ as follows:
|
1 2 3 4 5 6 7 8 |
Bitmap logo; BinData data = new BinData(); changeCompany("[companydataarea]") { logo = FormLetter::companyLogo(); data.setData(logo); data.saveFile("C:\\Users\\jonathan.halland\\Desktop\\logo.bmp"); } |
Requirement: When choosing email as the report destination in, allow the user to edit the email contents in Outlook rather than automatically sending.
A number of users have been concerned that outlook automatically sends email destination reports, rather than allowing them to first modify or verify the email, subject and addresses in a “new email” window.
Solution: To fix this you can modify the “SRSReportRunMailer.emailReport” method. Depending on your version of AX, modify the section around line 41 as below.
|
1 2 3 4 5 6 7 |
result = inetMailer.sendMailAttach(_emailContract.parmTo(), _emailContract.parmCc(), _emailContract.parmSubject(), _emailContract.parmBody(), true, /* Ensure dialog is shown */ //original false, /* do not show dialog*/ _attachmentPath); |
Requirement: Allow requests made via AIF to modify fields with the table field property “AllowEdit” set to NO to be modified. By default if you modify a field in a service call that is non editable no errors occur but the field is not updated.
Solution: On your AXD service contract class, right click on the class , click “Override method” choose method “initFieldAccessOverrides” to override. Append the following line to the method
this.overRideFieldAccess(tableNum([YourTable]), fieldNum([YourTable], [YourField]), AxdFieldAccess::AllowEdit, NoYes::Yes);
Note: This can also be used to override the “editOnCreate” property of a table field for AIF usage.
I’ve had it a number of times when trying to update parameters on batch job tasks that I’ve received the cryptic message “Cannot edit a record in Batch transactions (Batch). The corresponding AOS validation failed.” Due to laziness or business I’ve always dismissed the problem and found a work around, but never fully tried to understand why this message occurs.
I’ve finally had the opportunity to drill into it and decided to share what I found.
Firstly: The message is generated because of the value “false” being returned from the overwritten method “aosValidateUpdate” on table “Batch”, this explains the text of the message “AOS validation failed”, but not the business logic reasons. Drilling into this method one can see the following business logic for allowing updates of batch tasks
1. The user doing the update must either be the person who created the record (createdBy field) or a person with access to the Display MenuItem “BatchJob” (see Batch->IsBatchOperator). (This does work for administrators who don’t have explicit access).
2. Batch tasks may not change the company, dataPartition or batchJobId. (This is unlikely to happen as they are disable from forms anyway)
3. If the user doing the update is NOT the person who created the job then the parameters and Class of the task cannot be changed – this is the primary reason why we were experiencing the issue.
Notes: I’m not sure why the rule around changing the parameters exists, but it does, so if you want to change this then you would need to either login as the creator and change the parameters, or modify via SQL the batch task to run under your user in the case of historical batch jobs where the user does not exist.
A few months ago I wrote a series of posts on things I wish somebody had told me as an X++ developer right at the beginning of my explorations into the world of Dynamics AX. Today I want to start on a new list, one prompted by years of looking over the shoulders of users and realizing that many of the very basic navigation tools that I know and take for granted are often not taught to or effectively utilized by users.
For example I’ve seen way too many users copying field data on one form E.G. Employee Id on Purchase requisition and then navigating to the Employee form to go and search for it instead of simply right clicking and then clicking view details.
The target audience of this series is not seasoned users of AX (although you may benefit too) but rather new users wanting a quick centralized list of tips and tricks that can be used to make your usage of Dynamics AX that much more efficient. Where possible I have will try to link the tips to pre-existing blog posts from other experts rather than than create yet more of the same AX posts for the web.
Just over a month ago I embarked on a series of posts on SQL scripts that I use when restoring Dynamics AX databases from one environment to another. We have covered topics including server setups, general configurations as well as data cleans to get your new environment up and smoothly running as quickly as possible. We are now at the end of the series and I trust it has been helpful to you in structuring your scripts.
I’d just like to remind you that these posts and scripts are not meant to be used as is but rather to serve as a guide to adapt for your specific scenario and to prompt your own thinking as to what you will need.
That said you can download my templated script here or view it below (download)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME' ---Reporting Services--- Declare @REPORTINSTANCE varchar(50) = 'AX' Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX' Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX' Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]' Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]' Declare @REPORTFOLDER varchar(20) = 'DynamicsAX' Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS'; ---SSAS Services--- Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX' Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration ---BC Proxy Details --- Declare @BCPSID varchar(50) = '[BCPROXY_SID]' Declare @BCPDOMAIN varchar(50) = '[yourdomain]' Declare @BCPALIAS varchar(50) = '[bcproxyalias]' ---Service Accounts --- Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc' Declare @PROJSYNCACCOUNT varchar(20) = 'syncex' ---Help Server URL--- Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc' ---Outgoing Email Settings--- Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server Declare @SMTP_PORT int = 25 ---DMF Folder Settings --- Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ ' ---Email Template Settings--- Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING' Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com' ---Email Address Clearing Settings--- DECLARE @ExclUserTable TABLE (id varchar(10)) insert into @ExclUserTable values ('userid1'), ('userid2') --List of users separated by | to keep enabled, while disabling all others Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|' --List of users separated by | to disable, while keeping all the rest enabled all others --Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|' --*****BEGIN UPDATES*******--- ---Update AOS Config--- delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG) update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1 where serverid != @AOS -- Optional if you want to see the "affected row count" after execution. ---Update Batch Servers--- delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID) update BATCHSERVERGROUP set SERVERID=@AOS where serverid != @AOS -- Optional to see "affected row count" update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]' update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]' ---Update Reporting Services--- delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS) update SRSSERVERS set SERVERID=@REPORTSERVERID, SERVERURL=@REPORTSERVERURL, AXAPTAREPORTFOLDER=@REPORTFOLDER, REPORTMANAGERURL=@REPORTMANAGERURL, SERVERINSTANCE=@REPORTINSTANCE, AOSID=@AOS, CONFIGURATIONID=@REPORTCONFIGURATIONID, DESCRIPTION=@REPORTDESCRIPTION where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution. ---Update SSAS Services--- delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer) update BIAnalysisServer set SERVERNAME=@SSASSERVERNAME, DESCRIPTION=@SSASDESCRIPTION, ISDEFAULT = 1 WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows ---Set BCPRoxy Account--- update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS where NETWORKALIAS != @BCPALIAS --optional to display affected rows. ---Set WF Execution Account--- update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT ---Set Proj Sync Account--- update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT ---Set help server URL--- update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver ---Update Email Parameters--- Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT ---Update DMF Settings--- update DMFParameters set SHAREDFOLDERPATH = @DMFFolder where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows ---Set BCPRoxy Account--- update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS where NETWORKALIAS != @BCPALIAS --optional to display affected rows. ---Set WF Execution Account--- update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT ---Set Proj Sync Account--- update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT ---Set help server URL--- update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver ---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) ---Disable all users except for a specific set--- update userinfo set userinfo.enable=0 where CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0 ---Disable specific users--- ---update userinfo set userinfo.enable=0 where CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0 --Clean up server sessions delete from SYSSERVERSESSIONS --Clean up client sessions. delete from SYSCLIENTSESSIONS |
A few weeks ago I embarked on a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another. We have covered a variety of topics including various configuration and setting changes to Dynamics AX. This post will be the last in the set of “data cleanup scripts” designed to help scrub your data for use in a development or testing environment.
Disabling users.
Quite often companies or partners will require access to development environments to be restricted to a very select group of users that are aware of what to test and are also careful enough to work methodically in the correct environments (i.e. not trying to to real work in the wrong environment or test work in production). To ensure all of the above one could follow a few paths.
All of these solutions do not physically stop users from accessing the alternate environment. So if you need an additional security mechanism, simply disable the users either manually in AX after doing a restore or disable then along with the SQL scripts we have been building up over the past few weeks.
To disable users in AX navigate to “System Administration -> Common -> Users -> Users. Double click on the user in question, click “edit”, uncheck the “enabled” button.
The accomplish the same in a SQL script you can do the following. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 9 10 |
--List of users separated by | to keep enabled, while disabling all others Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|' update userinfo set userinfo.enable=0 where CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0 --List of users separated by | to disable, while keeping all the rest enabled all others Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|' update userinfo set userinfo.enable=0 where CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0 |
Note 1: Parameterising a list can be tricky in TSQL, so the above trick is what i found easiest.
Note 2: You will need to decide whether the list of users provided is inclusive (i.e. provide the list of all the users you WANT to disable) or exclusive (i.e. disable all users except for a small subset). I have provided both options, but would recommend the exclusive approach as it is more likely to give you safely give you what you expect.
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 #7 – Setting Email Templates and User Email Addresses
One of the nifty features of Dynamics AX is to share form personalisations between user. To see how to do so check out Murray Fife’s article over here. However it seems that a distinct restriction is that you cannot effectively share personalisations between companies, even for the same user. This means that you need to perform the same setups for every form that you are working on.
I thought I would share with you today a quick customization that will assist allow you to copy your own customizations from one company to another or copy from a different user’s profile in another company to yours.
Our customization will make a modification to the “SysSetupForm” in AX. This is the form that is displayed when right clicking on a field in AX and clicking “Personalise”
In this post we will basically duplicate the “Retrieve from User” button to provide a “Retrieve from Alternate company” option in addition to the basic functionality. You could also simply customize the standard function as well.
Step 1. Create a new button
Step 2. Update button to display profiles from all users and companies
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
void loadFromUserAll() { SysLastValue sysLastValue; userId userId; CompanyId companyId; ; if (!loadFromUserMapAll) { loadFromUserMapAll = new Map(Types::String, Types::String); while select sysLastValue group by UserId, Company where sysLastValue.Company != this.lastValueDataAreaId() && sysLastValue.RecordType == UtilElementType::Usersetup && sysLastValue.ElementName == this.lastValueElementName() && sysLastValue.DesignName { loadFromUserMapAll.insert(sysLastValue.UserId+"|"+sysLastValue.company, strFmt("%1 (%2)", sysLastValue.UserId, sysLastValue.company)); } } if (loadFromUserMapAll.elements()) { [userId,companyId] = str2con(pickUser(loadFromUserMapAll, true),"|"); if (userId) { element.loadSetupCompany(userId,companyId); } } else { info("@SYS73299"); } } |
Step 3: Provide logic to load the setup on selection
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
// AosRunMode::Client public void loadSetupCompany(userId userId, CompanyId _company) { SysLastValue sysLastValue; container value; Name designName; Map map; ; select firstonly sysLastValue where sysLastValue.Company == _company && sysLastValue.UserId == userId && sysLastValue.RecordType == this.lastValueType() && sysLastValue.ElementName == this.lastValueElementName() && sysLastValue.DesignName != ''; if (sysLastValue) { map = new Map(Types::String, Types::String); while select designName from sysLastValue where sysLastValue.Company == _company && sysLastValue.UserId == userId && sysLastValue.RecordType == this.lastValueType() && sysLastValue.ElementName == this.lastValueElementName() && sysLastValue.DesignName != '' { map.insert(sysLastValue.DesignName, sysLastValue.DesignName); } designName = pickList(map, "@SYS28107","@SYS28107", true); if (designName) { value = xSysLastValue::getValue( _company, userId, this.lastValueType(), this.lastValueElementName(), designName); xSysLastValue::putValue( value, this.lastValueDataAreaId(), curUserId(), this.lastValueType(), this.lastValueElementName(), ''); element.disableLayoutChanges(); } } else { info(strfmt("@SYS28106", userId)); } } |
You’re all done!
Compile your form and access it from anywhere in AX by right clicking on a field and clicking Personalise.
You should now have a “Retrieve from Company” button.
When clicking on this button you will see a list of all user profiles for other companies, including your own with the company listed in brackets. Double click on the user name, then double click on the the personalisation name to load it.
I hope this helps make life more efficient and easy!
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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
---Email Template Settings---Declare @WFAPPROVER_ADD varchar(50) = 'workflowqa@norplats.co.za' Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX - DEVELOPMENT' Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'dev@mydomain.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:
|
1 |
update sysuserinfo set sysuserinfo.EMAIL = 'youraddress@mydomain.com' |
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
Recently I started creating a list and a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, typically from production to QA or development.
We have so far discussed a number of configurations that must be changed for the new system to operate effectively and starting yesterday we moved onto exploring more optional configurations.
Today we will cover two of these items namely re-configuring the outgoing SMTP server in AX and configuring the settings for the DMF working folder.
Note: In my experience the SMTP server will most likely not change between a production and development server, but you may need to reconfigure it if moving to a new domain etc. Similarly it will work to use the same DMF shared folder between PROD and DEV, however I would recommend changing this to keep the data 100% separate.
These two configurations are located in the Dynamics AX 2012 client under the following paths.
1. SMTP Server, accessed via System Administration -> Setup -> System -> E-mail parameters.
2. Data import, export framework shared working directory. Accessed via “Data import export framework -> Setup -> Data import export framework parameters”
To update these 2 settings above we need to do the following:
1. Update the SysEmailParameters table to update the SMTP server name (and additional login parameters)
2. Update the DMFParameters table to reflect the new shared folder path.
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
---Outgoing Email Settings--- Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server Declare @SMTP_PORT int = 25 ---DMF Folder Settings --- Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\' ---Update Email Parameters--- Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT ---Update DMF Settings--- update DMFParameters set SHAREDFOLDERPATH = @DMFFolder where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows |
I hope this assists you and will be useful in your management of your AX environments.
View next – AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses
View Previous – AX DB Restore Scripts #5 – Configure Service Accounts and Help Server
Last week I embarked on a series of posts on DB restore scripts for Dynamics AX by creating a list of items in your AX database that need to be updated/reconfigured when restoring a database from a production environment to a development or QA one.
So far we have covered the majority of the most critical issues to fix when doing a DB restore, so from today on most of the scripts are basically optional. They are optional for one of the following reasons: 1. It may be common to have the same configuration in both live and dev, yet in some circumstances they may differ e.g. Business connector may remain the same if you are restoring from live to dev, but not from live to a demo VPC.
2. The data is correct but for safety or security you may want to manipulate it for a development environment. E.G. Changing all email templates to clearly indicate the source is from a dev system.
Today we will cover two topics namely re-configuring the service accounts in AX and configuring the AX help server URL.
In the Dynamics AX 2012 client, the configurations that we are going to look at are:
1. System Service Accounts, accessed via System Administration -> Setup -> System -> System service Accounts
2. Batch Jobs, accessed via System Administration -> Setup -> System -> Help system parameters
To update the settings above we need to do the following:
1. Update the SYSBCPROXYUSERACCOUNT table to reflect the business connector user’s SID, networkdomain and network alias. For this step you will need to find out the SID of the business connector either by setting it manually the first time and then running select SID from SYSBCPROXYUSERACCOUNT where NETWORKDOMAIN='[yourdomain]’ and NETWORKALIAS='[yourbcuseralias]’ or follow one of the suggestions over here http://blogs.msdn.com/b/gaurav/archive/2014/06/03/get-sid-of-the-object-registry-wmic-powershell.aspx
2. Update the SYSWORKFLOWPARAMETERS table to reflect the userId of the workflow execution account.
3. Update the SYNCPARAMETERS table to reflect the userId of the Microsoft Project server synchronization service account.
4. Update SYSGLOBALCONFIGURATION to reflect new help server URL if necessary.
Notes:
1. If you are wanting to use an Alias/Network domain combination for either of the two execution accounts, I would recommend setting them manually in AX as they create users and permissions automatically when you do this.
2. The above script assumes that execution accounts you are setting exist in the new system and have the necessary permissions.
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
---BC Proxy Details --- Declare @BCPSID varchar(50) = '[BCPROXY_SID]' Declare @BCPDOMAIN varchar(50) = '[yourdomain]' Declare @BCPALIAS varchar(50) = '[bcproxyalias]' ---Service Accounts --- Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc' Declare @PROJSYNCACCOUNT varchar(20) = 'syncex' ---Help Server URL--- Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc' ---Set BCPRoxy Account--- update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS where NETWORKALIAS != @BCPALIAS --optional to display affected rows. ---Set WF Execution Account--- update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT ---Set Proj Sync Account--- update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT ---Set help server URL--- update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver |
I hope this assists you and will be useful in your management of your AX environments.
View Next – AX DB Restore Scripts #6 – SMTP Server and DMF Folder
View Previous – AX DB Restore Scripts #4 – Reconfigure batch jobs
Last week I embarked on a series of posts on DB restore scripts for Dynamics AX by creating a list of items in your AX database that need to be updated/reconfigured when restoring a database from a production environment to a development or QA one.
This post is closely tied to and relies on my previous post on re-configuring your AOS and Batch AOS instances to work, so ensure you have completed that script before continuing. Today I will be looking at re-configuring your actual batch jobs and batch groups to function correctly under your new environment.
In the Dynamics AX 2012 client the configurations that we are going to look at are:
1. Batch Groups, accessed via System Administration -> Setup -> Batch Groups
2. Batch Jobs, accessed via System Administration -> Inquiries -> Batch Jobs -> Batch Jobs
Depending on the configuration of your source system (e.g. Production) you may have multiple AOSs setup as batch servers whereas your DEV and QA would most likely just have a single AOS that functions as the batch AOS. You may also have a number of batch jobs that that you explicitly don’t want to run in a development environment, such as integrations to vendors, automatic ordering etc… Therefore our scripts will need to do the following:
1. For each of our batch groups we want to:
– Delete all but one of their selected/linked “batch servers”
– Re-point the remaining link to the current batch server setup in our previous script.
2. Disable batch jobs that we do not want to run in our development environment.
Before proceeding, please note, these scripts are intended as guidelines and do make various assumptions on the state of your environment. For example I’m assuming that your batch jobs were cleanly shutdown with their status’ either in “Error, ended, or waiting” (not Cancelling or Executing). Please adjust these scripts to suit your environment.
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 9 10 11 |
--Reuse the parameter declared for the SSRS and AOS update script Declare @AOS varchar(30) = '[AOSID]' -- Must be in the format '01@SEVERNAME' -- #1.1 Clear out but one link between each batch server group and batch server. delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID) -- #1.2 Repoint remaining links to the active aos/batch server. update BATCHSERVERGROUP set SERVERID=@AOS where serverid != @AOS -- Optional to see "affected row count" -- #2 - Disable batch jobs that should not run in a production environment. Copy the two lines below for each batch job you want to disable. update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]' update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]' |
I hope this assists you and will be useful in your management of your AX environments.
View Next – AX DB Restore Scripts #5 – Configure Service Accounts and Help Server
View Previous – AX DB Restore Scripts #3 – Configure AX servers and batch servers
Earlier this week I embarked on a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that need to be updated when restoring a database from a production environment to a development or QA one.
Today I will be drilling into how to reset/configure your AX AOS and batch AOS configurations via DB scripts. These scripts are necessary especially for the SSRS script and for the batch server update script (available soon) as these rely on a correct AOS configuration.
Note: Under normal circumstances a new configuration for your AOS will automatically be created if it does not exist when the AOS starts up after a restore, however all your other configs like batch jobs, SSRS settings etc won’t be pointing to it, but rather the old settings that are not cleared. Thus I prefer to fix this in my script.
In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> System -> Server Configuration
Depending on the configuration of your source system (e.g. Production) you may have multiple AOSs setup here and typically your destination may only have a single AOS configuration therefore your script would need to do the following:
1. Remove all but one of the AOS configurations
2. Update the server details of the remaining configuration
3. Enable the remaining server as the “batch” server.
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 |
--Reuse the parameter declared for the SSRS update script Declare @AOS varchar(30) = '[AOSID]' -- Must be in the format '01@SEVERNAME' -- Clear out all but one server config. delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG) -- Update the remaining one to point to your current AOS and enable it for batch processing update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1 where serverid != @AOS -- Optional if you want to see the "affected row count" after execution. |
Note: These changes assume everything is setup correctly on the SSAS server itself.
View Next – AX DB Restore Scripts #4 – Reconfigure batch jobs
Recently I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that may need to be updated when restoring from one environment to another, in particular a production DB to a development or QA environment.
Today I will be drilling into how to reset the SSAS (SQL Server analysis services) configurations via DB scripts.
In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> Business Intelligence -> Analysis Services -> Analysis Servers.
Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following:
1. Remove all but one of the SSAS configurations
2. Update the server details of the remaining configuration
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 9 |
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX' Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer) update BIAnalysisServer set SERVERNAME=@SSASSERVERNAME, DESCRIPTION=@SSASDESCRIPTION, ISDEFAULT = 1 WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows |
Note: These changes assume everything is setup correctly on the SSAS server itself.
View Next – AX DB Restore Scripts #3 – Configure AX servers and batch servers
Yesterday I started a series of posts on DB restore scripts for Dynamics AX by providing a list of data entities in your AX database that should be updated when restoring from one environment to another.
Today I will start drilling into the details of each of these items starting with SSRS (SQL Server reporting services) configurations.
In the Dynamics AX 2012 client these configurations are located under System Administration -> Setup -> Business Intelligence -> Reporting Services -> Report Servers.
Depending on the configuration of your source system (e.g. Production) you may have multiple servers setup here and typically your destination would only have a single server (Development) therefore your script would need to do the following
1. Remove all but one of the SSRS configurations
2. Update the remaining one to be default and update the relevant data fields.
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME' Declare @REPORTINSTANCE varchar(50) = 'AX' Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX' Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX' Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]' Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]' Declare @REPORTFOLDER varchar(20) = 'DynamicsAX' Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS'; delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS) update SRSSERVERS set SERVERID=@REPORTSERVERID, SERVERURL=@REPORTSERVERURL, AXAPTAREPORTFOLDER=@REPORTFOLDER, REPORTMANAGERURL=@REPORTMANAGERURL, SERVERINSTANCE=@REPORTINSTANCE, AOSID=@AOS, CONFIGURATIONID=@REPORTCONFIGURATIONID, DESCRIPTION=@REPORTDESCRIPTION where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution. |
Note: These changes assume everything is setup correctly on the SSRS side.
A common task in the administration of a Dynamics AX installation is restoring databases from a live application to a development environment or quality assurance (QA) environment. However when doing so one needs to be very careful to re-configure the database so that it behaves correctly in its new context. For example you would need to re-configure your SSRS servers to point to the correct DEV/QA SSRS instance. Many of these changes can be done via the front-end, but it is very useful to script these in SQL so that nothing is missed and alot of effort is saved.
The following post aims to list as many of the potential data items that need to be changed when doing a DB restore. I will in the following days be posting the relevant SQL alongside the front-end equivalent and ultimately a full DB restore script that you can use when doing such a restore. Some of these may need to be adapted for your specific configurations and you may need to add your own based on customization etc.
Let me say at the outset that I am very new to Dynamics AX role centers and analysis services and really have a lot to learn. However I thought I would share the following problem with setting up role centers and the associated solution that I found.
Problem description: After installing role centers on AX2012 RTM CU 3 with SQL Server 2012 the following error is displayed “An attempt has been made to use a data extension ‘AXADOMD’ that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound)”
The Event log on the SSRS machine says the following: Role Centers / Analysis Services: Report Server (AX) cannot load the AXADOMD extension.
Problem resolution / investigation: After chasing my tail on this one for quite a while (including multiple re-installation etc) trying to figure out why the “AXADOMD” SSRS datasource extension was not installed I discovered that the problem did not lie with “AXADOMD” being missing but rather it might be having issues when it was loading.
Looking at the SSRS log files (C:\Program Files\Microsoft SQL Server\MSRS11.AX\Reporting Services\LogFiles) I discovered the following message:
ERROR: Exception caught instantiating AXADOMD report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.AnalysisServices.AdomdClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.AnalysisServices.AdomdClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′
at Microsoft.Dynamics.Framework.Reports.AxAdomdConnection..ctor()
This lead me to think something might be wrong with the Microsoft.AnalysisServices.AdomdClient itself despite the AX installation validation showing up that it was installed already.
Paying a quick visit to C:\Windows\Assembly revealed that only AdomdClient version 11.0.0.0 was installed
The simple solution to this issue was to install the 10.0.0.0 client from http://www.microsoft.com/en-za/download/details.aspx?id=23089 after which SSRS no longer complained.
I hope this saves somebody some of the hassle and time that I have spent on figuring this one out.
Recently a question was asked on the Dynamics AX community forums about the reason for and the use of manual decisions in Workflow. (see original thread)
Basically manual decisions allow you as a implementation consultant at configuration time to define a question for a user to answer with two outcomes (answers) that can be used to change the logical process flow of the workflow.
Normally in my experience with workflow, processes tend to not rely on a user’s choice to define the flow, but rather on a pre-decided flow and set of rules that can be catered for by automatic decisions (conditional statements), assignment etc. However there may, from time to time, be choices that are simply too fuzzy or complex for system to handle and we can therefore delegate the decision to a human user to make. For example: We may want a purchase requisition to go through an RFQ cycle if a senior manager thinks that the amount “looks” too much, or that he thinks there “may be” preferential treatment of a selected vendor. There is no conditional statements in AX that can cater for these subjective scenarios. So we can simple ask a user using a manual decision.
So here is how we setup a manual decision.
Configuration
1. Create a new workflow configuration of your choice and fill in all the basic details. E.G. Purchase Requisition review.
2. Drag the “Manual decision” control from the “Flow controls” onto your workflow configuration.
3. Select the newly created Manual decision and click the properties Icon in ribbon bar.
4. Provide a name for the element so that it makes sense in your configuration.
5. In the “Workitem Subject” field pose a question to the user. E.G. Does this requisition require a Review / RFQ?
6. Provide a description with more details in the “Work item instructions”.
7. Select the “Outcomes” tab (under Basic settings) and enter the two answers for the user. E.G. “Review Document” and “Do not review”
8. Select the assignment tab and choose who to pose the question to. (beyond the scope of this blog)
9. Click close.
10. On your workflow diagram create the relevant elements such as requisition review and approval nodes (beyond the scope of this blog)
11. Connect up your Manual decision
11.1 Connect an incoming flow to your decision e.g. From the start element (or from any other workflow element.
11.2 Connect (from the left of your decision) the Outcome one node to the element that your want to process should the user select what you defined as Outcome one. E.G. In our example Outcome 1 was to review document, so we connect outcome 1 to the review element. Hint: Hover over the decision to see the handle to drag the process flow
11.3 Repeat 11.2 for outcome 2.
12. Save and close your workflow. (ensure you select the “activate new version” option) when prompted
Test your workflow.
1. Create your document and submit it into workflow.
2. The user you selected in step 8 above should (after workflow has processed for a minute or two) see the following screen with our question and two outputs.
3. The user can now select an option and workflow will be redirected as needed.
I hope this is helpful in understanding Manual decisions in Workflow in Dynamics AX.
Happy Daxing
Its not really my habit in my blog to re-post or re-blog other users content, but rather to write up my own findings as I explore Dynamics AX. However this post is a bit of a worthy exception.
If you have been in AX for an period of time you have most likely heard of Murray Fife and his very practical Companion guides to Dynamics AX. Right now you can download his “AN INTRODUCTION TO DYNAMICS AX 2012″ for free over here. It is highly useful material for anyone new to AX or even old hats looking for some new easy to use tips and tricks. I’ve been compiling some introductory basic training material for our company and this book will go along way in assisting me with this!
Thank you Murray Fife for this awesome content.
See his original post over here too.
Update: Unfortunately this was only a limited time offer, but I would still encourage you to get hold of this book either from Murray Fife’s website http://www.dynamicsaxcompanions.com/ or from amazon Here
Recently while debugging some legacy code I came across the following interesting observation regarding transaction scopes (ttsbegin, ttscommit) and the exception handling surrounding it. While walking the legacy code was observing very strange and unexpected behaviour and after some online investigation i found the following statement from Microsoft:
“When an exception is thrown inside a ttsBegin – ttsCommit transaction block, no catch statement inside that transaction block can process the exception. Instead, the innermost catch statements that are outside the transaction block are the first catch statements to be tested.”
(https://msdn.microsoft.com/en-us/library/aa893385.aspx)
In its simplest form this means that for the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
try { ttsBegin; try { throw error("Thrown Error"); } catch (Exception::Error) { error("Inside Error"); } ttsCommit; } catch (Exception::Error) { error("Outside Error"); } |
Prints the following results:
Now when it comes to a simple block of code like above one may say “Well that is simply silly coding”, however it becomes harder to anticipate results when that inner try…catch is within a method somewhere deeper inside the call stack (perhaps in standard code somewhere) e.g.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
void innerFunction() { try { ttsbegin; throw error("Thrown Error"); ttscommit; } catch (Exception::Error) { error("Inside Error"); } } try { ttsBegin; innerFunction(); ttsCommit; } catch (Exception::Error) { error("Outside Error"); } |
Further more if your inner exception does cleanups or logging of information this will not happen by carelessly adding ttsBegins and ttsCommits around the calling code E.G. “writeErrorLog” will never be called in the following function regardless of what the writer of “innerFunction” does if the writer of the caller adds TTSBEGIN AND COMMIT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
static void testTransactions(Args _args) { void innerFunction() { while forUpdate select myTable { try { ttsBegin; if (someCondition) { throw error("Throw Error"); } else { myTable.myField = "update"; myTable.update(); } ttsCommit; } catch (Exception::Error) { writeErrorLog(strFmt("Failed on record %1", myTable.RecId)); error("Inside Error"); } } } try { ttsBegin; innerFunction(); ttsCommit; } catch (Exception::Error) { error("Outside Error"); } } |
I thought I would just paste these observations for anyone who like me has experienced this type of “strange behavior” in the past and didn’t know the exact reason and also as a warning to beware of the “Catch”.
Feel free to share your comments, observations and thoughts.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 = dictF.id(); log.insert(); info(strFmt("Adding field %1", dictF.name())); } SysFlushDatabaselogSetup::main(); } |
Its always a great opportunity to interact with other AX developers and have the opportunity to learn from each other. A few weeks ago I published a blog post on searching the AX AOT in code for objects with specific properties, after the post went out I got a comment on the post of a much easier way to search the AOT from the frontend, that for some reason I have never really noticed before. So here is a quick way to do an advanced search of the AOT using a real situation I encountered this morning.
Example: Find all privileges in the AOT that have a specific menuitem as an entry point. e.g. “PurchFormLetter_PackingSlip”
1. Open an AOT and select the section of the AOT that you wish to search. Obviously the narrower the search the quicker it will be. I selected Security->Privileges.
2. Right click on the object. Click Find
3. Select