SQL Server Connection Oracle detailed steps

We know that SQL Server and Oracle have many similar principles. In particular, some commonly used SQL statements are based on standards. So they can also have some interoperability. Here's how to configure SQL Server to connect to an Oracle. And then you're in SQL The contents of the tables in Oracle can also be viewed in the server.

I'll start by saying the environment I'm using:

Operating system: Win7, SQL Server, Oracle server 11gr1

There are also several points to note before you configure the SQL Server server where it is located. (The client can not I have not tried, not sure). Oracle's server is no matter where it is, as long as you can connect to it. Then you have to install the Oracle client.

The first step is to install the Oracle client and configure the TNS information.

Configure the TNS connection information for Oracle first. This is exactly the same as the normal use of oraclep.

ORCL =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = 123.456.176.42) (PORT = 1521))

)

(Connect_data =

(service_name = ORCL)

)

)

Step two, configure ODBC information

With ODBC connection information configured, SQL Server must not directly invoke Oracle's client to connect, It can only be indirectly connected by ODBC. Type ODBC in the place where you normally tap CMD to open the following interface. You first go to driver to see the Oracle's driver, No. It is certain that Oracle's client is installed.

Then choose the user DSN, click Add, select the Oracle driver you see on the previous screen.

After this page appears, enter a name in the data Source name, which will be used by SQL Server later. The TNS Service Name drop-down list is the one that is configured in Oracle and is empty if you start without configuration. After you choose a TNS name, you can click Test Connection to verify the connection is not.

The third step is configured in SQL Server

You can configure the ODBC data source in SQL Server before you create it. Open Management Studio first. Under Server objects, there is a linked Servers, select it and right-click New linked    Server. Open the following page. Enter a name in linked server, whichever you want, and use that name when you execute SQL later. Provider Select Oracle Provider for OLE DB. Product name fills in Oracle

The Data source is the name you get when you start to configure ODBC. The rest of the place is out of the way. And then click Security to pop another page.

Enter your username and password at the bottom of this page, then click OK

So you're done. You'll see Ora_arwen under Linked servers. Here's how to perform a few SQL validations under

SELECT * FROM OPENQUERY (Ora_arwen, ' select Sysdate from dual ');

--select * FROM OpenQuery This is a fixed format, and then in parentheses, first write the name you just created. This is Ora_arwen, Then enclose an SQL statement in a single quotation mark. This SQL statement is the SQL statement to execute in Oracle. Select Sysdate from dual is the SQL for Oracle to get the current time. If the user you are connected to Arwen has table info below, You can also write select * from info

SQL Server Connection Oracle detailed steps