Linked Server to Oracle

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Linked Server to Oracle

22 posts, Page 1 of 3

1 2 3 »»»

Linked Server to Oracle

Rate Topic

Display Mode

Topic Options

Author

Message

Dannygr

Posted Tuesday, July 08, 2008 10:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 2:25 AM
Points: 30, Visits: 54

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

Post #530170

Vijaya Kadiyala

Posted Wednesday, July 09, 2008 11:27 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, Visits: 409

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

Post #531105

Dannygr

Posted Thursday, July 10, 2008 1:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 2:25 AM
Points: 30, Visits: 54

Hi Vj

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

Thanks
Danny

Post #531409

Christian Buettner-167247

Posted Thursday, July 10, 2008 1:39 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:12 AM
Points: 2,526, Visits: 3,620

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

Best Regards,
Chris Büttner

Post #531416

Dannygr

Posted Thursday, July 10, 2008 1:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 2:25 AM
Points: 30, Visits: 54

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

Cheers
Danny

Post #531419

Christian Buettner-167247

Posted Thursday, July 10, 2008 6:40 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:12 AM
Points: 2,526, Visits: 3,620

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:
OriginalQuery: SERVER..SCHEMA.OBJECT
QueryExecuted SERVER.SCHEMA.OBJECT
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

Post #531537

Dannygr

Posted Friday, July 11, 2008 3:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 2:25 AM
Points: 30, Visits: 54

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

Thanks
Danny

Post #532241

Christian Buettner-167247

Posted Friday, July 11, 2008 3:44 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:12 AM
Points: 2,526, Visits: 3,620

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

Post #532244

Dannygr

Posted Friday, July 11, 2008 3:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 2:25 AM
Points: 30, Visits: 54

Yes it did.

Thanks
Danny

Post #532247

Christian Buettner-167247

Posted Friday, July 11, 2008 4:05 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:12 AM
Points: 2,526, Visits: 3,620

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

Post #532253

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions