weird issue connecting from Oracle to MSSQL

[hannah00]

Hi all,

I have the DB link and I was able to access data on two database on MSSQL via the link fine; however, I wasn't able to access to the third datbaase via the same link and all three databases in MSSQL are sitting on the same server.
I should have the full privilge on the three database.

The errors I got is "table or view does not exist"
[1]: (Error): ORA-00942: table or view does not exist [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'VENDOR'.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL State: 00000; SQL Code: 8180) ORA-02063: preceding 2 lines from MSQL

any thoughts on how to troubleshoot the issue???

thanks,

[davey23uk]

well first thing - make sure the table or view does exist

[KenEwald]

Try using double quotes around the object name "TableName". If the table exists, and you have permissions to it, it's probably a mixed case table name.

Ken

[hannah00]

table does exists and I was able to query in MSSQL and I did try double quote.

I am not sure if this is a problem, when I set up the ODBC, Microsoft will ask you the default database so let say I have three daabase in MSSQL I want to get to and I chosee the first database A. I was able to query database and database B but the C database is the one I have problem with. I am not sure this default database have any thing to do with my problem.



Any other advises???

[KenEwald]

default database has nothing to do with it. Permissions are granted to the user regardless of the database.

I'm thinking the user (used in your ODBC connection) doesn't have permissions on the 3rd table.

Ken