Geek Answers Handbook

Query Oracle Database from SQL Server

I have an Oracle 11g XE database that I would like to transfer to SQL Server Express 2005.

At first it seemed to me that I was just creating tables in Oracle as SQL, managing data formats and running a query in SQL Server. This worked for small tables, but I have several tables with several hundred thousand rows, and some with millions of rows, so this solution will not work.

Then I created a TNS file with the following contents:

OracleTnsName = ( DESCRIPTION= ( ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521) ) ( CONNECT_DATA = (SERVICE_NAME=XE) ) )

I followed the instructions I found elsewhere on how to generate an ODBC connection, and the "test connection" was successful.

Then I ran these commands to create a Linked Server in MS SQL:

 EXEC sp_addlinkedserver @server = 'OracleLinkServer' ,@srvproduct = 'OracleTnsName' ,@provider = 'MSDASQL' ,@datasrc = 'OracleTnsName' EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OracleLinkServer' ,@useself = 'False' ,@locallogin = NULL ,@rmtuser = 'user' ,@rmtpassword = 'password'

Now I'm trying to query a table in an Oracle database from SQL Server using openquery :

 select * from openquery(OracleLinkServer, 'select * from oracleTable')

But get the error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for the linked server "OracleLinkServer" reported an error. The supplier did not give any error information.
Msg 7303, Level 16, State 1, Line 1
Unable to initialize OLE DB provider data source object "MSDASQL" for linked server "OracleLinkServer".

When I check the properties of the linked server and just click OK, I get this error:

TITLE: Microsoft SQL Server Management Studio Express

"The linked server is updated, but the connection check failed. Do you want to change the properties of the linked server?"

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

The OLE DB provider "MSDASQL" for the linked server "OracleLinkServer" reported an error. The provider did not provide any error information. Unable to initialize OLE DB provider data source object "MSDASQL" for linked server "OracleLinkServer". (Microsoft SQL Server, Error: 7399)

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

BUTTONS:

& Yes

& No

Please, help!

thanks

+4
source share
3 answers

If you have successfully added the linked server, you will no longer need OPENQUERY . You can simply include the name of the linked server in the first part of the qualified name:

 SELECT * FROM OracleLinkServer.database.schema.table

Not sure which parts you need, but the points are key. First try:

 SELECT * FROM OracleLinkServer...oracleTable
+1
source 
 select * from [server]..[xxx].[yyyyy]

This works for me.

0
source

Change

 ,@provider = 'MSDASQL'

from

 ,@provider = 'MSDAORA'
0
source

More articles:


All Articles