Ms SqlServer2005访问Oracle（利用DBLINK和Synonym）

最新推荐文章于 2024-11-06 10:20:58 发布

fallingstarNo1

最新推荐文章于 2024-11-06 10:20:58 发布

阅读量5.2k

点赞数

分类专栏：数据库文章标签： oracle sqlserver sql server microsoft 服务器数据库

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

■通过建立DBLink，MS SQL Server2005连接Oracle服务器,方法如下:本人用的是SqlServer2005，所以以2005版本为例讲述DBLink的创建方法。

1）首先安装Oracle客户端，并创建到服务器的连接,用户名为TEST。

2)在sql server2005的Management Stadio 中ServerObject->LinkedServer->New LinkedServer
3)在新建对话框"常规"选项卡里面, 选择Microsoft ole db provider for oracle驱动. 产品名称为"Oracle", 数据源就是上面建立的网络名称TEST. 提供程序字符串填为 "MSDAORA".
4)在新建对话框"常规"选项卡里面输入该链接服务器取的名称（LINKTEST），"安全性" 选项卡里面, 选择" 用此安全上下文进行:", 输入Oracle中的用户名和密码.
5)如果连接不上，报MSDAORA不能提供连接错误的话，需要把ora81/network/ADMIN/sqlnet.ora文件中的“SQLNET.AUTHENTICATION_SERVICES= (NTS)”注掉。
6)查询方法：
方法一：select *from [LINKNAME]..[USERNAME].[TABLENAME]
方法二：SELECT * FROM OPENQUERY(LINKNAME, 'SELECT * FROM USERNAME.TABLENAME')

注：[LINK]..[USERNAME].[TABLENAME]最好全部大写。

用上面的一长串来查询数据太麻烦了，可以为其创建Synonym。
7)创建Synonym：
a）USE [MASTER] --数据库名
     GO
     CREATE SYNONYM [dbo].TABLENAME1 FOR [LINKNAME]..[USERNAME].TABLENAME
     --TABLENAME1 Synonym名
     --LINKNAME     DB LINK名
     --USERNAME Oracle的用户名
     --TABLENAME Oracle中的表名
     GO

b）测试
     select * from MASTER.dbo.TABLENAME1。

SQL Server 2005与Oracle同步注意NUMBER类型转换

　　我们可以使用同义词在SQL Server 2005下连接Oracle来实时访问Oracle数据库，但是如果Oracle中的表数据流较大则会影响应用系统的性能，于是应采用数据库作业每天定时执行。

　我们可以使用同义词在SQL Server 2005下连接Oracle来实时访问Oracle数据库，但是如果Oracle中的表数据流较大则会影响应用系统的性能，于是应采用数据库作业每天定时执行：

drop table abc--删除旧表
　　go
　　select * into abc

　　from aaa--aaa为同义词这样就可以把Oracle中的数据同步到本地的SQL Server数据库中。从而解决跨实例查询的性能问题。

　　使用这样的方式半年了都没有发现有什么问题，可是最近却发现了一个现象，在Oracle中有一个表aaa，其中一个字段BILL NUMBER类型(未指定精度和小数数据位)，对于这种类型，SQL Server2005中同步的表abc中却被定义为nvarchar(384)的类型!?明明是一个数字类型为什么SQL Server会将其转换为字符串类型呢?

　　若只是数据类型改变了倒没有什么，我应用程序在处理时转换一下就是了，但是更奇怪的是其中某些数据在Oracle中查出来是12.34567，但是在SQL Server 2005中查出来却成了12.345670543574563452346547546234234543656434...，后面是几十位的小数，简直神奇了!有一行数据在Oracle中是1，而在SQL Server中查出来是0.99999999999999999999999999999999...但是这种数据也很少发生，在数万条数据中可以找到2-3条这种几十位小数的数据。

　　正是这种数据的存在使得应用程序有时算出来的结果和Oracle那边的系统算出来的结果无法匹配。

　　经测试，如果Oracle中指定了NUMBER类型的精度和小数位数比如NUMBER(15)这样SQL Server将可以自动将其转换为numeric(15,0)类型。

　　由于NUMBER类型可以表示1.0 * 10(-130) —— 9.9...9 * 10(125) {38个9后边带88个0} 之间的数据，精确度可以达到小数点后38位小数，由于SQL Server中没有如此高精度的数据类型，所以在没有指定NUMBER精度和小数位的情况下SQL Server会将其转换为字符串类型以满足长度和精度的需要。

　　解决办法就是将SQL Server中同步表的nvarchar(384)类型修改为decimal类型或numeric类型，同步时不删除表，只是清除表内容，然后插入数据。同步SQL为：

TRUNCATE TABLE abc--清除表abc内容
　　go
　　insert into abc--将同义词aaa中的数据插入abc表
　　select *

本文以SQL Server 2k为例说明SQL Server到Oracle连接服务器的具体实现过程。

1.要求pc机上安装oralce客户端软件和sqlserver2000软件。

2.客户端的配置tnsnames.ora文件

配置所要连接的数据库服务器（windows，unix等平台均可以）

eg：tnsnames.ora

......

TEST = (DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.11.90)(PORT = 1521))

)

(CONNECT_DATA =

(sid = blue)

(SERVER = DEDICATED)

) )

......

3.配置windows的ODBC数据源

开始菜单—》设置—》管理工具—》数据源（ODBC）—》进入配置用户DSN或者系统DSN均可以：添加—》选择Microsoft ODBC for oracle—》自定义数据源名称（最好跟tns中连接串同名！）—》服务器名称（必填！填写tns文件中的连接串名称）—》完成。

4.配置sqlserver2000中的连接服务器

企业管理器—》安全性—》连接服务器—》右键新建连接服务器—》定义连接名称；选其他数据源；指定程序名称为：Microsoft OLE DB Provider for Oracle; 产品名称可不填；数据源指定刚才ODBC中定义好的数据源名称；提供程序字符串按以下格式填写：User ID=username;Password=userpasswd(或者按如下格式：UID=username;PWD=userpasswd)，这里的用户名和密码对应所要连接的oracle数据库中的用户名和密码 —》安全性标签页里：设置用此安全上下文进行，并录入oracle的数据库用户名和密码—》服务器选项标签页可默认—》确定。

5.准备工作全部完成

在sqlserver企业管理器—》安全性—》连接服务器打开刚建好的连接服务器—》点击表，即可在右边窗口看到该oracle数据库用户拥有的的所有表名，但在这里还并不能查看表的记录，这个需要在sqserver的查询分析器中用具体sql实现！访问表时，使用格式为： [连接服务器名]..[ORACLE用户].[表名]。更详细具体的使用这里不再赘述。