Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Oracle Linked Server Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 4:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, September 13, 2014 12:26 PM
Points: 223, Visits: 208
I have been asked to create a linked server on the SQL 2005 Instance which is hosted on a Windows cluster Server.

We have the Oracle client version 10.2.3 installed on both the nodes of the cluster and the TNSNames.ORA in the path d:\oracle\oracle10203etwork\admin\ updated with the entries required to connect to the Oracle server.

Configured the linked server and when I try to query the tables, getting the following message:

Server: Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" has not been registered.

I do not find the MSDASQL.dll created in the server. Do I need to get any drivers installed here?

Many thanks

Sandhya
Post #713959
Posted Monday, May 11, 2009 6:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
Points: 371, Visits: 437
Is it installed??

64bit SQL?
http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
Post #714037
Posted Monday, May 11, 2009 7:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
ive always used the oracle driver from the client install, but the previous poster is right...you might need to install the driver first:

SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver

here's my code for my linked oracle server:
--#################################################################################################
--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
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 = 'NotARealPassword' --oracle password

--list all the tables and their names
EXEC sp_tables_ex 'MyOracle'
GO
--EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'

--list all the tables and their names
EXEC sp_tables_ex MyOracle
GO
select * from MyOracle..CHANGE_ME.SOMETABLE
select * from MyOracle..MFHDS.SOMETABLE



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #714059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse