How To: Addressing SSRS Session Timeouts

Microsoft Dynamics AX 2012

Dealing with Dynamics AX 2012 Reporting Timeouts and Thresholds

Dynamics AX 2012 uses SQL Server Reporting Services for rendering reports. SSRS gets the data from AOS by using a custom SSRS Extension that uses WCF to communicate with AOS.

Depending on the size of the data and the complexity of the report, it might take a long time for the report to execute, resulting in various timeout and other thresholds being hit, which might cause the report rendering to fail. This article attempts to identify all the places where rendering large reports may cause thresholds could be hit and suggest tweaks or workarounds to address them.

A. Getting data ready

If the report uses Report Data Provider (RDP) to get the data, then it should be modified to use a pre-processed RDP class as the data source to invoke processing logic before a call is made to Reporting Services. For more information about RDP classes, see Using Report Data Provider Classes to Access Report Data and Report Programming Guide.

B. Report Execution Timeout

SSRS defines a Report Execution Timeout, which specifies the number of seconds after which the reporting processing times out. The default value for this is 30mins. If the report execution takes longer than that, then the report execution will fail.

This setting can be updated at the Report Server level or at an individual report level

Site level Settings

The report execution timeout at the report server level should be set to a value greater than the time required for the largest report to render. Alternately it can be set to never time out. This can be done in one of two ways -

1. Using the Report Manager - From the Site settings, the Report Timeout property can be changed.

2. This can also be changed using SQL Server Management Studio – In SSMS, Right-click the name of a report server, then click Properties. On the Server Properties window, click the Execution page and change the value for “Limit report execution to the following number of seconds”.

Report level Settings

The report execution timeout can also be set on each report, using Report Manager. Go to the report properties (see how) and in Processing Options, select either “Do not timeout report” or change the “Limit report processing to the following number of seconds” option.

C. SSRS Session Timeout

SSRS maintains a User Session which may time out if the report takes a long time to execute, causing the report execution to fail. This can be fixed for the report server by setting the 2 properties SessionTimeout and SessionAccessTimeout using the rs.exe tool. Again, these should be set to be greater than the time taken to render the largest report.

You should configure these values to be no less than the time it takes to render your largest report. Here is a sample script for rs.exe which will set these values for you:

 


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

You can run this script with the following command:

rs.exe -i <Path to SessionTimeout.rss> -s <Report Server URL> -v timeout="6000"

The tool rs.exe is usually located at “c:\Program Files(x86)\Microsoft SQL Server\110\Tools\Bin”.

The timeout is expressed in seconds, so this example sets the SessionTimeout and SessionAccessTimeouts to about an hour and a half. 

Example: c:\Program Files(x86)\Microsoft SQL Server\110\Tools\Bin\ rs.exe -i c:\Temp\sessiontimeout.rss -s <localhost/reportserver> -v timeout="6000"

Important: Do this with caution, keeping a session around longer than necessary can cause your ReportServerTempDB database to grow larger since temporary session snapshots will not be aged out as often. Also, this utility must be run as Administrator.

You can also check out this msdn blog post for more information.

D. WCF Timeouts and Thresholds

SSRS uses the Query Service (which is a WCF service exposed by the AOS) to get data. For reports with large datasets, the default WCF configuration may cause WCF to hit some thresholds at runtime. So the WCF configuration can be tweaked as follows……

Updating Server side settings

1) Open the Ax32Serv.exe.config file (it is typically under c :\Program Files\Microsoft Dynamics AX\<version>\Server\MicrosoftDynamicsAX\Bin) .

2) Locate the QueryServiceBinding element. The default value for the sendTimeout on this element is 10mins.

3) Increase the sendTimeout to a larger value, say 30mins, like so – sendTimeout=”00:30:00”

Updating Client configuration settings on the SSRS Server

1) Access the Microsoft SQL Reporting Services host using an account with Administrative privileges

2) Open the Microsoft Dynamics AX Configuration Utility (typically here: Start –> Administrative Tools –> Microsoft Dynamics AX 2012 Configuration)

3) Click the Configuration Target: drop-down and select Business Connector (non-interactive use only)

4) Create a new local client configuration using the Microsoft Dynamics AX 2012 Configuration as explained here.

5) On the Connection tab, click on “Configure Services” to open the SVC configuration utility.

6) Expand the Bindings folder and select QueryServiceEndpoint (netTcpBinding).

7) Update the values for the following properties -

o SendTimeout – This is set to 10 minutes by default. Increase it to a larger value, like 20 minutes.

o ReceiveTimeout – This is set to 10 minutes by default. Increase it to a larger value, like 20 minutes.

Note: If you are encountering connection failures due to the size of reports update the following….

o MaxReceiveMessageSize – This is set to 2147483647 by default. Increase it to double that value or 4294967295. The maximum allowed value is Int64.MaxValue.