KB39030: "QueryEngine encountered error: Execute Query failed. Error type: Odbc error." appears when executing report in MicroStrategy Developer 9.x against SQL Server 2008 database

Salesforce Solutions Architect • Strategy

This KB addresses an error seen when executing a report against SQL Server 2008 database.

SYMPTOM:
When executing a report against the SQL Server 2008 database, the following error can appear.


Error: SQL Generation Complete

QueryEngine encountered error: Execute Query failed. 

Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Incorrect syntax near '.500'. Connection String: DSN=TEST_SQL_WH;UID=ZFA1DEC16U3;APP=Strategy 9.0 ? Common Files;WSID=CHN-ZFAN;DATABASE=ZFA1DEC16U3;. SQL Statement: select a11.500kSalesKey  500kSalesKey,

a12.Y_DESC  Y_DESC,

a11.SAL  WJXBFS1

from SAL a11

join L_Y a12

 on  (a11.500kSalesKey = a12.500kSalesKey).. 

Error in Process method of Component: QueryEngineServer, Project test SQL, Job 3493, Error Code= -2147212544

CAUSE:
The SQL Server database can not support the table with the column name started with number for querying, like "500kSalesKey" above.

ACTION:
Change the name of the table column to begin with letters instead of numbers, like "k500SalesKey" for the example above.

WORKAROUND:
If the name of the table column can not be changed, the SQL can be run in FreeForm SQL report by using the bracket for the column name, as shown below.
Still using the example above, insert the following SQL statement in the FreeForm SQL report:


Sql
select a11.  ,

       a12.Y_DESC  Y_DESC,

       a11.SAL  WJXBFS1

from   SAL a11

join   L_Y a12

on     (a11. = a12.)

Then the SQL and report can be executed successfully.

Comment

0 comments

Details

Knowledge Article

Published:

May 11, 2017

Last Updated:

May 11, 2017