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 Friday, July 11, 2008 4:10 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
It works in SQL editor with the space. Not keen on changing the driver, will be my last resort.

Thanks
Danny
Post #532256
Posted Friday, July 11, 2008 4:20 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
Just tried the Microsoft driver and get the same error (with the space aswell).

Thanks
Danny
Post #532261
Posted Wednesday, July 16, 2008 7: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
I tried the MS driver and get the same error.

Thanks
Danny
Post #535204
Posted Wednesday, July 16, 2008 7:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #535209
Posted Wednesday, July 16, 2008 7:55 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
Sorry, its works in the editor but the same error from Excel for both drivers.

Thanks
Danny
Post #535213
Posted Wednesday, July 16, 2008 12:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #535494
Posted Friday, July 25, 2008 12:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:34 AM
Points: 49, 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.



Post #540712
Posted Friday, August 29, 2008 7:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 2:23 PM
Points: 254, Visits: 131
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
Post #561578
Posted Monday, September 22, 2008 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 6, 2012 9:30 AM
Points: 9, Visits: 33
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.

Post #573926
Posted Monday, September 22, 2008 6:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 2:23 PM
Points: 254, Visits: 131
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
Post #573964
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse