Users execute a report in Strategy Desktop 9.x and obtain the following message:
The QueryEngine encountered an error: The generated SQL string (X bytes) is longer than limitation of this ODBC driver (65336 bytes)
The value 'X' is greater than 65536. When the report Sturctured Query Language (SQL) size is evaluated in a tool, such as Microsoft Word, it turns out to be approximately half the size (X/2 bytes) as reported by Strategy Desktop (X bytes).
This is working as designed. The Strategy SQL Engine calculates the size based on two bytes per character as a standard to encompass Unicode and Double-Byte Character Set (DBCS) data. So even if the usage is for the regular Single-Byte Character Set (SBCS), it is calculated as two bytes per character so that there is a uniform standard. This is why the value calculated by the Strategy SQL Engine is approximately double that obtained from a text editor such as Microsoft Word.
To resolve the error, the user may want to increase the value of the Very Large Database (VLDB) setting, 'Maximum SQL Size'. As an example, doubling the value from 65536 bytes to 131072 bytes is a good start. Refer to the following Knowledge Base document for details on increasing this VLDB setting:
- KB6731 : 'The generated SQL string (X bytes) is longer than limitation of this ODBC driver (65336 bytes)' error message appears when running a report in Strategy Desktop 9.x
NOTE:
The VLDB setting, 'Maximum SQL Size', applies to per pass of SQL. This setting acts as a preemptive measure on the Strategy product side such that users may prevent large SQL (per pass) from being submitted to the Open Database Connection (ODBC) driver. However, if the ODBC driver itself has a limitation with regard to the size of the SQL it can handle (per pass), then the user may have to re-design the report to generate lesser SQL per pass in order to accommodate that limit in the ODBC driver.
