July 16, 2008 at 12:34 pm
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
July 25, 2008 at 12:20 am
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.
August 29, 2008 at 7:38 pm
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.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
September 22, 2008 at 4:50 pm
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.
September 22, 2008 at 6:58 pm
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.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
January 16, 2013 at 8:38 am
This works for me
SELECT [<column1name>]
,[<column2name>]
FROM [<linked servername>]..[<oracleschemaname>].[<oracletablename>]
GO
January 16, 2013 at 8:41 am
The brackets helped me every value I supply in the brackets are in uppercase
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply