Description

I have been trying to run a winshuttle query and I get errors. If I try and limit the dataset I get: Query cannot be executed because the query length exceeds 65536 characters If I try the full dataset : Exception of type 'System.OutOfMemoryException'

Applies to

Query 10.x

Cause

Whilst the 64k row barrier has been lifted on products since Excel 2003, there are other limitations if you are handling large amounts of information. The number of rows imported is also dependent on the number of columns, the more columns you have, the less rows that can be displayed. In addition Excel 2007 and 2010 still have 2GB memory limitations, if you are handling large amounts of information. If using pivot tables linked to external database tables (or text files), there can be no more than 65,536 unique values in any one field. Unless you are handling large datasets, you should not encounter these other limitations.

Solution

If you are getting this error whilst trying to run a query, please check whether you have any where clause with an 'IN' operator. The message indicates that the file size exceeds the limit of 65536 characters. 

The RFC to SAP call does not support more than 65536 characters per query. The length of the query measures spaces, characters, commas, inverted commas, and all other symbols to create the query and it counts as a character. It takes into account both the data in the IN file as well as the data being extracted. 

The following characteristics apply to the IN operator in Winshuttle QUERY: 

* A query can contain several IN operators. 
* Each IN operator can not contain more than 1000 values. 
* A query line can not exceed 65,536 characters. 

If you have a lot of data to process, you will have to divide the file size and execute two queries.

Additional Information

The Exception of type 'System.OutOfMemoryException' can be solved by using the chunking option in the advanced run options of Query and is covered in more details here: Exception of type 'System.OutOfMemoryException' was thrown