March 24, 2008 at 6:53 pm
We are experiencing six separate error conditions using OLE-DB to connect from SS 2005 SP2 to Oracle10g. The first three are minor, duplicate, triplicate and quadruplicate fields when we "select into... new query editor window". Depending on which tables are selected we are getting 7354 and 7356 errors... and more recently, 7306.
Those errors are identified as follows:
7354 errors are “data type does not exist” and point to a column that is not in the query executed.
7356 errors are “metadata” related and say that field type is different between compile and bind… and failure occurs.
The 7354 error appears to be the date datatype in Oracle. There are four recommended solutions to getting past this error. The only one that really applies to us is to use a “cast” in the SQL query.
It is very hard to pin down because the error reported names a column that is not in the query and not in the table being queried, as follows:
Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "EMREP" supplied invalid metadata for column "DELAY". The data type is not supported.
The 7306 error was only encountered once and that was when we were collecting examples of the other errors for Oracle Corporation. They have currently told us to "engage Microsoft" to resolve the issues - which just is not acceptable. I'm sorry but I cannot even find a definition of the 7306 error at the moment.
We have also found out that the duplicate, triplicate and quadruplicate fields issue exists in the "Release Candidate 2" of the CTP for SS 2008 - so apparently it's not going to go away.
Has anyone else encountered these problems? Any solutions or work-arounds found?
Thanks for your help.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
March 26, 2008 at 11:07 am
If you're running 32 bit SQL then...
My experience with linking to an oracle database is to use MSDASQL... especially (MSDASQL only exists in Server 2008 for 64 bit)
I find it the nicest and easiest to use and don't have many problems with it, apart from when I drill down into the catalogs in the gui view that is.
I had so many problems with my 64 bit SQL Server that we actually employ a "hop" server which is basically a 32 bit server with the sole purpose of running the Oracle connections and some of our SSIS. I think I even dug up the solution on this very forum.
March 26, 2008 at 4:20 pm
I opened a TAR with Oracle and they act like there are no problems (which anybody who uses Google knows is not true), so they are playing a dance with me and wasted four days trying to get to the point where they can demonstrate it on their hardware/software, only to discover that it only happens with 32-bit and SP2. Straight 2005 didn't have the issue. They also noted in their feedback that there were similar problems with the MS component... and went on to describe something about a "" (null) server connection. We actually saw that initially and decided the "solution" was to use the Oracle component. It was during my research that I found on the MSDN site supporting beta SS 2008 that the problems still exist.
Anyway, I do appreciate your proposed solution; but, because of the "" issue with the connection, it would fall completely on MS (vs. Oracle support).
In the meantime, I found out we have 24/7 MS support - so I have updated the TAR to ask exactly what it is they want to see from Microsoft... and the Oracle technition has been out sick all week (and didn't work the weekend)... so it's been five days now with no action.
The alternate techy at Oracle refused to touch it... "since Geoff seems to have a handle on it".
Geez!
Thanks again.
David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
March 27, 2008 at 9:33 am
That error you describe with the '"(null)" reported an error' does indeed come up on 2005 SP2 but it should only be on the gui you get this... run a query that calls the oracle linked server and everything should run smoothly.
That error message is exactly what I got as soon as I went SP2 but the actual procs that call the Oracle tables work fine.
March 27, 2008 at 10:20 am
Thank you Mark. That's great information. I was able to test the MS OLE DB today and it works exactly as you state. It simply does NOT work in the Management Studio GUI. When I connected through MS Visual Studio and executed queries they worked perfectly.
I have NOW gone back and used both the Oracle and MS Providers and have six test linked servers. They all operate a little differently, and it's hard to say which one is "best". My notes are not publishable and I may never make a documented comparison; but, Oracle has identified one item as a "bug" and have assigned it to a developer, and tentatively identifed two others as "bugs"; but are still investigating.
I certainly do appreciate your help.
David Russell
San Diego CA
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply