StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I was able to setup a linked server to a remote Oracle database, which ended up being a multi-step process:</p> <ol> <li>Install Oracle ODBC drivers on SQL Server.</li> <li>Create System DSN to Oracle database on SQL Server.</li> <li>Create linked server on SQL server using System DSN.</li> </ol> <p><strong>Step 1: Install Oracle ODBC drivers on server</strong></p> <p>a. Download the necessary <a href="http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html" rel="nofollow noreferrer">Oracle Instant Client</a> packages: Basic, ODBC, and SQL*Plus (optional)</p> <p>b. Unzip the packages to a local directory on the SQL server, typically <code>C:\Oracle</code>. This should result in a [directory] like <code>C:\Oracle\instantclient_10_2</code>, which will be the value of [directory] referenced in the rest of this answer.</p> <p>c. Create a text file named <strong><code>tnsnames.ora</code></strong> within the instant client [directory] that contains the following:</p> <pre><code>OracleTnsName = ( DESCRIPTION= ( ADDRESS = (PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521) ) ( CONNECT_DATA = (SERVICE_NAME=acc) ) ) </code></pre> <p>Note: Actual <code>HOST</code>, <code>PORT</code>, and <code>SERVICE_NAME</code> will vary based on Oracle server you are establishing a connection to. This information can often be found using the Oracle network client tools under the <em>listeners</em>.</p> <p>The <code>OracleTnsName</code> can be any name you want to assign to the Oracle data source, and will be used when setting up the system DSN. You can also use the syntax above to define multiple TNS names in the same <a href="http://www.orafaq.com/wiki/Tnsnames.ora" rel="nofollow noreferrer">tnsnames.ora</a> file if desired.</p> <p>d. Add the [directory] to the system <strong><code>PATH</code></strong> environment variable.</p> <p>e. Create a new system environment variable named <strong><code>TNS_Admin</code></strong> that has a value of [directory]</p> <p>f. Execute the <strong><code>[directory]\odbc_install.exe</code></strong> utility to install the Oracle ODBC drivers.</p> <p>g. It is recommended that you reboot the SQL server, but may not be necessary. Also, you may want to grant security permissions to this directory for the SQL server and SQL agent user identities.</p> <p><strong>Step 2: Create a System DNS that uses the Oracle ODBC driver</strong></p> <p>a. Open the <em>ODBC Data Source Administrator</em> tool. [ Administrative Tools --> Data Sources (ODBC) ]</p> <p>b. Select the System DSN tab and then select the Add button.</p> <p>c. In the drivers list, select <em>Oracle in instantclient {version}</em>. (e.g. 'Oracle in instantclient 10_2') and then select Finish button.</p> <p>d. Specify the following:</p> <ul> <li><code>Data Source Name</code>: {System DSN Name}</li> <li><code>Description</code>: {leave blank/empty}</li> <li><code>TNS Service Name</code>: should have the <code>OracleTnsName</code> you defined in the <strong><code>tnsnames.ora</code></strong> file listed, select it as the value.</li> <li><em>User ID</em>: {Oracle user name}</li> </ul> <p>e. Select Test Connection button. You should be prompted to provide the {Oracle user password}. If all goes well the test will succeed.</p> <p><strong>Step 3: Create linked server in SQL to the Oracle database</strong></p> <p>Open a query window in SQL server and execute the following:</p> <pre><code>EXEC sp_addlinkedserver @server = '{Linked Server Name}' ,@srvproduct = '{System DSN Name}' ,@provider = 'MSDASQL' ,@datasrc = '{System DSN Name}' EXEC sp_addlinkedsrvlogin @rmtsrvname = '{Linked Server Name}' ,@useself = 'False' ,@locallogin = NULL ,@rmtuser = '{Oracle User Name}' ,@rmtpassword = '{Oracle User Password}' </code></pre> <p>Note: The <code>{Linked Server Name}</code> can be anything you want to use when referencing the Oracle server, but the <code>{System DNS Name}</code> <strong>must</strong> match the name of the system DSN you created previously.</p> <p>The <code>{Oracle User Name}</code> should be the same as the User ID used by the system DSN, and the <code>{Oracle User Password}</code> should be the same as you used to successfully test the ODBC connection. See <a href="http://support.microsoft.com/kb/280106" rel="nofollow noreferrer">KB 280106</a> for information on troubleshooting Oracle linked server issues.</p> <p><strong>Querying the Oracle linked server</strong></p> <p>You may use <a href="http://msdn.microsoft.com/en-us/library/ms188427.aspx" rel="nofollow noreferrer">OPENQUERY</a> to execute pass-through queries on the Oracle linked server, but be aware that for very large recordsets you may receive a <code>ORA-01652</code> error message if you specify a <code>ORDER BY</code> clause in the pass-through query. Moving the <code>ORDER BY</code> clause from the pass-through query to the outer select statement solved this issue for me.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload