Thursday, January 15, 2009

OBIEE: error : Odbc driver returned an error (SQLExecDirectW)

My method so far to make changes to our existing reports has been to
1. Open the report
2. Modify
(Most, if not all, are Direct Database Requests)
3. Cut and Paste the SQL into my editor (JDeveloper)
4. Fix any issues
5. Run in SQL*Plus
6. Cut and paste back into OBIEE

On occasion, I'll have to use the Catalog Manager, select report, properties, Edit XML and get the SQL from there. Obviously not the most efficient way, but it works.

Others, like Mr. Berg, have suggested turning the log level to level 2 and capturing the SQL being passed to the database. I'm not that industrious yet.

Anyway, I've run across this error a couple of times so far.

When I do number 6 and click on Validate SQL and Retrieve Columns, I get the above mentioned error. Hmmm...I've just run this thing in SQL*Plus and it works just fine. The queries are really too big to simply eyeball either.

So I went simple, in the text box I entered SELECT * FROM DUAL and clicked on Validate SQL and Retrieve Columns:

error : Odbc driver returned an error (SQLExecDirectW).
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: 17001] Oracle Error code: 933, message: ORA-00933: SQL command not properly ended at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
error : SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "BIDEV"."Connection Pool" SELECT * FROM DUAL when mailing_group = ''EMPLOYEE'' then ''Other'' when mailing_group = ''TEST'' then ''Other'' when mailing_group = ''VIP-EXTERNAL'' then ''Other'' when mailing_group = ''SITEUSERS'' then ''Other'' else mailing_group end )')}


It appears the query that previously occupied that box is still there...partially.

Mr. Berg suggested clearing the cache, so I went into the Administration tool, Manage, but cache was grayed out. I asked the DBA and Linux guy to pass me the file, but they were being difficult today, so I didn't get it.

In the short term, I just created a new report appended with " R" (refactored) and moved on to the next one. Hopefully tomorrow I'll be able to get some answers.

4 comments:

oraclenerd said...

So I've noticed that this seems to occur more often on those reports whose SQL I pull from XML (in other words, too long for the web text box).

Unknown said...

HI oraclenerd,

We are facing the same issue. OBIEE tool has to get data from SAP BW system which is the backend. Users have developed reports in BEx and creating the same in OBIEE.

For some characteristics we were able to look at the members but for huge amount of data we are getting error.

"Odbc driver returned error (SQLExecDirectW)"

Please help me out.

Thanks,
Sudhakar

Anonymous said...

Is there any solution to the above problem . I am also facing similar problem

Unknown said...

So...the following is completely pointless:

error : Odbc driver returned an error (SQLExecDirectW).
error : State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

How do we find the REAL error