SQL Server connects to Oracle through linked servers

In SQL Server has been using the DTS Extract data, but DTS Microsoft only support to 2008, to 2012 there is no such tool, now need to build a channel between SQL Server and Oracle, with this channel, Extract the data from Oracle into the SQL Server database.

Environment Description: win2012 64-bit server, SQLserver2012 database, local database Oracle 11g-64bit

Note: Here is a special statement, perhaps some people think that Oracle does not distinguish between the number of bits, as long as the database on the line, is not possible, if your machine is 64-bit, you must install 64 bits of Oracle.

It is considered that the server is too wasteful to install the database, but in the installation of the client encountered the following problems:

Could not find the main class. Program would exit.

This question I also did not delve into, the net said is because in the Chinese catalogue the reason, actually, my is in the English directory, that is the version question.

Here is a detailed step for me to create a specific link:

1. Configure ODBC

Get to C:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\ first, find Tnsnames.ora.

Configure the service name on the connection to remote Oracle.

Open ODBC64

ODBC64 after a 64-bit Oracle database is installed

Then to the user DNS, click Add, find just the driver, you can see the following interface:

After clicking on the test connection, you will be asked to enter the username and password of the Oracle database and enter the completion point OK

Until the following interface appears, the description is half done.

2. New Linked Service

In the red section of the figure, pay special attention to IP plus the instance name. Perhaps some people think why not directly with the ODBC in the newly configured 160 as a data source, I just started to think it should be possible, but practice, error. I use the instance directly with the IP is successful.

Enter user name and password

You can also use scripting to establish a service link:

 Use [Master]GO/** * * * * object:linkedserver [TEST] Script DATE:2015/12/2 11:18:01 * * * * **/EXECMaster.dbo.sp_addlinkedserver@server =N'TEST11',@srvproduct=N'Oracle',@provider=N'oraoledb.oracle',@datasrc=N'192.168.1.160/oracle' /*For security reasons the linked server remote logins password are changed with ########*/EXECMaster.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'TEST11',@useself=N'False',@locallogin=NULL,@rmtuser=N'***',@rmtpassword='***'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Collation Compatible',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Data Access',@optvalue=N'true'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Dist',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Pub',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'RPC',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'RPC out',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Sub',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Connect Timeout',@optvalue=N'0'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Collation Name',@optvalue=NULLGOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Lazy Schema Validation',@optvalue=N'false'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Query Timeout',@optvalue=N'0'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Use remote collation',@optvalue=N'true'GOEXECMaster.dbo.sp_serveroption@server=N'TEST11',@optname=N'Remote proc Transaction promotion',@optvalue=N'true'GO

After the above work is done, you can test connectivity with the following statement

Select *  from OpenQuery (TEST11,'select sysdate from DUAL');

If there is a result, it means success.

SQL Server connects to Oracle through linked servers