Problem setting up linked server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Problem setting up linked server

14 posts, Page 1 of 2

1 2 »»

Problem setting up linked server

Rate Topic

Display Mode

Topic Options

Author

Message

Kenneth.Fisher

Posted Monday, April 28, 2008 1:31 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #491682

Michael Earl-395764

Posted Monday, April 28, 2008 1:52 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #491697

Kenneth.Fisher

Posted Monday, April 28, 2008 3:35 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #491757

Michael Earl-395764

Posted Tuesday, April 29, 2008 6:49 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #492043

Jayaram Krishnaswamy

Posted Saturday, May 17, 2008 4:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:32 PM
Points: 30, Visits: 48

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

Post #502550

w.loetzsch

Posted Monday, June 09, 2008 6:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 05, 2008 4:09 AM
Points: 1, Visits: 3

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.

Post #513659

Jayaram Krishnaswamy

Posted Monday, June 09, 2008 7:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:32 PM
Points: 30, Visits: 48

This was resolved and I posted what I found to one of Oracle's forums. Thanks for the suggestion.

Post #513682

DBA_Rob

Posted Wednesday, June 11, 2008 2:15 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:26 PM
Points: 469, Visits: 256

Could you please post the link to the Oracle forum? It might help others with a similar problem. Thanks.

Post #515494

Jayaram Krishnaswamy

Posted Wednesday, June 11, 2008 3:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:32 PM
Points: 30, Visits: 48

I think this was the thread..
http://forums.oracle.com/forums/message.jspa?messageID=2530772#2530772

Post #515549

pruthvi116

Posted Thursday, August 13, 2009 8:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 3, Visits: 260

Could you please post the solution in this thread too? It sure helps. Thanks!!

Post #770191

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions