I have several customers that are using oracle linked servers on MSSQL2k. Persons that have multisession rights on the oracle side seem to not have problems querying multiple tables but one of my customers only has the rights to have two sessions at any given time. This customer with the session limitation is trying to query multiple tables and it appears that each table query constitutes a session therefore after the second table is queried they exceed the quota and the job stalls.
I am currently using the microsoft oledb provided for each linked server. For the provider options I selected Nested Queries and AllowInProcess. I have tried the oracle provider, recieved errors, but these errors have not been resolved.
Using msaccess and odbc in the prior implementation worked for this customer. Does anyone have any suggestions or tech links that cover this scenario.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].