Selecting Oracle data via SS Query Analyzer for public synonyms

• Summer90

  SSC-Dedicated

  Points: 32891

  More actions

  March 9, 2006 at 11:31 am

  #167326

   I have setup the linked server from SQL 2000 to Oracle 9i and all of that. I can select from the base tables via the select * from servername..schemaname.table.  That all works fine. Where I have a problem is I don't know what to put for a public synonym. Public synonyms don't have a schema owner. If I don't sepecify an owner it gives me an 'invalid object name'. 

   Any help would be appreciated.

• March 13, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Chris Walker-298426

  Newbie

  Points: 5

  More actions

  March 13, 2006 at 7:34 pm

  #626408

  You can create a puiblic synonym so each time you reference that table you dont need the owner of that table prefixed.

  CREATE PUBLIC SYNONYM customers FOR oe.customers;

  SELECT COUNT(*) FROM customers;

  This will reference oe.customer table unless the user name logged in has a customers table, it will reference the user's table instead.

  Chris