title: Querying an Oracle database from SQL Server
body: <p>I have an Oracle 11g XE database that I would like to transfer into SQL Server Express 2005.</p>
  
  <p>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.</p>
  
  <p>I then created a TNS file with the following content:</p>
  
  <pre><code>OracleTnsName = 
  (
    DESCRIPTION=
    (
      ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521)
    )
    (
      CONNECT_DATA = (SERVICE_NAME=XE)
    )
  )
  </code></pre>
  
  <p>I followed instructions I found elsewhere on how to generate the ODBC connection, and the 'test connection' was successful.</p>
  
  <p>I then ran these commands to create a Linked Server in MS SQL:</p>
  
  <pre><code>EXEC sp_addlinkedserver 
       @server            = 'OracleLinkServer'
      ,@srvproduct        = 'OracleTnsName'
      ,@provider          = 'MSDASQL'
      ,@datasrc           = 'OracleTnsName'
  
  EXEC sp_addlinkedsrvlogin 
       @rmtsrvname        = 'OracleLinkServer'
      ,@useself           = 'False'
      ,@locallogin        = NULL
      ,@rmtuser           = 'user'
      ,@rmtpassword       = 'password'
  </code></pre>
  
  <p>Now I'm trying to query a table in the Oracle database from SQL Server using <code>openquery</code>:</p>
  
  <pre><code>select * from openquery(OracleLinkServer, 'select * from oracleTable')
  </code></pre>
  
  <p>But get an error:</p>
  
  <blockquote>
    <p>Msg 7399, Level 16, State 1, Line 1<br>
    The OLE DB provider "MSDASQL" for
    linked server "OracleLinkServer" reported an error. The provider did
    not give any information about the error.<br>
    Msg 7303, Level 16, State 1, Line 1<br>
    Cannot initialize the data source object of OLE DB provider
    "MSDASQL" for linked server "OracleLinkServer".</p>
  </blockquote>
  
  <p>When I check the properties of the Linked Server, and just click the OK, I get this error:</p>
  
  <h2>TITLE: Microsoft SQL Server Management Studio Express</h2>
  
  <p>"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"</p>
  
  <hr>
  
  <p>ADDITIONAL INFORMATION:</p>
  
  <p>An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)</p>
  
  <hr>
  
  <p>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)</p>
  
  <p>For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;ProdVer=09.00.5000&amp;EvtSrc=MSSQLServer&amp;EvtID=7399&amp;LinkId=20476" rel="noreferrer">http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;ProdVer=09.00.5000&amp;EvtSrc=MSSQLServer&amp;EvtID=7399&amp;LinkId=20476</a></p>
  
  <hr>
  
  <p>BUTTONS:</p>
  
  <p>&amp;Yes</p>
  
  <h2>&amp;No</h2>
  
  <p>Please help!</p>
  
  <p>Thanks</p>