Yet another linkserver problem ....

Question

Yet another linkserver problem ....

Opus

Default port

Points: 1419
More actions
May 3, 2010 at 2:45 pm

#234477

Environments:
1: Windows Server 2003 32-bit; MSSQLServer 2005 (both with all apppropriate SP's apppled); Oracle Client 10.2.0 32-bit
2: Windows Server 2003 64-bit; Oracle 10 64-bit
On machine where I am trying to create the linkserver, the tnsping and sqlplus are working without error. When I try the test connection to the linkserver, I get:
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PSPRODDB".
OLE DB provider "MSDAORA" for linked server "PSPRODDB" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
". (Microsoft SQL Server, Error: 7303)
------------------------------
I've been searching forums for days and things appeared to be configured properly (as far as I can tell). Any ideas or direction?
EXEC master.dbo.sp_addlinkedserver @server = N'PSPRODDB', @srvproduct=N'Oracle', @provider=N'MSDAORA', @datasrc=N'MSDAORA', @provstr=N'PSPRODDB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PSPRODDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'ps',@rmtpassword='########'

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

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

This looks like an oracle error trying to resolve the TNS name.

Have you installed the Oracle tools/client? Its a long time since I have used/setup an oracle client, but if i recall there was a file that containt the details of the connection.

Opus Default port Points: 1419 More actions · Answer 2

TNSNAMES.ORA and SQLNET.ORA appear to be configured properly.

--------------------------------------------------------------------------------------

# sqlnet.ora Network Configuration File: E:\oracle\product\10.2.0\client_1etwork\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

--------------------------------------------------------------------------------------

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

# Generated by Oracle configuration tools.

#PSPRODDB =

# (DESCRIPTION =

# (ADDRESS_LIST =

# (ADDRESS = (PROTOCOL = TCP)(HOST = 299.199.99.99)(PORT = 1521))

# )

# (CONNECT_DATA =

# (SERVICE_NAME = PSPRODDB)

# )

PSPRODDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dc-dbps-01.wccd.washk12.org)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PSPRODDB)

)

-------------------------------------------------------------------------------------------

Lowell SSC Guru Points: 323518 More actions · Answer 3

Opus it's been a while since i added an oracle linked server; here is the syntax i've used, i put in your server and username to what i think is right:

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

--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 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'PSPRODDB' --this is the SID/Service Name

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'ps',--oracle username

@rmtpassword = 'NotARealPassword' --oracle password

--list all the tables and their names

EXEC sp_tables_ex 'MyOracle'

GO

EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'

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!

Opus Default port Points: 1419 More actions · Answer 4

FINALLY!!! I found I was logged into the SQL Server using Windows authentication. My create linkserver script, the @locallogin was set to 'sa'. Once I disconnected to the SQL Server and reconnect as 'sa', the test linkserver worked. The smallest things really kill you sometimes.

Thanks Lowell!