Querying an Oracle linked server fails

The other day, I needed to pull some data from an Oracle server on our network. The oracle server was set up as a linked server on my SQL Server 2005 machine. For some reason, the query didn’t work. Now, when working with an oracle linked server, you have to include the linked server name, the schema name and the table name. Also, you can use an alias just like any SQL Server table. But, the key thing is to include the schema and table name. The query looks something like this:

select col1
from [OracleServer]..[schema_name].[table_name]

For some reason, the query failed with this error:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OracleServer" does not contain the table ""schema_name"."table_name"". The table either does not exist or the current user does not have permissions on that table.

Now, I could query the data using Toad or MS Access, so I knew the data was OK. There must be another issue that is causing my query to fail. I did some digging around and found the following KB from Microsoft:

Can’t Use Four-Part Name to Query Oracle Table If Name Contains Lowercase Characters You read that correctly; If you use lower case characters in your table name, the query fails. The fix is pretty easy, just modify you query to use upper case letters only:

select col1
from [ORACLERSERVER]..[SCHEMA_NAME].[TABLE_NAME]

And just like that, the query works! I know the KB says the issue exists on SQL Server 2000, but I am seeing the issue on SQL Server 2005 as well. Good luck!

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter