Oracle Linked Server

  • 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

  • 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!

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

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