Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.].
Searching on this error I get tons of hits, I have read through them all and tried every posted suggestion, however I still am unable to get this to work.
The strangest thing is that I CAN browse the tables on the linked Oracle server from within Enterprise manager, I only get the error message when I try to use Query Manager to do a select... ie select * from LINKEDSVR..OWNER.TABLE
I know this problem has been discussed a bunch so am hoping someone who has been through it can help out here. I have other SQL Servers that can connect to and run that same select statement against the same Oracle database. I've compared the two SQL servers and everything seems identical. I'm at my wits end here!
SQL2000, Service Pak 4 on Windows Server 2003/Oracle 8.1.6 on Solaris
Thanks!
SQL2000 Linked Server to Oracle
MagicDust
I agree 100%.
It is especially annoying when an error "explanation" sticks you in a dead end (identifies the error but provides no recourse for solution) or provides just tanatalizing info that explains the error very well but offers up a one-line "solution" that is too cryptic for many users to follow.
I think every issue like this should spawn a wizard that provides hyperlinks to KB and other resources as well as providing ROBUST directions to the program menu commands that will get you to a fix. I hate instructions such as "you need to run such-and-such" and I have no idea how to even get to that command or resource. MS is way too "good" at doing that.
C T Ranjith
HTH, jens Suessmeyer.
MGrant
Try to evaluate if the security context if different if you try to connect from the enterprise manager and the QA, perhaps you are using a usermapping which isn’t valid and therefore you get an ORA error about security Try to select a more insensitive table like dusl (Select * from dual) This one is accessible to everyone who was able to connect to Oracle. If this won’t work you assumably have a security issue.
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
bendada123
Ok, I ended up opening a support case with Microsoft. Because I had another server which was working we were able to compare the too (something I had already done previously). All the providers where the same version number and all settings were identical. The only difference in the two machines was that the one that worked was SQL2000 with SP3, and the one that didn't work had SP4.
So, to cut right to the chase we got it to work! Here's how...
In the registry under HKEY_LOCAL_MACHINE - Software - Microsoft -MSSQLServer - Providers - MSDAORA there are 7 keys. On the SP4 machine all 7 were set to '1' where the SP3 had all but two set to '0'. I made the change on the SP4 machine, didn't even have to reboot and the query's now work.
Nice to have it working!
Andrew Webb
Hi.
I have the same problem. Please tell us which 2 setings are set to 0.
Thanks very much
MikeC06
The only two that are set to '1' are AllowInProcess and NestedQueries. Everything else is set to '0'
depkain
Thanks for the reply Jens. In trying what you suggest I've discovered it doesn't matter what table I try to select from it always gives the same error. Even if I put a table name that doesn't exist it gives the same error, which makes me think it's not even communicating with Oracle.
Everything else communicates fine, SQL Plus, MS SQL Enterprise Manager, Net8 Easy Config, etc. But as soon as I try to run MS SQL QA it bombs with the very helpful error message...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.].
Does anyone think opening a support case with Microsoft will help with this We need to get it fixed quickly but I don't want to waste my time on the phone with them if they are just going to search the same resources I've already looked at to try and solve the problem.
Thanks
Khawar
This comment is generally about error messages, not necessarily from SQL Servers.
Some of the same errror messages are returned from SQL Servers from verison 6.5 through 2005. Is it too much to ask the software designers to provide more helpful messages The same thing with the wizards. I believe a good wizard should be able to guide till the user gets / finds an answer. Some of the error messages span pages and pages in the internet that you become sick and tired. A web service should be started which takes in a error message and guides the user to the ONLY master source where every ramification of that error message is described and a fix is given. There may be KB articles for this one and that, but what I want is a comprehensive one time fix.