KB251032: While performing a data import in MicroStrategy Secure Analytics Enterprise 10.0, “Database error <5>: Execute Query failed.” error occurs when retrieving catalog for Denodo database

SUMMARY
This issue has been classified as a defect by Strategy. The impact is limited to Denodo database. A code fix for this issue is in Strategy 10.0 Hotfix 1. Additional steps are needed to upgrade the database instance to apply the fix.
SYMPTOM
In Strategy 10.0,  the following error is received when retrieving catalog for Denodo database: 

Database error <5>: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect.

1. Log into Strategy Web and select to import data. Click on Database >> Pick Tables.
2. Create a new DSN-less database instance for Denodo. 
3. After selecting the database instance, a list of namespaces are retrieved.
4. Click on "namespace". The error message "Database error <5>: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [ :7: on SQLHANDLE]" is displayed.
5. Below entries are caught in DSSErrors.log.
   

   
   [Database Classes][Error] Execute Query failed.Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [ :7: on SQLHANDLE] Connection String: DRIVER=PostgreSQL Unicode(x64);DATABASE=xxx;SERVER=xx.xx.xx.xxx;PORT=9996;UID=xxxxxx;SSLMODE=disable;READONLY=0;PROTOCOL=7.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=255;MAXLONGVARCHARSIZE=8190;DEBUG=0;COMMLOG=0;OPTIMIZER=0;KSQO=1;USEDECLAREFETCH=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;LFCONVERSION=1;UPDATABLECURSORS=1;DISALLOWPREMATURE=0;TRUEISMINUS1=0;BI=0;BYTEAASLONGVARBINARY=0;USESERVERSIDEPREPARE=1;LOWERCASEIDENTIFIER=0;GSSAUTHUSEGSS=0;XAOPT=1;. SQL Statement: SELECT DISTINCT database_name NAME_SPACE, view_name TAB_NAME FROM CATALOG_METADATA_VIEWS() WHERE database_name=xxx order by 1,2;. [Catalog Server 2][Error][0x80041404] Database error <5>: Execute Query failed.Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [ :7: on SQLHANDLE]
   
   

   
   

NOTE: Database connection type needs to be upgraded manually to apply the fix.
Follow the steps below to upgrade the database instance:
 

• On Strategy Developer, navigate to “Administration”-> “Configuration Managers”->”Database Instances”
• Double click any one of the database instances and click on “Upgrade”
  

• In “DBtypes script file”, user can see the PDS file ”\\Common Files\ Strategy\Database.PDS”.
• Click “Load” to load all database types
• Choose “Denodo 5.x” and then click “>” to add it to existing database types.
• Click “Yes” to replace the existing one.
• Click “OK” to save the changes.

 
CAUSE
This is a known issue with Strategy 10.0.
ACTION
This issue has been fixed In Strategy 10.0 Hotfix 1. Upgrade to this version to take advantage of the fix.