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.

Labels: obiee, sql

  ¶ 1/15/2009 12:07:00 AM