Linked Server to Oracle

  • 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

  • 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

  • Hi Vj

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

    Thanks

    Danny

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

    Best Regards,

    Chris Büttner

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

    Cheers

    Danny

  • 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

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

    Thanks

    Danny

  • 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

  • Yes it did.

    Thanks

    Danny

  • 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

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

    Thanks

    Danny

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

    Thanks

    Danny

  • I tried the MS driver and get the same error.

    Thanks

    Danny

  • 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

  • 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