SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
APA0876
APA0876
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 400
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
SQLisAwE5OmE
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 3068
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
APA0876
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 400
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28227 Visits: 39955
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
SQLisAwE5OmE
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 3068
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28227 Visits: 39955
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
niall.baird
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 484
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
nick.mcdermaid
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 778
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
niall.baird
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 484
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
pdcjlw1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 10
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search