Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Linked Server to Oracle Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2008 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, July 9, 2008 11:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Thursday, July 10, 2008 1:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, July 10, 2008 1:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 2,842, Visits: 3,874
Can you check if you use the proper case? (lower case / upper case)

Best Regards,
Chris Büttner
Post #531416
Posted Thursday, July 10, 2008 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, July 10, 2008 6:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 2,842, Visits: 3,874
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
Posted Friday, July 11, 2008 3:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, July 11, 2008 3:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 2,842, Visits: 3,874
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
Posted Friday, July 11, 2008 3:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, July 11, 2008 4:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 2,842, Visits: 3,874
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse