Most of the time when I get involved in issues that are reported for the Dynamics AX Client running on a Windows Terminal Server, I can reproduce the issue also on a regular Client or using a Remote Desktop connection to a regular workstation. This issue is different, as I really had to use a Windows Server with Terminal Services in Application Mode to be able to reproduce which took me some time to realize…
So the issue has to do with a Dynamics AX 2009 Client running on a Windows Server 2008 with Terminal Services in Application Mode. When using the Export Grid to Excel feature of Dynamics AX 2009 on a Form with a Grid having about 1000+ records the export was failing with the following error message:
<PasteSpecial method of the worksheet object could not be executed>.
As mentioned before, this issue was only occurring on a Windows Terminal Server not on a regular Client and also not with a Remote Desktop connection to a regular Client or Windows Server.
The Export Grid to Excel functionality in Dynamics AX 2009 is heavily dependent on the Windows Clipboard (see also: How does the Export to Excel feature work under the hood?) and is a two-step operation involving the Dynamics AX Kernel and Dynamics AX Application (X++ code). First the Dynamics AX Kernel is fetching the raw data and placing it in the Windows Clipboard and then a method of the X++ class SysGridExporToExcel is called to place and format the data in Excel.
In our case at the time when the Application part was executed no data was present in the Windows Clipboard. The Clipboard was empty! Therefore the pasteSpecial call in Excel failed as there was no data to be pasted from the Clipboard. This means the Dynamics AX Client (Kernel) failed placing the data in the Clipboard.
But why, and why only on a Windows Terminal Server? – On a regular Windows installation Clipboard data is allocated from Paged Pool. If however Terminal Services in Application Mode are installed, the memory is allocated Session Paged Pool instead! The Session Paged Pool is a lot smaller than the regular Paged Pool and so data that can be placed into the Clipboard on a regular client might not within a Remote Desktop session to a Terminal Services in Application Mode.
The amount of memory available for the Session Paged Pool can be configured and so I was able to fix my local repro by changing the value for SessionPoolSize (see also: Applications may not run correctly in a Terminal Services environment). However changing the Session Paged Pool can have serious side effects, for example this can reduce the maximum total number of possible Terminal Services sessions.
I did not like the idea altering this value, trusting the default value is the best option, and was looking for alternatives… As we were running into this issue only for Grids showing a lot of records and in fact there was only one Form that is regularly exported having that many records, we decided to create an own export logic for this Form that is not dependant on the Windows Clipboard at all, calling the custom export code for the one affected Form.
However we thought the export logic should also be flexible, in case other Forms are filling up in the future running into similar issues. So we ended up with the following static method you can call and simply pass the Table Id of the Table to export as parameter ending up in Excel showing the exported data:
Container con, conSkip;
int field, counter;
str line, fieldName;
//Access Table based on TableId
dt = new DictTable(tableId); //For Table schema
c = dt.makeRecord(); //To access records
//Create temporary file
fileName = strFmt('%1\\ax_%2.csv', WinAPI::getTempPath(), dt.name());
cio = new CommaIO(fileName, "W");
line = "";
for(field = 1; field <= dt.fieldCnt(); field++)
//Skip System Columns
conSkip = conins(conSkip, maxint(), field);
con = conins(con,field,dt.fieldName(dt.fieldCnt2Id(field)));
con = conNull();
while select c
line = "";
for (field = 1; field <= dt.fieldCnt();field++)
if(0 == confind(conSkip, field))
con = conins(con, field, c.(dt.fieldCnt2Id(field)));
con = conNull();
info(strfmt("%1 records of table '%2' exported", counter, dt.name()));
//Open CSV file in registered app