I have a Windows 2008 Server (x64) running Microsoft SQL 2008 (x64) and I'm creating a Linked Server connection to an Oracle server. I'm able to make the connection, but I cannot see any information regarding which schema a table belongs to.
In SQL 2005, my linked servers show the schema information as I would expect.
Does anyone know how to resolve this issue? Is it an issue with the provider, OraOLEDB.Oracle?
Any help or pointers would be appreciated.
I've created lots of linked servers in sql server 2000 & 2005, mostly to as/400's, but I'm not sure what you mean when you say "I cannot see any information regarding which schema a table belongs to" - can you clarify?
I understand. Sorry, I don't think I can help.
@Boojiboy - When you are looking at the tables via a linked server, there used to be a column for what schema. It appears that in the latest the new Oracle OLEDB drivers don't show this information any longer.
It looks like sp_tables_ex will do the trick, it came from the below article.
--verify tables OK exec sp_tables_ex @table_server = 'LINKED_ORA', @table_schema='MySchema'
@table_schema is optional. If not provided, you will get a list of all tables in all schemas.
http://it.toolbox.com/blogs/daniel-at-work/linking-sql-server-2005-to-oracle-26791