April 6, 2009 at 9:00 am
Hi,
I am having a 4 node cluster on which couple of instance are there. The version of SQL Server is 2005 64 bit EE with SP2 and hotfix 3161. I have installed the Oracle 11 g client on all the nodes and make the TNS entry for that. Also I tested the connectivity with Oracle box using SQLPLUS. Now I need to add this oracle box under linked server to access the data from Oracle box on one of the SQL instance. When I am going to add it using GUI mode it is not showing me the provider for Oracle. When I trying to register it using query the systax executed success, but when I am trying to access the data it is showing the error. Please suggest.
While adding it thru GUI it is not showing me the provider for Oracle. But when I am adding it using query it is added as a linked server, but when I exploring the objects from the linked server it is showing me the below error.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "DWPLRP01". (Microsoft SQL Server, Error: 7302)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3161&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks,
Shekhar
April 6, 2009 at 9:11 am
I am having a 4 node cluster on which couple of instance are there. The version of SQL Server is 2005 64 bit EE with SP2 and hotfix 3161. I have installed the Oracle 11 g client on all the nodes and make the TNS entry for that.
You just said you are running Oracle 11g client so what is the actual Oracle version you are running 8i,9i,10g or 11g. And what is the Microsoft operating system Win2003 or Win2008. And you did not add if you are using Oracle OLE DB driver because that is usually better for such operations than the Microsoft OLE DB driver.
Kind regards,
Gift Peddie
April 6, 2009 at 9:17 am
The Windows version is 2003 server with SP1. I am connecting to Oracle 11g Database server and I already installed the Oracle 11g client software on all nodes. I tested the Oracle connectivity with the SQLPLUS and it is working fine for me. But I need to add this Oracle box as a Linked server in SQL and for this on other servers I am able to see OLDDB Provider for Oracle but on this box no option is coming as OLEDB provider for Oracle. So because of this I am not able to add the server. Also in ODBC admin to add the System or User DSN it is showing me only two entries as SQL Server and SQL Native Client.
Thanks,
Shekhar
April 6, 2009 at 9:22 am
i know there are issues with getting a 64 bit drivers;
outside of that, I've always used a script to add my oracle linked server.
this syntax might help you, since it trys to read some tables right away.
--#################################################################################################
--Linked server Syntax for Oracle 10G
--#################################################################################################
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
--add an access Database as a linked server
SET @server = N'MyOracle' --this is your ALias
SET @srvproduct = N'Oracle'
SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver
SET @datasrc = N'SFMN10G' --this is the SID from tnsnames.ora, sometimes our tnsnames is Server.Domain
set @provstr = ''
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
-- exec sp_dropserver AccessDb
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',
@useself = N'FALSE',
@locallogin = 'sa',
@rmtuser = N'CHANGE_ME',--oracle username
@rmtpassword = 'whatever' --oracle password
/*
exec sp_addlinkedsrvlogin @rmtsrvname='AccessDb',
@useself='false',
@rmtuser='Admin',
@rmtpassword=''
*/
--list all the tables and their names
EXEC sp_tables_ex 'MyOracle'
GO
exec sp_addlinkedsrvlogin @rmtsrvname='AccessDb',
@useself='false',
@rmtuser='Admin',
@rmtpassword=''
*/
--list all the tables and their names
EXEC sp_tables_ex MyOracle
GO
select * from MyOracle..CHANGE_ME.FIRSTTABLE
select * from MyOracle..MFHDS.SECONDTABLE
--EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'
Lowell
April 6, 2009 at 9:24 am
You are missing Win2003 sp2 because it fixed many MDAC issues which included many driver issues because I think Oracle 11g client was developed for Win2003 sp2 boxes.
Kind regards,
Gift Peddie
April 6, 2009 at 9:44 am
I added the linked server using your query but when trying to explore the objects from linked server it is giving me the below error.
Msg 7302, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot create an instance of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle".
April 6, 2009 at 9:50 am
sounds like gift Peddie's comments might be right then: upgrading a service pack to get the MDAC and drivers updated;
dunno if it makes a difference, but in my shop we had to install the client utilities on the server, as well as updating the tnsnames.ora and the sqlnet.ora files so we can create linked servers.....that is exactly what you are doing as well, right?
Lowell
April 6, 2009 at 9:54 am
Rahul,
You need Win2003 sp2 and the Win2003 OLE DB driver which is also a separate download and you must know when developing with Microsoft platform connection to SQLPlus is not relevant. Your TNSNAMES.ORA is very important it must be in all your servers.
Kind regards,
Gift Peddie
April 6, 2009 at 11:12 am
I have installed the Oracle 11g client on all the nodes on the cluster and the binaries exist on the same location on all nodes. Also I make the TNS entry on all the nodes. and I checked the connectivity from all the nodes to the oracle box. Also I am having the Windows 2003 with SP2, I didnt check it earlier so I mentioned the wrong SP in my earlier reply.
April 6, 2009 at 11:29 am
That is the current Microsoft OLE DB driver for x64 boxes so you need to install it in all your Win2003 instances in all your nodes.
Kind regards,
Gift Peddie
April 6, 2009 at 11:29 am
Rahul..i had the similar issue...when using 64 bit sql server edition for linked servers you will need oracle 10g 64 bit drivers for creating linked servers..and 11g 32bit drivers for DTS and SSIS...let me know if this works..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply