Linked server to Oracle - how to query?

• Bullfrog

  SSC Eights!

  Points: 895

  More actions

  April 29, 2010 at 7:43 am

  #142794

  Hello:

  I have a linked server connection from SQL Svr 2005 to Oracle 10. The connection works when I right click and 'test connection'. However, I've tried executing the following statements and keep getting the following error.

  select * from myLinkedServer..mySchema.myTable

  -or-

  select * from myLinkedServer...mySchema.myTable

  -or-

  select * from myLinkedServer...myTable

  The OLE DB provider "MSDAORA" for linked server "myLinkedServer" does not contain the table ""mySchema"."myTable"". The table either does not exist or the current user does not have permissions on that table.

  When I go over to Oracle (Toad) I can run the following query just fine.

  select * from mySchema.myTable

  Is this a syntax thing, or a permissions thing?

  Thanks!

• Mike01

  SSChampion

  Points: 11395

  More actions

  April 29, 2010 at 8:16 am

  #1159725

  Have you tried using OpenQuery instead?

  Select * from OpenQuery(MyLinkedServer,'Select * from MySchema.MyTable')

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  April 29, 2010 at 9:40 am

  #1159827

  Does oracle handle schemas the same way as MS SQL? (Totally clueless about all things oracle)

  The other option to try might be:

  select * from myLinkedServer...[mySchema.myTable]

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Mike01

  SSChampion

  Points: 11395

  More actions

  April 29, 2010 at 9:49 am

  #1159843

  Garadin (4/29/2010)

  ---

  Does oracle handle schemas the same way as MS SQL? (Totally clueless about all things oracle)

  The other option to try might be:

  select * from myLinkedServer...[mySchema.myTable]

  I don't remember, but I think it doesn't use the 4 part naming convention like SQL nor does it need the brackets. I think it would be more like

  select * from myLinkedServer.mySchema.myTable

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/