Linked Server in Win 2008 and SQL Server 2005

Question

Post reply

Linked Server in Win 2008 and SQL Server 2005

mathewspsimon

SSCommitted

Points: 1721
More actions
March 19, 2012 at 11:59 pm

#149182

Hi friends,I am trying to create a Linked server to Oracle in a Win 2008 (64bit) server using SQL Server 2005(64 bit).This server is configured to Replication and Clustering too.I installed 64 bit Oracle Client,64 bit ODAC and 32 bit Oracle client.But none of this helped.I dont see the oraoledb.oracle driver in the Linked servers providers list.I am able to connect the Oracle client with the Oracle DB.
Please advice what could be the reason.

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

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

mathewspsimon SSCommitted Points: 1721 More actions · Answer 1

I even tried using the Microsoft driver for ODBC.But I get the following error

TITLE: Microsoft SQL Server Management Studio

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

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

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

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 "MSDASQL" for linked server "SERVER".

OLE DB provider "MSDASQL" for linked server "SERVER" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".

OLE DB provider "MSDASQL" for linked server "SERVER" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

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

BUTTONS:

&Yes

&No

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Once I've used the following guide and it did work for me:

http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mathewspsimon SSCommitted Points: 1721 More actions · Answer 3

I did this but my issue in the Provider Menu Item I dont see oraoledb.oracle

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Have you reboot after install and changing network configuration file?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mathewspsimon SSCommitted Points: 1721 More actions · Answer 5

Friends,the issue was resolved.I had to installed the ODAC(Oracle Data Access client) in the Cluster server also.and then the OraOLEDB.Oracle showed up in the Linked servers Provider.