SQL Server Connection Oracle detailed steps

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 the SQL Server connects to an Oracle. Then you can also view the contents of the tables in Oracle in SQL Server .  I'll start with the environment I'm using:  www.2cto.com   OS: Win7  ,sql Server, Oracle  server 11g r1  Also note several points before configuring 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 you are, as long as you can connect to it. And then you have to install the Oracle client .  the first step of installing the Oracle client and configuring 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)  ))   Second step, configure ODBC information   Configure ODBC connection information, SQL Server must not directly call 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 go to driver to see the Oracle's driver, No. It's sure to be there if you have Oracle's client installed .   Then choose the user DSN, click Add, select the Oracle driver you see on the previous screen.  www.2cto.com     When 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 configured TNS information in Oracle, if you startIt is empty here if not configured. After you choose a TNS name, you can click Test Connection to verify that the connection is not .   the third step in SQL Server configuration   After creating the ODBC data source in the SQL The server is configured. Open Management Studio first. Under Server objects, there is a linked Servers, select it and right-click New linked server. Open the following page. In linked Enter a name in the server, whichever you want, and use that name when you execute SQL later. Provider Select Oracle Provider for OLE DB.    product name to fill oracle  www.2cto.com  data Source is the name you take when you start to configure ODBC. And then the security will pop up another page    enter the 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   www.2cto.com  select * from OPENQUERY (Ora_arwen, ' Select Sysdate from dual ');  --select * FROM OpenQuery This is a fixed format, and then in parentheses 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