Oracle Linked Server

Question

Post reply

Oracle Linked Server

Sandhya-371593

SSC Eights!

Points: 817
More actions
May 11, 2009 at 4:24 am

#384556

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

JamieX Hall of Fame Points: 3763 More actions · Answer 1

Is it installed??

64bit SQL?

http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!