SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server to Oracle


Linked Server to Oracle

Author
Message
Dannygr
Dannygr
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 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
Vijaya Kadiyala
Vijaya Kadiyala
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1885 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



Dannygr
Dannygr
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 54
Hi Vj

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

Thanks
Danny
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3537 Visits: 3889
Can you check if you use the proper case? (lower case / upper case)

Best Regards,

Chris Büttner
Dannygr
Dannygr
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 54
The query is exactly the same as the one thats runs in SQL Server Management Studio (all upper case).

Cheers
Danny
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3537 Visits: 3889
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
Dannygr
Dannygr
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 54
That did'nt work. Different error, "Could not add the table 'ORACLECONNECTION.'."

Thanks
Danny
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3537 Visits: 3889
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
Dannygr
Dannygr
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 54
Yes it did.

Thanks
Danny
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3537 Visits: 3889
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search