Thursday 26 November 2015

Workflow installation and configuration in AX 2009

Workflow prerequisites

Before we begin installing and setting up Workflow, you will need to have administrator privileges on the machines in which you are installing Workflow on. The following prerequisites are required:

Internet Information Services (IIS) 7
.NET Framework 2.0
Business Connector

The Workflow component for Dynamics AX utilizes the Business Connector to communicate directly to Dynamics AX from its web service. Although it depends on the number of users and computing resources available, it is best practice to implement the Workflow web service application on its own server.

Workflow accounts setup

The Workflow system in Dynamics AX utilizes two accounts to function properly. If these accounts are not specified, workflow will still function; however, it is best practice to have dedicated accounts. One account is the service account. This account is responsible for the communication between Dynamics AX and the Workflow web service. The other account is the execution account. This account is responsible for executing Workflow tasks and processes. Similar to the Business Connector proxy account, these two accounts must be created with the following criteria:

The password must never expire
It must not be interactive
It must not be associated to any Dynamics AX users

Once the Workflow accounts have been created, the next process is to ensure that the Dynamics AX Workflow system will use the accounts. To do this, perform the following steps:

Log in to Dynamics AX 2009.
Go to Administration | Setup | Security | System service accounts.
In the System service accounts form, specify the Workflow System Account and the Workflow Execution Account. To specify the accounts that were created in the Active Directory, mark the Alias field radio boxes. When complete, click on the OK button.

Now that the Workflow accounts have been specified, the Dynamics AX Workflow system can utilize these accounts when communicating with the Workflow web service. It is also possible to create a user within Dynamics AX but not Active Directory, and use the Dynamics AX user accounts as the Workflow Service and Execution accounts. These accounts may appear as different users in Dynamics AX; however, these accounts will be impersonated by the AOS service account to the Workflow web service. This can make troubleshooting and connection auditing more difficult and therefore, it is not recommended.

Installing Workflow

Since Workflow consists of various parts that function together to create the Dynamics AX 2009 Workflow system, we will break down each part’s setup and complete each setup individually.

Creating a website for Workflow

Before we can install the Workflow extended server component, we must have a website available to install upon. It is possible to use the default website that is on port 80. However, it is not recommended; therefore, a new website must be created. For information regarding how to create a website in IIS 7, refer to http://technet.microsoft.com/en-us/library/cc772350(WS.10).aspx.

Installing the Workflow component

By now you should be accustomed to the process of installing the extended components for Dynamics AX. Installing Workflow is no different. The following steps will guide you through the process:

Run the Microsoft Dynamics AX Setup wizard to add new components. In the Add or modify components screen of the wizard, mark the Workflow checkbox, as shown in the following screenshot and then click on the Next button:
In the following screen of the wizard, specify the password for the .NET Business Connector proxy account, and then click on the Next button:

The Domain\user name field will automatically be populated if the Business Connector proxy user is specified in the Administration | Setup | Security | System service accounts form in the Business Connector Proxy group.

In the next section of the wizard, you will be prompted to tweak the Workflow service. In other words, you can select which website you want to install the Workflow service into. By default, the wizard will select the default website in IIS. However, it is recommended to install Workflow on its own dedicated site and port, you have the flexibility to do so. When you are satisfied with the settings on this page, click on the Next button.
In the following screen you will be prompted to specify the AOS account for the Workflow service to grant permissions to. If there is more than one AOS and each AOS service account is different, provide the accounts for each AOS so that they can access the Workflow service.
In the following step, you are prompted to complete the installation of the Workflow by clicking on the Finish button. You will want to restart IIS after the Workflow has been successfully installed. Therefore, leave the option checked at the bottom, as shown in the following screenshot:
Once installed, you will be prompted with the final screen. The final screen will display the result of the installation of the Dynamics AX 2009 Workflow system. If the installation is successful, you will see a green box next to the installed component. Otherwise, if the box is orange or red, you should open the log file after you close the wizard by marking the checkbox at the bottom.

Enabling Workflow in Windows Server 2008 R2

In Windows Server 2008 R2, additional setup is required to enable the Workflow web service. The Workflow web service application pool must be enabled to run 32-bit applications. Otherwise, the Workflow service will fail. To set up the application pool to run 32-bit applications, perform the following steps:

Go to the IIS console and access the web server that you installed the Workflow web service on.
Under &ltYour web server&gt | Application Pools, select the Dynamics AX Workflow application pool.
Under Actions, go to Advanced Settings.... In the Advanced Settings window, set the Enable 32-Bit Applications property to True. When complete, click on the OK button to save the changes.

After performing these steps, the Workflow web service can then be used by Dynamics AX to process Workflows. Next, we will set up Dynamics AX to use the Workflow web service.

Setting up Workflow

Now that the Workflow service for Dynamics AX has been successfully installed, we can begin to configure it for use. Dynamics AX will not utilize the Workflow service after it has been installed. Therefore, in order to properly configure the Workflow system, we must do so within Dynamics AX 2009.

Workflow configuration prerequisites

Before we begin configuring the Workflow system, we need to ensure that the following is set:

A dedicated Workflow batch group has been created
An AOS is set up as a batch server

Creating a dedicated batch group is not required but recommended since, there is no batch group dedicated for Workflow. Since Workflow uses batch jobs to generate notifications and run Workflow processes, a batch server is required.

Creating a dedicated Workflow batch group

The following steps cover the process of verifying whether an AOS is a batch server and how to create a Workflow batch group:

To create a dedicated Workflow batch group, go to Administration | Setup | Batch groups.
Create two new records in the Batch group form—one group that will execute Workflow commands and another that will process Workflow due date notifications.

Setting up the AOS as a batch server

Since Dynamics AX workflow uses a batch job to send workflow tasks to the web service in order to be processed in Dynamics AX, we need to ensure that there is at least one AOS that is designated as a batch server. Without the batch job, the Workflow web service would sit idle, waiting for a workflow request even though a user may have initiated a workflow task in Dynamics AX. In this section, we will cover the process of setting up an AOS as a batch server and ensuring that a Workflow’s batch group will be associated to a designated batch server.

To view and modify the current batch server set up on an AOS or group of AOSs, go to Administration | Setup | Server Configuration.

An AOS can be set up as a batch server and service users. However, depending on the performance and resource requirements for an implementation, it is recommended to have an AOS as a dedicated batch server.

Select the appropriate AOS that should run as the batch server and then click on the General tab and ensure that the Is Batch Server checkbox is marked.
Go to the Batch server groups tab, and add the Workflow batch groups that we created.
To specify the number of batch threads/processes that can run simultaneously and the time the batch processes can run, go to the Batch server schedule tab and provide the desired values. The following batch schedule is the default schedule and runs eight threads at any time of the day:

Configuring Workflow

Now that we have an AOS that can run Workflows in a batch server, we must configure the batch processes. This section will guide you through the process of:

Configuring Dynamics AX to use the Workflow service
Specifying Workflow parameters for notifications and general use

Running the Workflow infrastructure configuration wizard

The Workflow infrastructure configuration wizard allows you to quickly specify the Workflow web service address, Workflow batch groups, and specify when the Workflow batch jobs should run. It also validates the Workflow web service and ensures that it is accessible by the AOS. In this section, we will cover the process of running the wizard.

Go to the Administration module and in the Setup section, open the Workflow infrastructure configuration wizard. Once in the Workflow infrastructure configuration wizard form, click on the Next button, as shown in the following screenshot:
In the next section of the wizard, you may specify the URL of the Workflow web service, which you created as described in Creating a website for Workflow section of this article. By default, this field will already be populated if you installed the Workflow service correctly. However, if you want to modify this field, you certainly have the option to do so. Be sure to click on the Validate button to confirm that the URL is accessible by Dynamics AX, as shown in the following screenshot:
In the following section of the wizard, you will be able to see which batch group is designated to execute Workflow batch jobs. Verify that the appropriate batch group is assigned and then click on the Next button.
In this section of the wizard, you can specify how many times you want the batch server to repeat the Workflow batch process. The lowest value you can enter is 1. The wizard will allow you to enter in 0; however, the batch system only accepts 1 as the lowest value. Once you have specified the appropriate value, click on the Next button.
The next and final screen of the wizard displays a summary of all the settings that were specified. Click on the Finish button to apply these new settings and to make Dynamics AX start using the Workflow service.

Specifying Workflow settings

The Workflow infrastructure configuration wizard assists in setting up common Workflow settings that are shared between companies. However, company specific settings for Workflow must also be specified such as Number sequences. Additional settings can be customized but are not required, such as specifying custom e-mail templates or another Workflow web service URL. In this section, we will cover the process of specifying company-specific workflow settings.

Go to Basic | Setup | Settings for workflow. In the General tab, provide a custom template for Approval and task notifications. By default, no template is specified. In this case, a generic e-mail template will be used.
In the Administration tab, verify that the correct Workflow web service is listed and verify that the system can access the service by clicking on the Validate button.
In the Number sequences tab, ensure that Number sequences are mapped to the appropriate Reference.

If number sequences are not mapped, it may be because the number sequence wizard never ran. To run the number sequence wizard, go to Basic | Setup | Number sequences to load the Number sequences form. In the Number sequences form, run the wizard by clicking on the Wizard button. Dynamics AX will automatically check which number sequences need to be set up and associate number sequences to their reference.

Workflow installation and configuration in AX 2009

Workflow prerequisites

Before we begin installing and setting up Workflow, you will need to have administrator privileges on the machines in which you are installing Workflow on. The following prerequisites are required:

Internet Information Services (IIS) 7
.NET Framework 2.0
Business Connector

Workflow accounts setup

The password must never expire
It must not be interactive
It must not be associated to any Dynamics AX users

Once the Workflow accounts have been created, the next process is to ensure that the Dynamics AX Workflow system will use the accounts. To do this, perform the following steps:

Log in to Dynamics AX 2009.
Go to Administration | Setup | Security | System service accounts.
In the System service accounts form, specify the Workflow System Account and the Workflow Execution Account. To specify the accounts that were created in the Active Directory, mark the Alias field radio boxes. When complete, click on the OK button.

Installing Workflow

Since Workflow consists of various parts that function together to create the Dynamics AX 2009 Workflow system, we will break down each part’s setup and complete each setup individually.

Creating a website for Workflow

Installing the Workflow component

By now you should be accustomed to the process of installing the extended components for Dynamics AX. Installing Workflow is no different. The following steps will guide you through the process:

Run the Microsoft Dynamics AX Setup wizard to add new components. In the Add or modify components screen of the wizard, mark the Workflow checkbox, as shown in the following screenshot and then click on the Next button:
In the following screen of the wizard, specify the password for the .NET Business Connector proxy account, and then click on the Next button:

In the next section of the wizard, you will be prompted to tweak the Workflow service. In other words, you can select which website you want to install the Workflow service into. By default, the wizard will select the default website in IIS. However, it is recommended to install Workflow on its own dedicated site and port, you have the flexibility to do so. When you are satisfied with the settings on this page, click on the Next button.
In the following screen you will be prompted to specify the AOS account for the Workflow service to grant permissions to. If there is more than one AOS and each AOS service account is different, provide the accounts for each AOS so that they can access the Workflow service.
In the following step, you are prompted to complete the installation of the Workflow by clicking on the Finish button. You will want to restart IIS after the Workflow has been successfully installed. Therefore, leave the option checked at the bottom, as shown in the following screenshot:
Once installed, you will be prompted with the final screen. The final screen will display the result of the installation of the Dynamics AX 2009 Workflow system. If the installation is successful, you will see a green box next to the installed component. Otherwise, if the box is orange or red, you should open the log file after you close the wizard by marking the checkbox at the bottom.

Enabling Workflow in Windows Server 2008 R2

Go to the IIS console and access the web server that you installed the Workflow web service on.
Under &ltYour web server&gt | Application Pools, select the Dynamics AX Workflow application pool.
Under Actions, go to Advanced Settings.... In the Advanced Settings window, set the Enable 32-Bit Applications property to True. When complete, click on the OK button to save the changes.

After performing these steps, the Workflow web service can then be used by Dynamics AX to process Workflows. Next, we will set up Dynamics AX to use the Workflow web service.

Setting up Workflow

Workflow configuration prerequisites

Before we begin configuring the Workflow system, we need to ensure that the following is set:

A dedicated Workflow batch group has been created
An AOS is set up as a batch server

Creating a dedicated Workflow batch group

The following steps cover the process of verifying whether an AOS is a batch server and how to create a Workflow batch group:

To create a dedicated Workflow batch group, go to Administration | Setup | Batch groups.
Create two new records in the Batch group form—one group that will execute Workflow commands and another that will process Workflow due date notifications.

Setting up the AOS as a batch server

To view and modify the current batch server set up on an AOS or group of AOSs, go to Administration | Setup | Server Configuration.

Select the appropriate AOS that should run as the batch server and then click on the General tab and ensure that the Is Batch Server checkbox is marked.
Go to the Batch server groups tab, and add the Workflow batch groups that we created.
To specify the number of batch threads/processes that can run simultaneously and the time the batch processes can run, go to the Batch server schedule tab and provide the desired values. The following batch schedule is the default schedule and runs eight threads at any time of the day:

Configuring Workflow

Now that we have an AOS that can run Workflows in a batch server, we must configure the batch processes. This section will guide you through the process of:

Configuring Dynamics AX to use the Workflow service
Specifying Workflow parameters for notifications and general use

Running the Workflow infrastructure configuration wizard

Go to the Administration module and in the Setup section, open the Workflow infrastructure configuration wizard. Once in the Workflow infrastructure configuration wizard form, click on the Next button, as shown in the following screenshot:
In the next section of the wizard, you may specify the URL of the Workflow web service, which you created as described in Creating a website for Workflow section of this article. By default, this field will already be populated if you installed the Workflow service correctly. However, if you want to modify this field, you certainly have the option to do so. Be sure to click on the Validate button to confirm that the URL is accessible by Dynamics AX, as shown in the following screenshot:
In the following section of the wizard, you will be able to see which batch group is designated to execute Workflow batch jobs. Verify that the appropriate batch group is assigned and then click on the Next button.
In this section of the wizard, you can specify how many times you want the batch server to repeat the Workflow batch process. The lowest value you can enter is 1. The wizard will allow you to enter in 0; however, the batch system only accepts 1 as the lowest value. Once you have specified the appropriate value, click on the Next button.
The next and final screen of the wizard displays a summary of all the settings that were specified. Click on the Finish button to apply these new settings and to make Dynamics AX start using the Workflow service.

Specifying Workflow settings

Go to Basic | Setup | Settings for workflow. In the General tab, provide a custom template for Approval and task notifications. By default, no template is specified. In this case, a generic e-mail template will be used.
In the Administration tab, verify that the correct Workflow web service is listed and verify that the system can access the service by clicking on the Validate button.
In the Number sequences tab, ensure that Number sequences are mapped to the appropriate Reference.

Customer payment journal through X++ Code - AX 2012

static void cust_GLPosting(Args _args)
{
Ledgerjournalname ledgerjournalname;
LedgerjournalTable LedgerjournalTable;
LedgerjournalTrans LedgerjournalTrans;
LedgerjournalCheckPost LedgerjournalCheckPost;
NumberSeq numberSeq;
Container con;
Filename filename, Filename2;
FileIOPermission permission;
TextIO textIO, textIO1;
Dialog dialog;
DialogField dialogField;
AccountNum customerAccount= 'BRMF-000001';
AccountNum offsetAccNum = 'B01';
#File

select ledgerjournalname where ledgerjournalname.JournalName == "Test";
ttsBegin;
LedgerjournalTable.JournalName = ledgerjournalname.JournalName;
LedgerjournalTable.initFromLedgerJournalName();
LedgerjournalTable.JournalNum = JournalTableData::newTable(LedgerjournalTable).nextJournalId();
LedgerjournalTable.insert();
ttsCommit;

ttsBegin;
numberSeq = NumberSeq::newGetVoucherFromId((ledgerjournalname.NumberSequenceTable));
LedgerjournalTrans.Voucher = numberSeq.voucher();
LedgerjournalTrans.JournalNum = LedgerjournalTable.JournalNum;
LedgerjournalTrans.CurrencyCode = "USD";
LedgerjournalTrans.ExchRate = Currency::exchRate(LedgerjournalTrans.CurrencyCode);
LedgerjournalTrans.AccountType = LedgerJournalACType::Cust;
LedgerjournalTrans.parmAccount(customerAccount,LedgerjournalTrans.AccountType);
LedgerjournalTrans.Txt = 'Testing';//conPeek(con,1);
LedgerjournalTrans.AmountCurCredit = 200;//conPeek(con,3);
LedgerjournalTrans.TransDate = systemDateGet();//str2Date(conPeek(con,4),123);
LedgerjournalTrans.OffsetAccountType = LedgerJournalACType::Bank;//LedgerjournalTable.OffsetAccountType;
//LedgerJournalTrans.offsetacco
LedgerjournalTrans.OffsetLedgerDimension = 22565431567; //LedgerjournalTable.OffsetLedgerDimension;
LedgerjournalTrans.DefaultDimension = CustTable::find(customerAccount).DefaultDimension;
LedgerjournalTrans.OffsetDefaultDimension= CustTable::find(customerAccount).DefaultDimension;
LedgerjournalTrans.insert();
ttsCommit;

LedgerjournalCheckPost = LedgerjournalCheckPost::newLedgerJournalTable(LedgerjournalTable,NoYes::Yes);
LedgerjournalCheckPost.run();

}

Batch Jobs debug in AX 2012

Debugging in Microsoft Dynamics AX 2012

http://msdn.microsoft.com/en-in/library/gg860898.aspx

Debugging Batch Jobs in Microsoft Dynamics AX 2012

http://msdn.microsoft.com/en-in/library/gg860898.aspx#BatchJobs

Demonstration of debugging batch jobs in AX 2012.

http://daxmusings.codecrib.com/2011/08/x-in-net-clr-life-in-fast-lane.html

Customer payment journal through X++ Code - AX 2012

static void cust_GLPosting(Args _args)
{
Ledgerjournalname ledgerjournalname;
LedgerjournalTable LedgerjournalTable;
LedgerjournalTrans LedgerjournalTrans;
LedgerjournalCheckPost LedgerjournalCheckPost;
NumberSeq numberSeq;
Container con;
Filename filename, Filename2;
FileIOPermission permission;
TextIO textIO, textIO1;
Dialog dialog;
DialogField dialogField;
AccountNum customerAccount= 'BRMF-000001';
AccountNum offsetAccNum = 'B01';
#File

select ledgerjournalname where ledgerjournalname.JournalName == "Test";
ttsBegin;
LedgerjournalTable.JournalName = ledgerjournalname.JournalName;
LedgerjournalTable.initFromLedgerJournalName();
LedgerjournalTable.JournalNum = JournalTableData::newTable(LedgerjournalTable).nextJournalId();
LedgerjournalTable.insert();
ttsCommit;

ttsBegin;
numberSeq = NumberSeq::newGetVoucherFromId((ledgerjournalname.NumberSequenceTable));
LedgerjournalTrans.Voucher = numberSeq.voucher();
LedgerjournalTrans.JournalNum = LedgerjournalTable.JournalNum;
LedgerjournalTrans.CurrencyCode = "USD";
LedgerjournalTrans.ExchRate = Currency::exchRate(LedgerjournalTrans.CurrencyCode);
LedgerjournalTrans.AccountType = LedgerJournalACType::Cust;
LedgerjournalTrans.parmAccount(customerAccount,LedgerjournalTrans.AccountType);
LedgerjournalTrans.Txt = 'Testing';//conPeek(con,1);
LedgerjournalTrans.AmountCurCredit = 200;//conPeek(con,3);
LedgerjournalTrans.TransDate = systemDateGet();//str2Date(conPeek(con,4),123);
LedgerjournalTrans.OffsetAccountType = LedgerJournalACType::Bank;//LedgerjournalTable.OffsetAccountType;
//LedgerJournalTrans.offsetacco
LedgerjournalTrans.OffsetLedgerDimension = 22565431567; //LedgerjournalTable.OffsetLedgerDimension;
LedgerjournalTrans.DefaultDimension = CustTable::find(customerAccount).DefaultDimension;
LedgerjournalTrans.OffsetDefaultDimension= CustTable::find(customerAccount).DefaultDimension;
LedgerjournalTrans.insert();
ttsCommit;

LedgerjournalCheckPost = LedgerjournalCheckPost::newLedgerJournalTable(LedgerjournalTable,NoYes::Yes);
LedgerjournalCheckPost.run();

}

AX DB Restore Scripts - Moving AX DB from one server to another server

Following SQL script is very useful while moving AX DB from one server to other server. Thanks to http://www.exploreax.com.

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

reference : http://www.exploreax.com/blog/blog/2015/10/13/ax-db-restore-scripts-full-script/

How to show or hide query ranges on dialogs in Dynamics ax

Sometimes, we create dialogs which have a query associated with them. We use this query to filter records and show us some data. Often, associating such queries with dialogs adds ranges to the dialogs. But do we really need these ranges always? In this post, let us look at one of the reason why ranges may get added and how to hide them.

Let us create a simple Query as shown in the figure below. Nothing fancy about it. Just SalesTable and SalesLine joined.

Now we will create a dialog, set the dialog's query with the query we created above and run it.

When the dialog opens, this is how it looks.

Notice now that there are two SalesId ranges. These come from SalesTable and SalesLine respectively.

The logic to add the range here is that the first active index on the tables are displayed. Take a look at both SalesTable and SalesLine.

On SalesTable, the first index is SalesIdx and on SalesLine, the first index is SalesLineIdx.

Before we move further, some of you may question that SalesLineIdx has threee fields - SalesId, LineNum & RecId. So why is only SalesId shown as a range. Well, the answer is that for all child datasources, only the first range is shown.

Now back to the topic, I want to hide the SalesLine's SalesId range on the dialog. How can I do that?

The answer is to override the showIndexFields method on the dialog and return false. Let us try that.

public boolean showIndexFields(tableId id) 
{ 
    boolean ret; 
  
    ret = true; 
  
    return ret; 
}

Well, the fields have been hidden but the labels are still showing. This is a bug in the RunBaseDialogModify class which hasn't been fixed yet even in the latest roll up. If you are interested in fixing this, you will have to move the code on lines 26 & 27 in \Classes\RunBaseDialogModify\addQueryDatasource() inside the condition on line 75.

Coming back to our original requirement, I just want to hide the range for the SalesLine table. If you look closely at the showIndexFields() method's signature, it passes a tableId value. We can use this value to decide which tables to show the ranges for and which to disallow.

public boolean showIndexFields(tableId id) 
{ 
    boolean ret = true; 
      
    switch(id) 
    { 
        case tableNum(SalesLine): 
            ret = false; 
            break; 
    } 
  
    return ret; 
}

As you can see from the below image now, only the range on SalesTable is visible now. The SalesLine range's label is still visible which is due to the bug in code I mentioned above.

In this post, we learnt how we can hide the ranges of certain tables by overriding the showIndexFields() methods.