Error Using Open SQL engine with MII

[former_member361580]

Hi Experts,

I am trying to use the open sql engine with with SAP MII. I have configured a "JDBC Custom DataSource" as follows

Driver Name: System_Driver

SQL Engine: Open SQL

Isolation Level: Default

JDBC Version: 1x (without XA support)

JDBC Driver Class : oracle.jdbc.driver.OracleDriver

Server URL Format : jdbc:oracle:thin:@<host>:<port>:SID

Username:

Password:

I have configured a datasource connection in MII. When using this connection in sql query, I am able to select the query mode. But when I open fixed query or query window, I get the error

ORA-00942: table or view does not exist

OpenSQLExceptionCategories: [NON_TRANSIENT, DB_OBJECT_UNKNOWN].

The logs show the following error

SQL error occurred on connection <server>:<sid>:<user>: code=942, state="42000", message="ORA-00942: table or view does not exist";

SQL statement is "SELECT * FROM "BC_DDDBTABLERT" WHERE "NAME" = ?".

[EXCEPTION]java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist...

and the template is unable to fetch the table list from the DB.

Please advise

Regards

[former_member361580]

Hi Team,

It looks like the table BC_DDDBTABLERT is used by the SAP NetWeaver DB to grant access to the database tables. So what is the best practice for using open connector and use the connection in workbench.

Is there a list of prerequisites/preconditions that the DB needs to meet?

Regards

Maaz

[Former Member]

Hi Maaz,

You are right in stating that BC_DDBTABLERT is a system level table which stores metadata and definition of other tables.

In order to access it you should use Data Source type of Data Server and point to XMIIDATASOURCE created at Netweaver.

Regards,

Anushree

[former_member361580]

Hi Anushree,

thanks for the response.

But connecting to the XMIIDATASOURCE is not the goal. I want to connect a custom DB and use the opensql engine.

Are there any standard set of activities that can be performed on the DB (like creating these authorization tables etc.) that can be followed. And the connection to that external DB established using the open connector to use it with SQL queries in the workbench. We do not wish to create our custom tables in the XMIIDATASOURCE DB.

Regards,

Maaz

[Former Member]

Hi Maaz,

what the validation Query you have used while configuring?

You can refer this blog may be it will help you :

Regards,

Suman