KB211138: In MicroStrategy 10.x, report execution against Vertica 7.x warehouse fails when there are previously failed executions because of exceeding the maximum row count

• Strategy

This issue has been classified as a defect by MicroStrategy and is specific to Vertica ODBC driver version 7.00.01.00.

SUMMARY
This issue has been classified as a defect by Strategy and is specific to Vertica ODBC driver version 7.00.01.00. Multiple workaround for this issue have been provided.

SYMPTOM
In Strategy 10.x, report execution against Vertica 7.x database fails when there are previously failed executions because of exceeding the maximum row count. An error similar to the following is received:


Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed. 
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HY000:20: on SQLHANDLE] [Vertica][VerticaDSII] (20) An error occurred during query execution: *** report SQL *** on (a13 [01000:10: on SQLHANDLE] [Vertica][VerticaDSII] (10) An error occurred during query preparation: Multiple commands cannot be active on the same connection. Consider increasing ResultBufferSize or fetching all results before initiating another command. Connection String: DSN=xxxxx;AUTOCOMMIT=1;BACKUPSERVERNODE=;. SQL Statement: *** report SQL *** Error in Process method of Component: QueryEngineServer, Project PROJECTNAME, Job xxx, Error Code= -2147212544.

CAUSE
This issue has been found specific to 32-bit Vertica ODBC driver version 7.00.01.00.

ACTION
An issue has been logged with Vertica support to get a root cause for this error and provide a fix.

WORKROUND
One of the following workarounds cane be used to address this issue:

Workaround 1:
Increase the 'Result buffer size' value in the ODBC DSN configuration. This setting can be accessed by editing the DSN from the Windows ODBC Administrator. Strategy does not have a recommendation on an optimal value that needs to be set for this setting. Contact Vertica Technical Support for additional details regarding this setting.

Workaround 2:
Disable connection caching on the Strategy database connection. This can be achieved by setting the following 2 settings on the advanced tab of the DB connection editor to a value of zero.

Connection lifetime (sec)
Connection idle timeout (sec)

Workaround 3:
This issue is not seen with Vertica ODBC driver version 7.00.00.00; users can use this driver version instead.

The Strategy Internal Reference Number for the issue discussed in this technical note is 971111.

Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.

Comment

0 comments

Details

Knowledge Article

Published:

May 25, 2017

Last Updated:

May 25, 2017

Error: SQL Generation Complete QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HY000:20: on SQLHANDLE] [Vertica][VerticaDSII] (20) An error occurred during query execution: *** report SQL *** on (a13 [01000:10: on SQLHANDLE] [Vertica][VerticaDSII] (10) An error occurred during query preparation: Multiple commands cannot be active on the same connection. Consider increasing ResultBufferSize or fetching all results before initiating another command. Connection String: DSN=xxxxx;AUTOCOMMIT=1;BACKUPSERVERNODE=;. SQL Statement: *** report SQL *** Error in Process method of Component: QueryEngineServer, Project PROJECTNAME, Job xxx, Error Code= -2147212544.