Post reply

Linked server to Oracle - how to query?

  • 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!

  • 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/

  • 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]

  • 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/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply