1.连接目的是能在ODBC源建立下将oracle数据库中的查询插入到sqlserver中
2.连接通过在windows下建立ODBC源
sp_addlinkedserver (Transact-SQL)----http://msdn.microsoft.com/zh-cn/library/ms190479.aspx
sp_addlinkedsrvlogin (Transact-SQL)---http://msdn.microsoft.com/zh-cn/library/ms189811.aspx
OpenQuery()---- http://msdn.microsoft.com/zh-cn/library/ms188427.aspx
1.保证oracle端服务名正确建立,tnsping通过
2.windows端安装oracle客户端,添加对应oracle的服务名
3.windows下建立ODBC数据源
4.sqlserver中建立link
more listener.ora
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /oracle/app/product/10.2.0/db_1)
(SID_NAME = orcl)
)
)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = product)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = product)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
Windows端安装oracle客户端(10g)并配置tnsname确保能够连接到oracle数据库
添加连接串:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.70)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
C:\Users\frank>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 07-2月
2012 16:11:36
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16
.192)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)
OK (50 毫秒)
确保能够ping通。
Win端ODBC源添加
控制面板---》管理工具---》数据源ODBC---》系统DNS---》添加---》找到oracle客户端
Data Source Name:填写资源名(随意填写)
Description:描述该ODBC
TNS Server Name: orcl(此处为oracle对应的连接串)
User ID:system (连接用户)
Sqlserver端操作:
Sqlserver端配置连接操作需要用到两个方法:
sp_addlinkedserver
sp_addlinkedsrvlogin
1.建立linkedserver:
EXEC sp_addlinkedserver
@server = 'Ora',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'orcl'
详细参数配置参考sp_addlinkedserver (Transact-SQL)
2.建立linkedsrvlogin
EXEC sp_addlinkedsrvlogin
'Ora',
'false',
NULL,
'system',
'oracle'
详细参数配置参考sp_addlinkedsrvlogin (Transact-SQL)
测试连接
select * from OpenQuery(ora, 'select * from frank.test')
创建一个测试JOB
要求,将oracle端的frank用户下的数据同步insertinto进sqlserver的表:
创建JOB:
企业管理器中:SQL Server 代理----》作业----》右键新建作业
通过以上连接,我们就可以在SQL Server中连接oracle数据库。从而将oracle数据插入到SQL Server中。