October 14, 2002 at 9:47 pm
I'v setuped a Oracle8.16 linked server througth oracle oledb provider:
OraOLEDB.Oracle. When I use this linked server to get data, SqlServer
gave the following error message:
"OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error."
But when I change the OraOLEDB.Oracle provider to MSDAORA,
none error occured!
Can somebody pls. guide me to resolve the issue?
many thanks in advance?
October 15, 2002 at 2:12 am
There is a registry key fix you will need to do. See books online for details (search for
'ole db provider for oracle') for details.
Also make sure the tns files are correct, and the linked server security is valid
Steven
October 15, 2002 at 3:56 am
That fix pertains to using the Microsoft OLEDB for Oracle driver and not the Oracle OLE driver. The meesage pump from Oracles driver has a huge tendency with this error. Generally run it two or three times in QA and it will give you the correct error. Odds are it is syntax related, but you need to get the valid message to see for sure what it requires of you. I usually also test the query in SQL*PLUS to see if I get an error. If not then consider how you are submitting your query to the Oracle server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 16, 2002 at 12:34 am
I found when the size of the return
rowset was less than 100, my query would
get right result,but when the size was over
100,Sql Server will give me the following error message:
"OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error."
My query is as following:
select * from openquery(oraLinkedServer,'select * from mytest')
Edited by - my liu on 10/16/2002 12:35:37 AM
October 16, 2002 at 4:10 am
Just for the sake of testing the problem try it instead with the TOP x syntax both 100 or less and higher than 100 to see if the same issue occurrs. If so then try skipping around to see if you can lock which record is the potential issue like so 100, 110, 120, 105, 108. Whne the issue is occurring run the query several times also to get the actually error message from the driver. Couple of other things, what version of SQL Server and Service Pack level? What is the structure of your Oracle table? Try testing each colum from Oracle individualy to see which causes the error, if any particular ones due there were known issues with SQL 7 before SP2 that required you to use TO_CHAR and TO_DATE to get the problem to go away, if any column appears to be the problem try the appropriate TO_xxx item to see if it fixes.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply