Post reply

Linked Server to Oracle

  • July 8, 2008 at 10:02 am

    #209662

    I have set up a linked server to Oracle 9i from SQL server 2005. This works fine when working with queries in SQL Server Management Studio, but I am after getting this working with Excel. I have set up an ODBC connection to SQL Server, then try querying the linked server but get an "invalid object name" when using Microsoft Query. I would assume if the query works in SQL Server studio it would also work in MS Query (therefore excel reports).

    Thanks

    Danny

  • July 9, 2008 at 11:27 am

    #840404

    HI,

    May i know answer to the below questions, to understand your requirement in Better way.

    1) Are you joing Oracle tables/views with SQL Server over the Linked Server?

    Thanks -- Vj

    http://dotnetvj.blogspot.com

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • July 10, 2008 at 1:30 am

    #840663

    Hi Vj

    Yes I was intending to join the Oracle and SQL Server tables.

    Thanks

    Danny

  • July 10, 2008 at 1:39 am

    #840669

    Can you check if you use the proper case? (lower case / upper case)

    Best Regards,

    Chris Büttner

  • July 10, 2008 at 1:49 am

    #840672

    The query is exactly the same as the one thats runs in SQL Server Management Studio (all upper case).

    Cheers

    Danny

  • July 10, 2008 at 6:40 am

    #840760

    I just did a quick check here and got the same issue as you with the Oracle OLEDB Driver.

    The microsoft driver works for me so this might be an option for you.

    I am currently investigating and if I find some more info I will let you know.

    Edit: Either the MSQuery engine or the OleDB driver from Oracle seems to mess up the 4 part name:

    [font="Courier New"]OriginalQuery: SERVER..SCHEMA.OBJECT

    QueryExecuted SERVER.SCHEMA.OBJECT [/font]

    The double dot gets lost during processing of the query and sp_prepexec is called with the wrong name.

    Interestingly the MS driver fails immediately to execute sp_columns_ex (data type conversion error) and then seems to execute the remote query. The Oracle driver does not fail with this statement, but instead fails later when it tries to execute sp_special_columns against the oracle server (which obviously cannot succeed). This might be where the wrong 4(3)-part name is built.

    2nd Edit: Try adding a space between the double dots. This seemed to fix the issue for me with the Oracle driver as well.

    Best Regards,

    Chris Büttner

  • July 11, 2008 at 3:40 am

    #841323

    That did'nt work. Different error, "Could not add the table 'ORACLECONNECTION.'."

    Thanks

    Danny

  • July 11, 2008 at 3:45 am

    #841325

    Regarding the original error - did the error message show the wrongly built 3-part name as in my case?

    SERVER.SCHEMA.TABLE

    Best Regards,

    Chris Büttner

  • July 11, 2008 at 3:49 am

    #841327

    Yes it did.

    Thanks

    Danny

  • July 11, 2008 at 4:05 am

    #841332

    Just to be on the safe side, can you try to re-type the whole query in the SQL Editor and then add the space? (SELECT * FROM SERVER. .SCHEMA.TABLE)

    Not sure whether this will help at all.

    Besides that, did you try the MS driver already as a workaround? Or do you have the same issue there?

    Best Regards,

    Chris Büttner

  • July 11, 2008 at 4:10 am

    #841334

    It works in SQL editor with the space. Not keen on changing the driver, will be my last resort.

    Thanks

    Danny

  • July 11, 2008 at 4:20 am

    #841338

    Just tried the Microsoft driver and get the same error (with the space aswell).

    Thanks

    Danny

  • July 16, 2008 at 7:49 am

    #843770

    I tried the MS driver and get the same error.

    Thanks

    Danny

  • July 16, 2008 at 7:52 am

    #843775

    Hi Danny,

    in one of the post above, you wrote that adding the space in the editor works.

    Is the issue solved by this or does it only work in the editor but not in excel anymore?

    (I assumed the issue was closed)

    Best Regards,

    Chris Büttner

  • July 16, 2008 at 7:55 am

    #843779

    Sorry, its works in the editor but the same error from Excel for both drivers.

    Thanks

    Danny

Viewing 15 posts - 1 through 15 (of 21 total)

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