Creating SQL to Oracle Linked Server

Question

Creating SQL to Oracle Linked Server

apat

SSCertifiable

Points: 5929
More actions
October 25, 2010 at 8:54 am

#230790

Hi All,
I need some help in creating a linked server from SQL Server 2008 to Oracle. I have following details for the oracle server,
Server: test05.ca.smr.com
Port: 1521
SID: test05
User: Linkuser
Passwd: ****
I am new to this, can anyone please help me on how shall I do this?
Thanks in advance...

Viewing 7 posts - 1 through 6 (of 6 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

havent tried with oracle but I use linked server quiet often between sql server and other microsoft product(like excel).

I think for oracle you just need to mention the correct driver as ' ole db provider for oracle'.

More you can read here....

http://support.microsoft.com/kb/280106

----------
Ashish

Lowell SSC Guru Points: 323518 More actions · Answer 2

I edited my saved snippet to have your values;

change the password and see if this works.

note that for this to work, the client tools have to have been isntalled on your SQL server, the TNS names were already updated so from the server itself, you can connect via SQLPlus or some other Oracle tool.

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

--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 Oracle 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'test05' --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'Linkuser',--oracle username

@rmtpassword = 'NotARealPassword' --oracle password

/*

Server: test05.ca.smr.com

Port: 1521

SID: test05

User: Linkuser

Passwd: ****

*/

--list all the tables and their names

EXEC sp_tables_ex 'MyOracle'

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!

apat SSCertifiable Points: 5929 More actions · Answer 3

Thanks for your help Lowell...

I did what you said above and when try to access data I get following error,

Failed to retrieve dta for this request. An exception occurred while executing

a Transact-SQL statement or batch. Cannot initialize the data source of

OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" (Microsoft SQL Server, Error: 7303)

what's going on wrong here?

Lowell SSC Guru Points: 323518 More actions · Answer 4

the oracle Client tools ARE installed on the server, right? you did successfully connect from the server's desktop via SQLPlus, right?

after that, AFAIK, there's three typical reasons for error 7303;

1. i see firewall issues, where the server's firewall prevents connectivity,

according to the MS site:

http://msdn.microsoft.com/en-us/library/aa226395(SQL.80).aspx

2. one of the parameters in the linked server setup is wrong:(data_source, location, provider_string, or catalog) our servers here are sid.domainname in our TNS files...could that be it?

3. The username or password is wrong (you did change "NotARealPassowrd") from my script, right?

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!

apat SSCertifiable Points: 5929 More actions · Answer 5

How do I check that oracle client tools are installed on server or not? I am not using sqlplus. After creating a linked server, I'll just use openquery to fetch data from oracle server. Sorry I am new to this and asking you some basic questions.

Lowell SSC Guru Points: 323518 More actions · Answer 6

apat (10/25/2010)
How do I check that oracle client tools are installed on server or not? I am not using sqlplus. After creating a linked server, I'll just use openquery to fetch data from oracle server. Sorry I am new to this and asking you some basic questions.

you need physical control of the SQL Server. Assuming you are in a more developmental role, you'll need to go sit with the network admin and get him to download and install them. once they are installed on that physical server, connectivity needs to be tested via the Oracle tools...TNSnames.ORA and other files are usually copied from an existing installation to a new installation in our shop, so I bet that's the case.

that's a big prerequisite before you can add a *working* linked server....you gotta be able to connect before you can use a linked server .

also, depending on your version, you might need 32 bit or 64 bit drivers from oracle...if your SQL is 64 bit, you need the newer 64 bit drivers from Oracle.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

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!