Error Using Oracle Synonym via LinkedServer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error Using Oracle Synonym via LinkedServer

Author

Topic

TerryP
Starting Member

4 Posts

Posted - 2009-01-22 : 12:15:15

SSEE 9.00.3042

I have a LinkedServer (called ORACLEREBAGATEWAY) to Oracle (10gR2)
Here is the definition
It autheticates to Oracle as SCOTT.

In Oracle (Sql*plus), as SCOTT, "SELECT * FROM an_oracle_synonym" works just fine as expected.
where "an_oracle_synonym" is a valid synonym pointing to another schema's table.

In SSMS 2005/Toad-for-sqlserver, When I do "SELECT * FROM ORACLEREBAGATEWAY..SCOTT.an_oracle_synonym",

I get this error:

SQL Server Database Error: The OLE DB provider "OraOLEDB.Oracle" for
linked server "ORACLEREBAGATEWAY" does not contain the table
""SCOTT"."an_oracle_synonym"". The table either does not exist or the
current user does not have permissions on that table. 2 0

Alternatively, switching from using synonym to using a View works just fine.
e.g. "SELECT * FROM ORACLEREBAGATEWAY..SCOTT.an_oracle_view"

But I really need to use the synonym.

TIA for your help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-22 : 12:21:13

Search for Synonym in Booksonline.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources