Direct Database Request in OBIEE using Essbase causes [nQSError: 46008] Internal error

Direct Database Request in OBIEE using Essbase causes [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1003. (HY000)

Applies to:
Business Intelligence Answers Option – Version: 10.1.3.2.1 to 10.1.3.4.1 [1900] – Release: 10g to 10g

Symptoms

When running a query in Direct Database Request in OBIEE against Essbase, the following error occurs:

ERROR
———————–

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1003. (HY000)

The query works in Answers screen but not in Direct Database Request screen.

STEPS
———————–
By following these steps the issue can be reproduced using Essbase as data source:
1. Create a report in Answers
2. Verify that the query returns results
3. Now copy the physical SQL of the above query from the query log (section ‘…sending query to database…’)
5. Copy it into the Direct Database Request SQL statement field
6. Verify that the columns in SELECT statement are displayed in the Results Column section
7. Run the results and the above error is generated

Cause

This issue has been caused by Bug 6869282 “DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE”.

There is an issue with the code that checks for FROM clause in the SQL. The parsing code expects a space before and after the FROM clause.

Solution

The behavior is reproducible in the latest version of OBIEE, 10.1.3.4.1.

The recommended workaround is in the MDX  query to enter space before the “FROM” clause in the physical SQL and execute the query.

A single space before the FROM clause resulted in the following error (the error moves from line 1003 in the error described above to contrary to line 1050 in error below):

Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1050. (HY000)

Adding two spaces in front of the FROM clause so that the FROM clause is aligned with the text in the upper line of the query, resolved the error and it was possible to run Direct Direct Database requests in OBIEE on Essbase.

Bug 6869282 “DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE” is planned for resolution in the next release of OBIEE.