SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem setting up linked server


Problem setting up linked server

Author
Message
Kenneth Fisher
Kenneth Fisher
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8864 Visits: 2096
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 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
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21543 Visits: 23078
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.
Kenneth Fisher
Kenneth Fisher
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8864 Visits: 2096
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 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
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21543 Visits: 23078
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.
Jayaram Krishnaswamy
Jayaram Krishnaswamy
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 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
w.loetzsch
w.loetzsch
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
Jayaram Krishnaswamy
Jayaram Krishnaswamy
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 48
This was resolved and I posted what I found to one of Oracle's forums. Thanks for the suggestion.
DBA_Rob
DBA_Rob
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 360
Could you please post the link to the Oracle forum? It might help others with a similar problem. Thanks.
Jayaram Krishnaswamy
Jayaram Krishnaswamy
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 48
I think this was the thread..
http://forums.oracle.com/forums/message.jspa?messageID=2530772#2530772
pruthvi116
pruthvi116
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 358
Could you please post the solution in this thread too? It sure helps. Thanks!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search