April 28, 2008 at 1:31 pm
First I am working in a virtual environment with 2 virtual servers that were created with exactly the same specs.
Server 1 I am able to create my Oracle Linked Servers and use them with no problems. I am using the Microsoft OLE DB Provider for Oracle.
Server 2 I have the following issues:
When using the Microsoft OLE DB Provider for Oracle I I get the following error when I try to test or create the Linked Server: "Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "TEST". OLE DB provider "MSDAORA" for linked server "TEST" returned message "Oracle error occured, but error message could not be retrieved from Oracle." Microsoft SQL Server, Error 7303)
When I use the Oracle Provider for OLE DB I get a series of different errors depending on what I'm doing and where I'm calling the query from.
If I try to do a query remotly (ie from my desktop) I get the following error if I use OpenQuery or try to use a 4 part name.
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL816n".
Now if I RDP to the server I get the following errors:
If I do a query using a 4 part name I get
OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL816n" returned message "Table does not exist.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL816n". The provider supports the interface, but returns a failure code when it is used.
If I use an openquery I get the first row of the table and the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL816n" reported an error. Provider caused a server fault in an external process.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL816n".
Does anyone have any idea what might be going wrong? As I said earlier I have a server with the exact same specs (server 2 is a clone of server 1) that works. The SQL Server and Oracle client installs were done seperatly but I know for sure that the SQL Server installs were done the same. I can't be entirly sure about the Oracle install though.
Thanks for any help
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 28, 2008 at 1:52 pm
Check the Oracle installs and make sure your TNSNames configuration is the same.
Also, since you copied a virtual machine, make sure there is not a DNS conflict of some sort. Oracle clients can be sensitive about IP and MAC addresses. Make sure you don't have something dumb like the clone has the same IP address as the original machine.
I would assume they are on the same subnet, but if you clone was moved to another subnet you may have a transport error happening - you may want to use IP addresses in your TNSNames configuration.
April 28, 2008 at 3:35 pm
Unfortunatly same subnet, different IP addresses. Also same TNS files. We copied them from the working server.
Any other suggestions?
Thanks for the help BTW
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 29, 2008 at 6:49 am
Are you accessing the oracle server with OPENQUERY or by using a 4 part name?
Try both - this may clue you into the issue. If openquery works but 4 part name queries do not, it could be that the ODBC driver is running out of memory. This could mean that you need to use the -g startup parameter for SQL Server to allocate more memory to non-SQL processes within SQL Server.
If neither works, I would completely uninstall the Oracle client and reinstall it. Make sure you reboot afterwards.
May 17, 2008 at 4:37 pm
You mentioned that the linked server on one of them is working. Could you describe what arguments you used for making them work? I am trying from SQL Server 2008 and I am using a Oracle 10G XE. I could easily connect to it from VS Studio (both 2005 and 2008) using OE DB. However with the same connection information I cannot establish a linked server. I get an Oracle error that makes no sense. For a perfectly acceptable pair of login information it comes out with 'invalid login'.
I appreciate if you would clarify.
Jayaram Krishnaswamy
June 9, 2008 at 6:33 am
Did you configure the provider?
In SQL server enterprise manager go to "Server objects" - "Linked servers" - "providers". Then enable "Allow inprocess". Restart SQL-server instance.
June 9, 2008 at 7:01 am
This was resolved and I posted what I found to one of Oracle's forums. Thanks for the suggestion.
June 11, 2008 at 2:15 pm
Could you please post the link to the Oracle forum? It might help others with a similar problem. Thanks.
June 11, 2008 at 3:31 pm
I think this was the thread..
http://forums.oracle.com/forums/message.jspa?messageID=2530772#2530772
August 13, 2009 at 8:15 am
Could you please post the solution in this thread too? It sure helps. Thanks!!
April 15, 2010 at 9:10 am
Articles that may help:
http://support.microsoft.com/kb/906954
more specifically the 64bit sql2005 system is looking for this system stored proc in the 32bit sql 2000 system.
May 5, 2011 at 9:42 am
I've had the same issue and for me it was all about the path to the DLL's not being found (%PATH% variable) and the tnsnames.ora not being found. I concluded that via procmon and documented my albeit hated adventure below:
I've used this thread as the starting point as it was google ranked #1 for
Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server
July 15, 2011 at 3:33 pm
The suggestion re enabling "Allow inprocess" on the provider solved our problem, thanks!
We enabled the following options on the OraOLEDB.Oracle service provider and restarted the database instance:
– Dynamic parameter
– Nested queries
– Allow inprocess
– Supports "Like" operator
Afterwards the connections succeeded.
January 16, 2013 at 5:33 am
First: It was a while ago someone posted in this thread, perhaps I should make a new one but anyways:
I'm having a problem reading active directory via linked server
There is a 10 000 row roof I can't get past. So I did a work-around that worked in one environment, but not in the current one. So I set a WHERE clause with
AND sAMAccountName = ''A*''
and runs several of them SELECTs with ="A*" , ="B*", ="C*" etc to get all of the AD records.
But - for some of the letters (B, W, X) the queries are getting the error with message:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
I'm leaning on that it is some kind of strange chars in some of the rows, that can't be read thru T-SQL/linked server/Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any suggestions in what it can be that causes this or tip on other work arounds.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply