欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Sqlserver2000连接Oracle11G数据库进行数据实时同步

程序员文章站 2022-03-26 23:36:13
...

Sqlserver2000 连接 Oracle11G 数据 库 进行 数据 实时 同步 1,前提条件 已经有sqlserver2000环境,已经存在oracle11g环境,准备这两个 数据 库 ,建立各自的访问账号,两者之间需要ping通,telnet端口也要能通,目的是将sqlserver里面的 数据 自动 同步 到

Sqlserver2000连接Oracle11G数据进行数据实时同步

1,前提条件

已经有sqlserver2000环境,已经存在oracle11g环境,准备这两个数据,建立各自的访问账号,两者之间需要ping通,telnet端口也要能通,目的是将sqlserver里面的数据自动同步到oracle数据里面去。Sqlserver里面有链路服务器(linked server)可以实现。

2,在sqlserver上面建立odbc数据

有2种方式:“Microsoft ODBC for Oracle”选项以及“Oracle inOraDb10g_home3”选项

2.1,“Microsoft ODBC for Oracle”驱动方式

进入“控制面板”->“管理工具”->“数据源 (ODBC)”,双击打开。

进入“系统 DSN”,点击“添加”按钮,在弹出的“选择你想为其安装数据源的驱动程序”选择框里面,选择在列表框中选择“Microsoft ODBC for Oracle”选项,单击“完成”按纽。

PS:这里不要选择安装oracle客户端时候生成的“Oracle in OraDB10g_home3”这个驱动。

如下图1.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

之后在弹出的界面里面,设置

Data Source Name:这里就是数据源名称,是tnsnames.ora里面配置的数据源名称,可以用tnsping xxx能测试ok的数据源名称。

Description:描述,随意填写

User Name:用户,连接oracle数据的用户名,比如scott

Server:软件服务器,默认为Oracle

这里,根据自己的实际情况,设置如下图2.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

之后点击“确定”按钮,就会生成新的ODBC数据源。

2.2,“Oracle in OraDb10g_home3”驱动选项

进入“控制面板”-> “管理工具”->“数据源 (ODBC)”,双击打开。

进入“系统 DSN”,点击“添加”按钮,在弹出的选择框里面,选择在“选择你想为其安装数据源的驱动程序”列表框中选择“Oracle in OraDb10g_home3”选项,单击“完成”按纽。如下图3.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

之后再弹出的界面,设置

Data Source Nam:随便输入一个名字,这个名字就要后面sqlserver要用到的

Description:描述,随意填写

TNS Service Name:下拉列表中就是oracle中配置好的TNS信息,如果你开始没有配置的话这里就为空的.你选一个TNS名字后,可以点下test connection验证下看连接上了不.

User ID:就是连接oracle数据的用户名,比如scott。

如下图5.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

点击“Test Connection”按钮,输入连接oracle数据的用户名密码,如果成功就会弹出“Connection successful”的提示框。之后点击“OK”按钮,完成创建,在“ODBC 数据源管理器”界面就会生成新的TEST数据源,如下图6.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

3,在“企业管理器”创建连接oracle数据的链路服务器

在前面创建好ODBC数据源之后,就可以开始建立连接oracle数据的链路服务器了,进入sqlserver企业管理器“控制台目录”-> “SQL Server组”->“(LOCAL) (Windows NT)”-> “安全性”->“链路服务器”。在右边的空白窗口界面,右键点击选择“新建链路服务器”,开始设置:

(0),定义连接名称;
(1),选其他数据源;指定程序名称为:Microsoft OLE DB Provider for Oracle;
(2),产品名称可不填;
(3),数据源指定刚才ODBC中定义好的数据源名称;
(4),提供程序字符串按
以下格式填写:UserID=username;PassWord=userpasswd
(或者按如下格式:UID=username;PWD=userpasswd),
这里的用户名和密码对应所要连接的oracle数据中的用户名和密码。


设置用此安全上下文进行,并录入oracle的数据用户名和密码。


可默认,

最后点击“确定”按钮完成链路服务起的建立工作,如下图7.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

4,验证链路服务器

进入sqlserver企业管理器“控制台目录”-> “SQL Server组”->“(LOCAL) (Windows NT)”-> “安全性”->“链路服务器”->“TEST”,点击表,即可在右边窗口看到该oracle数据用户拥有的的所有表名,如下图8.png所示:

Sqlserver2000连接Oracle11G数据库进行数据实时同步

5,在sqlserver的查询分析器窗口验证链路服务器

-- 执行查询

SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;

-- 录入数据

insert into openquery(TEST_ORCL,'SELECT *FROM SCOTT.ZZZ_TEST')

values(2,'b');

SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;

--删除数据

DELETE FROM openquery(TEST_ORCL,'SELECT *FROM SCOTT.ZZZ_TEST')

WHERE ID=2;

SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;

-- 修改数据

UPDATE openquery(TEST_ORCL,'SELECT * FROM SCOTT.ZZZ_TEST')SET NAME='A2' WHERE ID=1;

SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;

6,查看后台的链路服务器的ddl语句

在sqlserver的企业管理器里面看不到,可以通过SQL Manager 2008 for SQL Server工具来查看,你可以不通过sqlserver的管理器来操作,可以直接使用ddl语句来创建,ddl语句如下所示:

EXEC master.dbo.sp_addlinkedserver @server= N'TEST_ORCL',
 @srvproduct = 'Oracle',
 @provider = 'MSDAORA', @datasrc = 'TEST_ORCL',
 @location = '',
 @provstr = 'UID=username;PWD=paxxx',
 @catalog = ''
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'collation compatible',
 @optvalue = 'false'
GO
 
 EXECmaster.dbo.sp_serveroption @server = N'TEST_ORCL',
 @optname = 'collation name',
  @optvalue= 'null'
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'data access',
 @optvalue = 'true'
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'connect timeout',
 @optvalue = '0'
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'query timeout',
 @optvalue = '0'
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'rpc',
 @optvalue = 'false'
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'rpc out',
 @optvalue = 'false'
GO
 
EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
 @optname = 'use remote collation',
 @optvalue = 'true'
GO
 
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'TEST_ORCL',
 @locallogin = NULL,
 @useself = 'false',
 @rmtuser = N'username',
 @rmtpassword = 'password'
GO


7,一些报错信息汇总

错误 7399: OLE DB 提供程序 MSDAORA' 报错。

OLEDB 错误跟踪 [OLE/DB Provider 'MSDAORA' IDBInitialire::Initializereturned 0x80004005: ]

如下图:20150310/1.png

第一种解决方案:

代理中的sQl server 连接的问题,不能用windows身份验证,得用sysadmin登陆,

具体设置方法如下:在企业管理器中->管理->SQL Server 代理->右键菜单"属性"->分页选项卡上选"连接"->"使用SQL

Server 身份验证",之后"应用"跟"确定"就可以了。

第二种解决方案:

在建立odbd数据源的时候,不要用默认oracle客户端的“Oracle in OraDb10g_home3”驱动选项,要用“MicrosoftODBC for Oracle”驱动选项。


----------------------------------------------------------------------------------------------------------------
有,任!>
原博客地址: http://blog.itpub.net/26230597/viewspace-1457700/
原作者:黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

Sqlserver2000连接Oracle11G数据库进行数据实时同步

声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。