6

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

At first I thought I'd just generate the tables in Oracle as SQL, manipulate the data formats, and run the query in SQL Server. This worked for small tables, but I have several tables with a few hundred thousands rows and some with millions of rows, so this solution won't work.

I then created a TNS file with the following content:

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

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

I then 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 the Oracle database from SQL Server using openquery:

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

But get an error:

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

When I check the properties of the Linked Server, and just click the OK, I get this error:

TITLE: Microsoft SQL Server Management Studio Express

"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"

ADDITIONAL INFORMATION:

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

The OLE DB provider "MSDASQL" for linked server "OracleLinkServer" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "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

mikimr
  • 311
  • 3
  • 8
  • 18
  • Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jul 03 '12 at 11:22
  • Whats the bitness of the driver and the SQL Server? If you only installed a 32 bit driver and you are running a 64 bit SQL Server that would explain it. – Nick.Mc Mar 25 '14 at 08:43
  • Con you confirm you installed the Oracle client and TNS file on the SQL Server? You then need to test the Oracle client using oracle tools like `TNSPING` and `SQLPLUS`. You then need to confirm that the bitness matches. I suggest that instead of all that you export the Oracle data as a flat file, copy it over and import it that way, because the Oracle driver can be very unfriendly – Nick.Mc Aug 24 '14 at 09:43
  • What happens if you just select a single column rather than `*`? (Preferably something simple like an integer) Or pick out a particular row with a `WHERE` clause? Sometimes bad data in a column can cause linked server code to choke—I've seen in particularly with Oracle dates that are outside SQL Server's valid date ranges. – Matt Gibson Jun 12 '15 at 06:19

3 Answers3

1

If you have successfully added your linked server, you no longer need OPENQUERY. You can just include the linked server name as the first part of the qualified name like so:

SELECT * FROM OracleLinkServer.database.schema.table

Not sure which parts you need, but the dots are key. Try this first:

SELECT * FROM OracleLinkServer...oracleTable
David M
  • 71,481
  • 13
  • 158
  • 186
0
select * 
from [server]..[xxx].[yyyyy]

It works for me.

slavoo
  • 5,798
  • 64
  • 37
  • 39
kam0200
  • 11
0

Change 

,@provider = 'MSDASQL'

with

,@provider = 'MSDAORA'
Al Fes
  • 1