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 Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 54
It works in SQL editor with the space. Not keen on changing the driver, will be my last resort.

Thanks
Danny
Dannygr
Dannygr
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 54
Just tried the Microsoft driver and get the same error (with the space aswell).

Thanks
Danny
Dannygr
Dannygr
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 54
I tried the MS driver and get the same error.

Thanks
Danny
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5341 Visits: 3889
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
Dannygr
Dannygr
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 54
Sorry, its works in the editor but the same error from Excel for both drivers.

Thanks
Danny
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5341 Visits: 3889
Hi Danny,

Sorry for the late reply. I had almost no time today to troubleshoot this, but I might be able to check again tomorrow.
Although my confidence to find a solution is pretty low by now.

Best Regards,

Chris Büttner
Scott MacCready
Scott MacCready
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 175
I'm going to jump in with some oblique ideas.
If you are using the MS OLEDB provider, you may not realize that it has not been upgraded past 8.x and probably never will be because Oracle won't provide critical info to MS anymore. So, MS now suggests that a linked Oracle server should be provided by the OraOLEDB provider. It goies without saying that the version should be at least the same level as the target server. To use this, however, the Oracle client must be installed on the same machine as SS.

Another idea of possible interest:
Perhaps you could create synonyms in a sql server database (hosted in the same SS instance) that point to the linked Oracle server. These can be views which means that they can preprocess some of the Oracle sourced data before presenting it to Excel.



debrucer1
debrucer1
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1362 Visits: 136
I have only skimmed this thread; but, I started a similar one back in March on this and a couple of other boards. I opened a TAR with Oracle and reported about five different errors (by number) and a couple of unusual situations like two, three and four repetitions of fields in SQL made in the linked server. I jumped through weeks of hoops for Oracle only to find out that once the server was linked, that the way around the errors was to use Visual Studio to access the data. We worked with both MS and Oracle providers, and after much testing decided tht the MS providers (even though "not updated past version eight") worked better with our 2005 SP2 to Oracle10R2 connection... and that is what we went with in development, QC and production.

No. It's not "fast" and fortunately for us, the volumn isn't heavy. It does what it needs to do, and it does it well with no errors.

My advice would be to create the linked server and forget it in the SS Management Studio. Use a third party tool to access the data.

Thanks,

David Russell
Oracle Since 1982
SQL Server Since 1998
Bonz99
Bonz99
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 41
Greetings. Since you seem to have been able to make a working connection to Oracle, would you be willing to post your code? I can make a linked server where the test connection works, but most queries generate "out of memory" or other errors.
debrucer1
debrucer1
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1362 Visits: 136
not sure I have any time to be posting anything.... wouldn't have been on this site and/or reading this response except for the email notification. There were a couple of little things as I recall. Let me look tomorrow and see if I can point out the right piece that you might be missing.

You do NOT have to create a "UDL file" even though everything I read says you do... and that was originally in my instructions. It didn't make sense that the UDL file required the user/schema and password, as well as the linked server requiring it... and when I finally just skipped that step, it still worked.

You say your queries get "out of memory" errors. I've never seen that with any of mine. Exactly where/how are you executing your queries that get this error?

Like I said before, it seemed to our developers that the MS software worked better than the Oracle software... and I know that there was a funky way they had to code to use a cursor; but, it works, daily, in development, QA/QC and production.

It's receiving data in California from a database in Montreal, and the "cloud" between here and there is poor; but, it's doing the job as designed.

It wasn't easy to get there, and there's a lot on the internet about some of the issues, 32 bit vs. 64, bit etc. The untimate solution though wasn't which provider to use... it was which tool to use after the linked server was created.

Visual Studio is what our developers are using to get their code working.

Thanks,

David Russell
Oracle Since 1982
SQL Server Since 1998
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