How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager

Question

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager

APA0876

Hall of Fame

Points: 3853
More actions
December 14, 2012 at 10:27 am

#266248

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager
I'm reading some articles and applying them but with no result.
First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exe
Thanks for your help, this is driving me crazy.
Thanks again
APA

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 1

APA0876 (12/14/2012)
How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager
I'm reading some articles and applying them but with no result.
First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exe
Thanks for your help, this is driving me crazy.
Thanks again
APA

You mean, you want to set up a linked server which connects to Oracle from SQL server right?

SueTons.

Regards,
SQLisAwe5oMe.

APA0876 Hall of Fame Points: 3853 More actions · Answer 2

Yes, That is what I exactly want.

What I meant was - Install Oracle client driver to be able to create a linked server using an a system DSN previously created using that driver.

Thanks I'm looking forward to hear something from you regarding this.

Thanks again

Lowell SSC Guru Points: 323518 More actions · Answer 3

it starts out pretty much like every other desktop install around the office that is connecting to Oracle.

Use the oracle universal installer to install the client tools. See your It guys for it, it's a pretty big item to redownload.

next you need the 64 bit drivers as well, if they are not part of the universal install(our installer seemed to only do the 32 bit, but it's been a while since i needed to install again)

the 64 bit ones found here:

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

in our office, we copy the TNSNAMES.ORA file and the SQLNET.ORA files from an existing installation (ie C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN) o the matching \NETWORK\ADMIN folder on the server that was created after installing the drivers.

once that is set up, and using the tools like SQLDeveloper or SQLPlus to prove we can connect, via those programs to the Oracle Instances, it's just a matter of setting up the linked seerver.

here's my working linked server example code:

--#################################################################################################

--Linked server Syntax for Oracle 10G / 11

--#################################################################################################

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'

--EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'

GO

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!

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Hi Lowell,

I don't exactly follow all the steps you mentioned above.

Can you please provide step by step for example, I want to create a

Linkedserver from SQL to Oracle.

What are the steps involved from beginning to end and do I need to contact

Oracle team to accomplish this?

Thanks in advance.

SueTons.

Regards,
SQLisAwe5oMe.

Lowell SSC Guru Points: 323518 More actions · Answer 5

SQLCrazyCertified (12/31/2012)
Hi Lowell,
I don't exactly follow all the steps you mentioned above.
Can you please provide step by step for example, I want to create a
Linkedserver from SQL to Oracle.
What are the steps involved from beginning to end and do I need to contact
Oracle team to accomplish this?
Thanks in advance.
SueTons.

SueTons you've got to establish basic connectivity to the Oracle Server(s) on the SQL server itself just like any other desktop that connects. That means using Oracle tools, before you can can create a linked server that uses that connectivity. that's step one, and you certainly could get the Oracle team involved; they probably do the same step to every new machine that gets added to your company.

It will absolutely require the Oracle Client tools installation from the Universal installer.

once you've got that, editing the linked server example should be very straight forward.

let us know where you are at so we can offer some better suggestions.

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!

niall.baird Hall of Fame Points: 3468 More actions · Answer 6

There is more than one person watching this...I'm trying to work out what the "SID" is.

Running the script gives me this:

OLE DB provider "MSDAORA" for linked server "MyServerName" returned message "ORA-12154: TNS:could not resolve the connect identifier specified

".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "MyServerName".

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 7

ORA-12154: TNS:could not resolve the connect identifier specified

Means you have not established basic client connectivity (as mentioned) before trying to create your linked server.

You'll need to do some Oracle research on how to do this, or you can post a specific question.

If you search on the ORA-12154 message above, you' should find many guides on how to use Oracle tools to troubleshoot connectivity.

niall.baird Hall of Fame Points: 3468 More actions · Answer 8

Thanks. I d did manage to solve it. Apparently I had a mismatch between the server name in my tnsnames.ora file and the server name in my script.

All solved by the forum again

Thanks

Niall

pdcjlw1 Grasshopper Points: 15 More actions · Answer 9

I am working with a linked server from SQL to Oracle. I am getting this message when I try to execute any SQL from SQL server to oracle.

Msg 7308, Level 16, State 1, Procedure UFN_SRBRECR_LIST, Line 7

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Can you help me understand what I need to do to fix this?

APA0876 Hall of Fame Points: 3853 More actions · Answer 10

Hello,

Check this link, where they have a similar situation:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dfd61545-1cb3-4353-9d70-b93387f65c8c/after-sql-2005-sp2-query-to-apartment-linked-servers-fails