Dynamic Switching of Schema/Database based on user logged in

1362797

Hi All,

With the help of Session variables i was able to achieve the dynamic switching of the schemas to which the OBIEE Connects. It is working fine as expected.

However i am getting an error while running an ibot.

View Display Error

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: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied at OCI call OCISessionBegin. [nQSError: 17014] Could not connect to Oracle database. (HY000)

What can be the probable issue?

Regards,

[Deleted User]

What can be the probable issue?

^-- The user running the agent isn't a user known to / authorized on the database schema you're dynamically connecting to.

1362797

Hi Christian,

Thanks for the reply. The same user when creating a report in adhoc is working fine as expected but when he is running an agent. It throws the error.

1362797

and using the same report in agent which he has created in adhoc

[Deleted User]

Have you verified this in detail in the agent configuration, its XML and most importantly the log files of the agent execution  as well as the detailed logging in the NQServer and NQQuery logs?

1362797

Hi Christian,

• When i hardcode one schema in connection pool (not using switching of the schemas) and try to run an ibot it is working fine. No Errors.
• There are no logs mentioned as when i try to run the ibot it gives me error in case of dynamic switching.

1362797

So just wanted to know how ibot/agents uses the connection pool information because in adhoc the report is working fine for the user whereas an agent is not working

Gianni Ceresa

Hi,

What are the values of username and password in your connection pool?

It's maybe :USER and :PASSWORD ?

1362797

User Name: VALUEOF(NQ_SESSION.USER )

Password:  VALUEOF(NQ_SESSION.PASSWORD)

Gianni Ceresa

Have a look at this : Agents not working when :USER :PASSWORD used in connection pool

Same situation, same issue ...

[Deleted User]

So increase the logging. Normal agent operations always produce the logs necessary.

You must find out what exact values are being used for your variables whne running an agent and through that which schema should be accessed.