Friday, June 24, 2011

Can't consume Oracle Synonyms with a SQL Server linked server endpoint

Generally, I'd prefer to post how you *can* do something, but to save anyone having the same dilemma some time -- SQL Server linked server objects can't consume Oracle Synonyms.  This is due to the same reason you can send a "select top 10 *" query through a linked server even though that's just gibberish to Oracle.  Commands directly to a linked server object rely on SQL Server objects and syntax regardless of the fact that the back end is Oracle (although still magically persist case sensitivity).

If you need to use a synonym in a query against an Oracle linked server, you will have to use openquery.  This isn't pretty for more complex queries/loads.  The syntax for using openquery for this is
  • select * from openquery(LinkedServerName, 'select object from synonym.object')

It is also possible (but bad behavior) to look directly at the Synonym and find the referenced object (easiest in a product like toad, but if you use sqlplus the command is DESC synonymname).  Again, the developer who created the synonym probably created it because he wants you to use it, so unless you are the developer for the target server, you should really check this is okay.

No comments:

Post a Comment