因为把两台数据库装了同一台机机器上，所以没有安装oracle Client的部分,Oracle部分使用netca创建的Net Service Name，使用tnsping以及登入方式的确认用户权限的以及链接的通畅。
C:Windowssystem32>tnsping melchior TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 28-AUG-2 013 15:36:48 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: C:appLingproduct12.1.0dbhome_1 etworkadminsqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = melchior.c om))) OK (10 msec) C:Windowssystem32>sqlplus hr/hr@melchior SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 28 15:37:13 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Last Successful login time: Wed Aug 28 2013 15:27:24 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions SQL>
进入SQL Server Management
USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'ORACLE_MELCHIOR', @srvproduct=N'oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'MELCHIOR' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ORACLE_MELCHIOR', @locallogin = NULL , @useself = N'False', @rmtuser = N'hr', @rmtpassword = N'hr' EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1 Go
sp_addlinkedserver (Transact-SQL)
sp_addlinkedsrvlogin (Transact-SQL)
Linked Server Properties (Provider Options Page)
在SQL Server Management中利用Linked Server 进行查询
select * from openquery(ORACLE_MELCHIOR,'select * from employees')