Access Oracle through SQL Server 2008

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

Previously wrote an article about SQL Server access to the MySQL database, and recently just encountered the need to access Oracle, the configuration process is recorded for your reference.

Preparatory work

Do the following on the host that requires access to the Oracle database:

1. Installing the SQL Server database: SQL Server R2 Express

2. Installing the Oracle Client Access program: Oracle 10g clients

3. Configure the Oracle client Tnsnames.ora file, which needs to be aware of the database alias "Oradb" in the file, which will be used when creating linked Server below.

oradb =  (DESCRIPTION =     hostip) (PORT = 1521))     ))  

4. Ensure that the host can access the remote Oracle database through Sqlplus.

Create linked Server

Open SQL Server Management Studio into the Server Objects list, right-Linked Servers, click "New Linked Server ..."

Fill in the following items in the general interface:

Linked Server: Fill out the Linked server name

Provider: Select Microsoft OLE DB Provider for Oracle

Product name: Complete Oracle

Data source: Fill in the database aliases mentioned above ORADB

Click on Security, select "be made using this security context", assuming that the Oracle database has a user ID called admin, enter the access account in "Remote login" and click "OK" to end the creation.

When you're done, you'll see the Oracle database link you just created in linked Servers.

Access Oracle

Next, you can access the Oracle database on the SQL Server side, the query syntax is as follows:

SELECT * from Linked_name. Account_name. table_name;

Combined with the above example, if you want to query the contents of the Admin user under the Jobs table, the corresponding SQL statement should be:

SELECT * from Orasvr. Admin.jobs;

When using the above method for data query, if there is a conversion error when the Oracle table has a number column, you can convert the number type to a string (also, modify the column property to numbers (10), and set good digits for the Count column). It is more common to consider factors such as view queries, such as personal feelings or the way strings are converted.

Of course, if you query too many columns and each number column turns to varchar, then the best way to do this is to use the OPENQUERY function:

SELECT * from OPENQUERY (linked_name, ' SELECT * from table_name ');

In accordance with the syntax of OPENQUERY, the above SQL statement strain is:

SELECT * from OPENQUERY (orasvr, ' SELECT * from JOBS ');

This allows us to easily access the Oracle database on the SQL Server side and make the appropriate data adjustments.

Access Oracle through SQL Server 2008

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.