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 12»»

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
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
Post #1396747
Posted Sunday, December 16, 2012 6:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 422, Visits: 2,647
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.
Post #1397041
Posted Monday, December 17, 2012 11:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
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
Post #1397359
Posted Monday, December 17, 2012 3:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--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 #1397448
Posted Monday, December 31, 2012 3:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 422, Visits: 2,647
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.
Post #1401516
Posted Tuesday, January 1, 2013 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--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 #1401616
Posted Sunday, May 12, 2013 6:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
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".
Post #1451956
Posted Monday, May 13, 2013 9:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
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.
Post #1452383
Posted Monday, May 13, 2013 10:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
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
Post #1452391
Posted Thursday, September 26, 2013 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 12:36 PM
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?
Post #1498907
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse