Linked server

Question

Post reply

Linked server

pzmrcd

Default port

Points: 1419
More actions
January 27, 2009 at 3:08 pm

#204552

Hi
I am trying to create a linked server on SQL server 2005 to Oracle..
Tried the following..
EXEC sp_addlinkedserver
@server = 'Mktg',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'TestServer'
GO
..it runs with our any error but when I try to look for tables/views nothing shows up but for an error msg.." Cannot initialize the database object of OLEDB provider 'MSDAORA' for linked server 'Mktg'.

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

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

1) What Service Pack have you got on your SQL Server?

2) What version of the Oracle Client did you install on the SQL Server?

3) What version of is the Oracle Server?

4) Has an Oracle Linked Server ever worked on this SQL Server prior to this error?

Kindest Regards,

liebesiech SSCrazy Points: 2610 More actions · Answer 2

Setting up a linked ORACLE server sounds pretty easy but there are some things to consider:

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

If you havn't installed to ORACLE client get it here:

http://www.oracle.com/technology/software/products/database/index.html

Install it on your server where the SQL instance is running and make sure the tsnames.ora file is found (certain information depends on the installed version):

DriveLetter:\oracle_home\ora92\NETWORK\ADMIN\tnsnames.ora

On my machine it looks like this:

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora

Define an environment variable ORACLE_HOME which points to the directory where ORACLE is installed:

Set ORACLE_HOME=C:\oracle\product\10.2.0\client_1

Some more readings on that:

http://www.orafaq.com/wiki/ORACLE_HOME

http://www.orafaq.com/wiki/Tnsnames.ora

Add your linked server:

EXEC sp_addlinkedserver

@server = 'Mktg',

@srvproduct = 'Oracle',

@provider = 'MSDAORA',

@datasrc = 'TestServer' <--This entry must match the entry in tsnames.ora otherwise you get a ORA-12154 error

GO

Start the ORACLE Net Manager and configure the services. This will create the tsnames.ora file.

tsnames.ora contains the connection information to your ORACLE Server(s):

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.

TestServer=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = yourORACLEServerHOSTName)(PORT = 1521)))

(CONNECT_DATA =

(SID = TestServer)))

Finaly add the login:

exec sp_addlinkedsrvlogin 'Mktg', false, null, 'username', 'password'

or if you have to drop it

exec sp_droplinkedsrvlogin 'Mktg', 'username'

Get all your linked servers:

exec sp_linkedservers

Watch out when your query returns numeric information! Casting might be necessary due to numeric format conversion.

http://www.simple-talk.com/community/forums/thread/18991.aspx

SELECT CAST(NUMBERDATA as NUMERIC(12,2)) as CONVERTED FROM OPENQUERY(LinkedORACLEServerName,'SELECT * FROM SCHEMA.TABLE)

Hope this was a little bit of help.

dpmac66 SSC Rookie Points: 47 More actions · Answer 3

Hi all

I have a similar problem I need to check and see if the linkedserver is in fact connected. I am trying to just do a select statement from an Oracle database with 20 records and it never finishes and when I have to stop the process it locks up the server. So I need to error handle if I am connected to the server and quit if there is a problem.

Thanks

Dan