Blog

Name is Anant Dubey and the intent to create this blog is to discuss the problems and issues that developer face in the dynamics AX development and to share the new things that come up with the new version of AX.

Monday, May 4, 2015

How to check Permission Access of AX file in ax 2012

Super Tips for Testing Dynamics AX File Permission Access

Batch processes which use file input are very common designs in Dynamics AX, but there are often assumptions made by the AX developer as to their ability to read/write/execute files at the time they will be processed. Reading a file for input to AX is generally pretty simple, but a different permission set may be needed when AX needs to write results out to a file in a batch process. Further complicating the issue is that often these batch processes are tested with a manual run (probably processing code on the AX client) then executed in production using the automated batch process (which runs on the AOS server). If care isn't taken to ensure that your export code can execute on the server (which is very common), you may encounter a situation where the test export runs fine, but the production batch fails writing the file.
We have a periodic routine which reads table data in AX and exports a flat text file to a file share for use by an Electronic Data Interchange (EDI) partner. Our custom class which collects the data and exports to the file funs on the client by default. In the custom class we can develop around the usual file handling problems using InteropPermissions, but when we run the export on the batch in production AX, the job failed writing the output file. The problem isn't so much that we don't know how to handle this scenario in code, but that our users may want a tool in AX to show them whether or not a batchable job will have access to write and output file prior to tossing it onto the batch and hoping for the best.
Our solution here will show how to borrow some code from the Application Integration Framework to test file IO permissions from both the client and server contexts given an output file path. With this tool we should be able to test file permissions before scheduling a batch job, giving us a high degree of certainty that our batch process will finish successfully.
We found some very relevant code in the AifFileSystemAdapter class, validateTransportAddress method which provides a sort of template for how we can test file path permissions.
The resulting code looks like this:
client server static void validateExportfilePermissions(FilePath _filePath,
                                                        NoYesId  _checkRead,
                                                        NoYesId  _checkWrite,
                                                        NoYesId  _checkDelete)
{
     int requiredPermissions;
     int directoryPermissions;
     str errorMessage;
     Microsoft.Dynamics.IntegrationFramework.Adapter.FileSystem fileSystem;

     #AIF
     ;

     if (_checkRead)
     {
          requiredPermissions = AifDirectoryPermission::ReadFile;
     }
     if (_checkWrite)
     {
          requiredPermissions = requiredPermissions | AifDirectoryPermission::WriteFile;
     }
     if (_checkDelete)
     {
          requiredPermissions = requiredPermissions | AifDirectoryPermission::DeleteFile;
     }

     errorMessage = "@SYS95130";

     new InteropPermission(InteropKind::ClrInterop).assert();
     fileSystem = AifUtil::getClrObject(#FileSystemProgId);
     CodeAccessPermission::revertAssert();

     // Check that path exists
     new InteropPermission(InteropKind::ClrInterop).assert();
     if (!fileSystem.DoesDirectoryExist(_filePath))
          throw error(strfmt("@SYS95128", _filePath));
     CodeAccessPermission::revertAssert();

     // Check required directory permissions
     new InteropPermission(InteropKind::ClrInterop).assert();
     directoryPermissions = fileSystem.VerifyDirectoryPermissions(_filePath, requiredPermissions);
     CodeAccessPermission::revertAssert();

     if (requiredPermissions != directoryPermissions)
     {
          throw error(strfmt(errorMessage, _filePath));
     }
}
Effectively all we're really doing is using a .Net file system object to test directory permissions. If the directory doesn't exist, or the required permissions aren't found, errors will be thrown. For that reason our main method uses try..catch statements when checking from the client and server contexts. Our server test method is a simple wrapper for validateExportfilepermissions ensuring the code runs on the server:
server static void serverTest(FilePath _filePath,
                              NoYesId  _checkRead,
                              NoYesId  _checkWrite,
                              NoYesId  _checkDelete)
{
     ;

     info("Server test start...");
     info(strFmt(" File path: %1", _filePath));
     info(strFmt(" User: %1", WinAPIServer::getUserName()));
     FileIOPermsTest::validateExportfilePermissions(_filePath, _checkRead, _checkWrite, _checkDelete);
     info("Server test passed");
}
Similarly, our client code test simply ensures the validation method runs on the AX client:
client static void clientTest(FilePath _filePath,
                              NoYesId  _checkRead,
                              noYesId  _checkWrite,
                              NoYesId  _checkDelete)
{
     ;

     info("Client test start...");
     info(strFmt(" File path: %1", _filePath));
     info(strFmt(" User: %1", WINAPI::getUserName()));
     FileIOPermsTest::validateExportfilePermissions(_filePath, _checkRead, _checkWrite, _checkDelete);
     info("Client test passed");
}
You'll notice when running this code that the server test will run as the user login for the windows service administrating the Dynamics AOS. When running the client test, we'll see that the file path is validated against the current user's permissions. One caveat we haven't mentioned is that AX tries at every turn to guide you toward using UNC file paths whenever possible. This is due to the impossibility of server-side code to correctly choose drive letters at run-time (so always use a UNC path and never drive letters in file path dialogs).
Using this method we have been able to provide our users with a predictable tool for testing file output permissions on batchable jobs. We hope this approach is helpful in troubleshooting and proactively determining a successful batch run.
- See more at: http://axmentor.com/learninglab/blog/development/super-tips-for-testing-dynamics-ax-file-permission-access/#sthash.H0hqivN5.dpuf

The report execution #### has expired or cannot be found in AX 2012

How to Fix SSRS Timeouts in AX 2012 the Right Way

Reporting in AX 2012 has been updated from previous versions of AX. Where we used to use MorphX reports in AX 2009 and earlier versions, the entire AX 2012 reporting solution has been migrated to SQL Server Reporting Services. Reporting timeouts can cause lots of frustration for users and impact critical business processes such as month-end, year-end, etc.
In this blog post we will discuss the causes of timeouts in AX 2012 SQL Server Reporting Services (SSRS) reports. The intended audience of this blog post are Dynamics AX developers and other technical professionals. If you are seeing errors like this, read on to discover some common ways to fix reporting timeout issues in AX 2012!
Microsoft Dynamics AX 2012 Timeout Error
We have separated this blog post into two main sections:
  • Timeout Troubleshooting
  • Timeout Settings

Timeout Troubleshooting

SSRS report timeouts in AX 2012 are often caused by inefficient code/query design in AX. To identify where the bottleneck is, we need to debug and explore the SSRS logs.

Debugging

Before we get started, you need to make sure you have the right components to debug.
To debug Report Data Provider (RDP) classes, you must be on the Application Object Server (AOS) machine and that same machine must have the SSRS instance installed. Also the AOS must be setup for debugging.
Microsoft Dynamics AX Server Configuration Utility
SSRS reports in AX can get data from one (or more) places:
  • AX query
  • AX Report Data Provider
  • An outside data source
Debugging a Query can be tough, as it simply pulls data from the AX database from an AX query object in the Application Object Tree. See the section below on query optimization for techniques on improving query performance.
To debug a report based on an RDP, set a breakpoint in that report’s processReport method.
Microsoft Dynamics AX RDP
Observe the behavior of the RDP class using the same parameters as your report users to see where your bottlenecks are.

SSRS Logs

You can view the logs on the SSRS server to get more details on a timed out report. The logs can be found on the SSRS Server at \Program Files\Microsoft SQL Server\MSRS10.<Instance Name>\Reporting Services\LogFiles.
Reviewing SSRS logs will provide more information than AX infolog dialogs in many cases.
Microsoft Dynamics AX SSRS Logs
If the log doesn’t provide enough information, you can increase the DefaultTraceSwitch setting in the ReportingServicesService.exe.config file. The file can be found at \Program Files\Microsoft SQL Server\MSRS10.<Instance Name>\Reporting Services\ReportServer\bin.
Microsoft Dynamics AX eportingservicesservice

Timeout Solutions

Once we have identified the error, sometimes we need a little help planning our attack on improving report runtimes. We have provided some common solutions to long running reports here.
Note that these code improvements will only have effect on large data sets. Make sure you know the source of your timeouts before taking the time to redesign code.

Code Review

We all make mistakes. Perhaps the developer who wrote the RDP class made a mistake or the data processing requirements of the report have changed.

Code Review: Query

If a query takes a long time to run, see if you can make it run more efficiently.
Common areas of improvement are:
  • Inner joins vs. exist joins
  • Dynamic field property set to Yes
If the business requirement is to see if a record exists, we can accomplish this through an inner join. However, when we do this we may be pulling unnecessary amounts of data into AX.
For example, the business requirement in the query below is to give all purchase order lines where the vendor’s vendor group has a payment term of 30 days. The join to the VendGroup table is only to filter the results, no VendGroup fields are required in the report.
microsoft-dynamics-ax-vendgroup-1
This query is making two mistakes; the query does not need to perform an inner join and it doesn’t need to pull all the fields for the VendGroup table.
Microsoft Dynamics AX VendGroup 2
Here we have set the JoinMode property of the VendGroup node to ‘ExistsJoin’ and removed all of the unnecessary fields from the VendGroup node.
Now when the query runs, it will run faster due to the reduced amount of data requested from the AX database.

Code Review: RDP Class

If an RDP class is written in an inefficient way, it can greatly impact report running times. Here we will provide an example of an RDP class that can be refactored to improve report performance.
For example, say we have a very simple business requirement to pull the number of invoices that a given vendor has had in a report.
In the code segment below, an extra SQL call is made for each vendor in the report.
Microsoft Dynamics AX RDP Class 1
We can optimize this report by having all the invoice counts done during the initial report query.
In the resulting RDP class, we only make one call to SQL to get the total number of invoices for each vendor. This reduces network traffic, causes the report to run more quickly, and reduces the number of calls to the SQL server during report execution.
Microsoft Dynamics AX RDP Class 2

Special Case: AX Kernel Code

This scenario is a *last resort* for troubleshooting. Once you have exhausted query/RDP re-design as well as AX/SSRS settings you will most likely need to submit a support request with Microsoft. You can contact your Partner to do this and they will contact Microsoft on your behalf to resolve the issue.
To give an example of this type of scenario, myself and my team had a customer that was printing check runs of 1,000+ checks. The customer was running AX 2012 R2 CU 7. This is not terribly uncommon and AX should be able to handle these types of reporting scenarios. To add to the complexity, the ChequeDP RDP class and SSRS report (along with accompanying C# code) was heavily customized by a previous Partner and we had no documentation. To resolve the issue we tried the following:
  • We re-designed the ChequeDP class to extend the SrsReportDataProviderPreProcess
  • We increased timeouts in the Reporting Services AXC file as well as an isolated AX Client AXC file
  • We increased timeouts in the Production instance of SSRS
  • We increased timeouts in the Ax32Serv.exe.config file for the AOS handling reporting requests
Even after increasing all available timeouts to over an hour, the report was timing out against the SSRS instance after approximately an hour. Nothing seemed to work and we reached out to Microsoft. After troubleshooting it turned out to be an AX Kernel issue. As a Partner we do not have visibility into the AX Kernel code. Microsoft provided a (at the time unpublished) hotfix. We are providing the KB number and title here for reference purposes. If you are seeing timeouts after implementing the suggestions in this blog post and are running AX 2012 R2 CU7 (we cannot attest to other versions of AX 2012) you may want to reach out to Microsoft to resolve this issue and/or log into your Life Cycle Services account to download the latest hotfix for this issue.

Timeout Settings

Now that we’ve explored timeout troubleshooting, let’s take a look at the settings that can affect report timeouts.

Be careful of “Just increase the timeouts!”

Before we start, when running into timeouts in SSRS a common answer is, “Just increase the timeouts!” This kind of thinking is commonly a band-aid for poor report design. If you must increase timeouts as a troubleshooting measure, remember to set them back to previous levels after finding out why your reports are timing out! Long timeouts can cause the [ReportServer]TempDb database on the SSRS database server to grow very large when many users have open sessions (among other challenges).

SSRS Settings

Below are some common SSRS settings you can extend as your troubleshoot long running reports.

Report Execution Timeout: Site Level

To extend this timeout, open the SSRS Home Page and navigate to the Site Settings page (top right).
The default timeout is 30 minutes (1800 seconds).
SQL Server Reporting Services Site Settings

Report Execution Timeout: Report Level

By default, all reports use the Site level Report Timeout value. However, you can change the Report Timeout setting on a per-report basis.
SQL Server Reporting Services Dynamics AX
SQL Server Reporting Services Site Settings Report Timeout

SessionTimeout & SessionAccessTimeout

The SQL Server instance has two settings that may cause timeouts if the user session used to render SSRS reports takes too long.
The SessionTimeout and SessionAccessTimeout settings are in seconds.
By default, the SessionAccessTimeout setting is not visible in the SSRS settings. You can view them in SQL Server Management Studio by connecting to the SSRS instance and viewing the Properties of the instance.
SQL Server Management Studio Settings
SQL Server Management Studio Settings 2
To change the SessionAccessTimeout setting for the first time, you must run a script against rs.exe. This executable is typically located in \Program Files\Microsoft SQL Server0\Tools\Binn (SQL 2008) or \Program Files (x86)\Microsoft SQL Server0\Tools\Binn (SQL 2012).
Create a text file the following and save it with the .rss extension.
Public Sub Main()
Dim props() as [Property]
props = new [Property] () { new [Property](), new [Property]() }

props(0).Name = "SessionTimeout"
props(0).Value = timeout

props(1).Name = "SessionAccessTimeout"
props(1).Value = timeout

rs.SetSystemProperties(props)
End Sub
Now you can run the following command against rs.exe to update the timeout settings.
"C:\Program Files (x86)\Microsoft SQL Server0\Tools\Binn\rs.exe" -i c:\temp\extendTimeouts.rss -s http://localhost/reportserver -v timeout="6000"
This example will set both user session timeouts to approximately an hour and a half.
Microsoft Dynamics AX CMD Timeout
For future changes to these timeouts, you can change them from the SQL Server Management Studio.
Microsoft Dynamics AX SQL Server Management Studio

AX Settings

Since SSRS uses the AX Query Service, we may run into timeout issues due to these settings.

AX Settings: Server-side

We can make changes to the WCF thresholds in the AX32Serv.config. This file is typically located at \Program Files\Microsoft Dynamics AX\Server\MicrosoftDynamicsAX\bin on the AOS server.
Since the Query Service sends data to SSRS when requested, we can increase the sendTimeout attribute of the binding element with the QueryServiceBinding name attribute to handle requests that take longer than the default 10 minutes.
Microsoft Dynamics AX Settings Server Side

AX Settings: SSRS Host

The AX Reporting Extensions use the Business Connector settings for the AX Client installed on the SSRS host computer. Alternatively, SSRS can use an AXC file placed in \ReportServer\bin directory of the SSRS instance. View Create a new Microsoft Dynamics AX configuration [AX 2012] if you have this sort of configuration as you will need to edit that AXC file instead of the Business Connector configuration. These instructions are for a Business Connector configuration that is saved in the registry.
On the SSRS host server, open the Dynamics AX 2012 Configuration Utility as an Administrator (otherwise your changes may not be saved due to User Account Control) and navigate to the Business Connector configuration.
Microsoft Dynamics AX Configuration Utility 1
If there are no configurations other than the Original (installed configuration) create a new one.
Microsoft Dynamics AX Configuration Utility 2
Open Configure Services and acknowledge your settings will overwrite the default WCF settings.
Microsoft Dynamics AX Configuration Utility 3
If you see this message, you can download and install the Windows SDK for your version of Windows Server and the SDK for .NET Framework 4.0. But this is not required.
Microsoft Dynamics AX Configuration Utility 4
If you do not have the Windows SDKs installed, you will be presented with a text file. Search for QueryServiceEndpoint and increase the sendTimeout and maxReceivedMessageSize attributes of the binding element with the QueryServiceEndpoint name attribute.
When troubleshooting, we typically increase sendTimeout to 00:20:00 (20 minutes) and maxReceivedMessageSize to 4294967294 (twice as big).
Microsoft Dynamics AX Text File sendtimeout

Querying date effective tables in Microsoft Dynamics AX 2012 R3

Querying date effective tables in Microsoft Dynamics AX 2012

In Microsoft Dynamics AX 2012, valid time state tables (date effective tables) were introduced to help organizations keep track of data that changes over time. For example, currency exchange rates change over time and it is necessary to view exchange rate data at a specific point in time.
For this blog, the exchange rate table will be used as an example.
This blog discusses how developers can query data in date effective tables and examines the results when using forms, queries, views and X++ code. If not properly understood by the developer, incorrect or incomplete data will be returned when querying the database.
Exchange rate table
The exchange rate table holds exchange rate data between two (2) currencies. The standard Microsoft Dynamics AX form can show current, past and future the exchange rate data as show below.
Creating custom forms
A custom screen was created to display the exchange rate table. The exchange rate table was used as a data source and a grid was added to display the data.
The data that is returned only shows data that is current based on a current date of 1/2/2015. Prior and future exchange rates are excluded.
The ExchangeRate datasource property ‘ValidTimeStateAutoQuery’ is used to control what data is returned.
Change this to ‘DateRange’ and all records will be returned.
Querying the data using a query
A custom query was created to query the ExchangeRate table.
The following code is used to run this query:
static void AXMExchangeRateQuery(Args _args)
{
    QueryRun        queryRun;
    ExchangeRate    ExchangeRate;
    ;
    query = new query(queryStr(AXMExchangeRateQuery));
    
    queryRun = new QueryRun(query);
    
    while (queryRun.next())
    {
        ExchangeRate = queryRun.get(tableNum(ExchangeRate));  
        info(strFmt("%1 %1", ExchangeRate.ValidFrom, ExchangeRate));
    }
}
Running the query returns only current records.
It is possible to change the behavior of the query results set to return data at a previous point in time, for example prior exchange rates.
The following code can be used to query data for different date ranges:
static void AXMExchangeRateQuery(Args _args)
{
    QueryRun        queryRun;
    ExchangeRate    ExchangeRate;
    FromDate        FromDate = mkDate(1,1,2014);
    ToDate          ToDate   = mkDate(1,6,2015);
    ;
    query = new query(queryStr(AXMExchangeRateQuery));
    query.validTimeStateDateRange(FromDate, ToDate);
    
    queryRun = new QueryRun(query);

    
    while (queryRun.next())
    {
        ExchangeRate = queryRun.get(tableNum(ExchangeRate));  
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}
Running the query returns the following data.
Use the validateTimeStateASOfDate method to return data that is valid at a specific date. For example, if you want to know what the exchange rate should be for a transaction with a date of 3/7/2014, run the following code:
static void AXMExchangeRateQuery(Args _args)
{
    QueryRun        queryRun;
    ExchangeRate    ExchangeRate;
    TransDate       TransDate = mkDate(3,7,2014);
    ;
    query = new query(queryStr(AXMExchangeRateQuery));    
    query.validTimeStateAsOfDate(TransDate);
    
    queryRun = new QueryRun(query);

    
    while (queryRun.next())
    {
        ExchangeRate = queryRun.get(tableNum(ExchangeRate));  
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}
The following data is returned.
Querying the data using x++
The following x++ code is used to access the data:
static void AXMExchangeRateAccess(Args _args)
{
    ExchangeRate ExchangeRate;
    ;
    while select ExchangeRate
    {
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}
The following data is returned, again only current data is returned with a current date of 1/2/2015. It is important to note this as not all records are returned from the databse, despite no filtering applied in the X++ while select statement.
It is possible to return data as of a specific point in time using the validTimeState keyword.
The following code can be used to find data that is valid for a given data range:
static void AXMExchangeRateAccess(Args _args)
{
    ExchangeRate    ExchangeRate;
    FromDate        fromDate = mkDate(1,1,2014);
    ToDate          toDate = mkDate(1,1,2016);
    ;
    while select validTimeState(fromDate, toDate) ExchangeRate
    {
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}
The following data is returned. By changing the from and to dates, we can control the amount of data being returned.
If you want to know what the exchange rate should be for a transaction with a data of 3/7/2014, run the following code:
static void AXMExchangeRateAccess(Args _args)
{
    ExchangeRate    ExchangeRate;
    TransDate       TransDate = mkDate(3,7,2014);
    
    while select validTimeState(TransDate) ExchangeRate
    {
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}
The following data is returned.
Creating a view
Microsoft Dynamics AX Views can be created in the AOT to return data from data effective tables. This can be used to overcome issues when you need to return all data from a date effective table and cannot use the query object or X++ parameters to control the date effective options.
The following view was created in the AOT.
Using a view in a form data source
A new form was created and the view above was used as datasource.
The following records are shown, the date effective filters do not apply.
Using a view in a query
A custom query was created to query the ExchangeRate table using a view.
The following code is used to run this query:
static void AXMExchangeRateViewQuery(Args _args)
{
    queryRun                queryRun;
    AXMExchangeRateView     AXMExchangeRateView;
    ;
    queryRun = new queryRun(queryStr(AXMExchangeRateViewQuery));
    while (queryRun.next())
    {
        AXMExchangeRateView = queryRun.get(tableNum(AXMExchangeRateView));   
        
        info(strFmt("%1 %2", AXMExchangeRateView.ValidFrom, AXMExchangeRateView.ExchangeRate));
    }
}
Running the query returns all records, the date effective filters do not apply.
Using a view in a x++ while select
The following X++ code is used is used to access data in the view:
static void AXMExchangeRateViewAccess(Args _args)
{
    AXMExchangeRateView AXMExchangeRateView;
    ;
    while select AXMExchangeRateView
    {          
        info(strFmt("%1 %2", AXMExchangeRateView.ValidFrom, AXMExchangeRateView.ExchangeRate));
    }
}
Running the code returns all records, the date effective filters do not apply.
Modifying the view properties
By default, using a view to display data form a date effective table disables the date effective search results, which is useful when it is necessary to always return all data. However, it is possible to enable the date effective parameters, by changing the view property below