SQL Server链接服务器访问Oracle

SQLServer 链接服务器访问 Oracle一、测试环境说明操作系统：Windows Server 2008 R2 64 位数据库版本：SQLServer 2008 R2 64 位和 Oracle Database 11g 第 2 版(11.2.0.1.0)及相应的客户端版本：Oracle 11g client 64位其中 Oracle Database 11g 安装在一台电脑，另一台安装 SQL Server 2008 R2 64 位和 Oracle 11g client 64 位。二、创建 SQL Server 链接服务首先 SQL Server链接 Oracle 可以通过两个访问接口：“MSDAORA”和“OraOLEDB.Oracle”默认状态下，SQL Server 2008 R2 64位安装后在服务器对象-链接服务器-访问接口 下并没有MSDAORA”和OraOLEDB.Oracle” 接口。安装“MSDAORA”访问接口需要下载 Oracle Data Access Components(ODAC)for Windows 的相应64位版本。安装后重新启动服务器即可以看到MSDAORA”接口。因为此接口不支 持分布式事务，因此不做过多的描述。安装“OraOLEDB.Oracle”访问接口需要下载Oracle 11g client 64位。具体安装过程如下：1)下载后解压zip文件到文件夹，点击setup.exe,弹出如下界面：直接点是。2) 弹出如下界面：选择定制选项。点击下一步，弹出如下界面：4诜搖产品钳言迭择产詁谄言丫电宁古注和吉 执行先决杀件检亘Y解y舞产品Oracle Cliem妥浇孚-體客户机-歩靈2/7of?ac qqg DATABASE赧好於品B.电曰用占吕辿|hTi谓肓Cv 后退（WbiCEU *誣（E）却* &交三査吾寸匸僅加利亚语 寻睜汁TT活in廉罗尼亚语 二干节壬 印惠尼丙亚洁 _33.p :际 垄尹三尹订才匚堺助凹直接点击下一步，弹出如下界面：$a-tfri?（u）c l心 ientp-：d.cr mc-1 i充成殖蜓醴.后退砂卜一知扌8定宜菸位證节迭髓装类出I诜召产屋隹兰:；r 一专子沖牢白 T H至目录选择软件安装的位置后点击下一步，弹出如下界面：Oracle Client条程序-设苣喜户机-歩赊4/8可用产詰组件ORACLE ftfUgOATAbASEy指注嫁付需 來可用产占秦且件 y讥.-宦卫第:斗彫音丫撕要!安菸产品Oracle SQLJOracle Daiabase utilitiesOracle J日ira CliEniSQLTliJE：Oracle JDBQiTHIM InlerfacesDracls Inta met Dlractory c Ils ntOracle Call Inlerface (OCI)Oracle ProgrammerOracle XML Development Kj1Oracle Advanced SecurityEnterprise Manager Minimal IntegrationOracle NelOracle Conneciion ManagerDraclg N01 LlsienerOracle Mu Hi media Client OptionOracle ODBC Drl/erOracle ClusiewBre Hish Availability APIOracle SQL DgvlDper下一步时a=后退(B)在可用产品组件窗口点击全选后直接点击下一步，弹出如下窗口：Oracle Client虫装程序-设苣容户机-舞 5/9Oracle DdtjibR5F Schdul$r AgantI 判 I ?ri 9 C y.nh 胎 d Er u.-u yr.-iu ur I 爭以 主汉讦 30占 “lifiSE类出啜酥辽屮揖礼和m園Loacm可用言啟件俺由書山爭用上営15QD汛： W牛母昭台率助Q)*启退也I、一歩皿T Y诰择产ffiiHs 指詮装位程1豊品7完咸T 理OIACLE qqgDATABASE在调度程序代理主机名中输入本机的主机名。点击下一步，弹出如下界面：Oracle Client安轄序-蘑客户机-歩曝6/10Oracle Services for Microsoft TransActin Srvicss庚f囲HEN.2030ia棘鬲代库v:；l+耳早斗冷百率助Q)网(E)迭择产ffiiHs 摘宝宝装位畫 可用产勰件安装产品完成Orn c 9 UTS R9cover，, S ard .* 是宦 Orat a SetYices tot M crosortTrar 汨忧1。n S mrve t 自动床裝的。Oracle UTS Recovery Sbmcg接收请求,鬧决立删署上启动的有问題的讥DTC |脈朗串轻处哩。输 入3 IeMTS RECorerySerui 三此4算机上生听谪求的端口号。TI TIT7 racie seiMces mr mtsV启运也卜一捋皿ORACLE qqgDATABASE在 Oracle Services for MTS 中配置端口，这里直接使用默认端口，点击下一步：Oracle Services for Microsoft Transaction ServerOracle Services for MTS 允许客户在 MTS 协调事务中使用 Oracle 数据库作为资源管理 器，从而提供了 Oracle 解决方案和 MTS 之间的强大集成。 Oracle Services for MTS 用作 Oracle 数据库到 MTS 分布式交易协同器的代理。因此，这些服务负责提供与 MTS 的客户 端连接合并，并允许COM组件通过Oracle参与MTS交易。此外，Oracle Services for MTS 也可以操作运行在任何操作系统上的 Oracle 数据库（如果服务本身在 Windows 上运行）。直接点击完成，开始安装客户端程序。3） 配置注册表打开运行，输入regedit，按回车。找到注册表项 HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCIOracleOciLib 值改为 oci.dll、OracleSqlLib 值改为 orasql11.dll、OracleXaLib 值改为 oraclient11.dll。HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftMSDTCMTxOCI 对 它 做同 样的修改。4) 配置完成后重新启动计算机，继续配置在开始 -所有程序 -Oracle - OraClient11g_home1 -配 置和移植工 具下找到 NetManager 点击打开，弹出如下窗口：H咖名0 rac le Net Ma nag er - D：CIien tprod uct112.0cI ient_64NETWOR KvADM N 交件旧瑁龜命第帮助心）最烫用户辅几邑含碟朋碧綁 连接宇皓:另帝參刼割助目录中的”本地=服轴容r“朋务葩喀文件夹用于配狀地齡岩方迭。去晞吿方去酗宕方法立 -,快用迭种方法可以将简单钢,网塔翊宫解析为连摟甕融垢库或齢 防需的洁耳CON NE CT u s Ema m sJp asswn rd nBt_s e rri 匚 e_narnE:至査舌駆已在TNSNAWES.ORA件中色健了网络匪务占面収击“朋 :势命茗”文件加如杲厨培屈勞售不存在,请在工耳栏上单击或迭算:“瑁辑”=“创逞 卩榛5汕1八刖闫盍一詹懈更； 皿|I :轟Jqti 站嘶程帛点击服务命名，选择编辑-创建弹出网络服务名向导:上一步（E）下一歩迥）取消要通过网络祐问Oracle數据库或其他服努，应使用网络朋势名。该向导 可以帮助您创建网貉服希请输入希望用来访问數据库或朋务的名称o您可以选择任何営称网貉購努名网縉服务程向导第1庚（共5页）:网络販务名在网络服务名中输入要连接的 Oracle 实例名。点击下一步：网络服务名向导第2页共厅页）;W下一歩迥）取消TCP/IP （Internet 协谊）便用 茨1_的TCP/IP安全Internet协後） 命名管道（Microsoft貉连接）IPC沫地数据库）.上-歩姜通过网络与数据库通信，需要便用网貉协议。诸选择将用于要谊冋数摒库 的协讣U.I默认选择TCP/IP（lnternet协议）点击下一步:口网縉服箸程向导第3页洪5页）:协谊设置要使用TCP/IP协後与数据库通信，需要输入敢据库i+算机的主机名。 请输入数揺障所在计算机的TCP/IP主机名。还需要输入TCP/IP端口号。Oracle數据库的端口号通常是1。一 般不必臭外指定其他端口号、 上一步（下一步歹）主机名端口号1521取消输入要连接的主机名，端口号为1521 , Oracle的默认端口，点击下一步:网结服务备向导第4页共亍页般鹘Xtest匪势名:连接类型：数据库默认设蛊下一歩迥）取消上一歩（B）每亍0阳山数据库或朋劳都有一何諦名。恥uIe数据库的那务名溼 常是全局数拐库容请输入要访问的埶据库或其他購豎的霭势名您也可以根据需要选澤共享，专用或也中朋务器数据库连接。默认的方式 是由数据库自行决定。输入服务名（关于Oracle服务名与实例名，请参考Oracle服务名与实例名.doc）,点击下一步：点击测试中的测试按钮，进行服务器连接测试，注意要输入正确的用户名和密码，可 以直接点击完成跳过测试步骤。1、“MSDAORA” 访问接口是由 Microsoft OLE DB Provider for Oracle提供的，建议不使用 此接口进行链接。通过该接口建立的链接服务器在进行查询 Oracle 表时会报错，在带数据 类型 CLOB、 BLOB 字段时。错误提示如下：“链接服务器的 OLE DB 访问接口 MSDAORA 返回了消息 发生了一个 Oracle 错 误，但无法从 Oracle 中检索错误信息。链接服务器的 OLE DB 访问接口 MSDAORA 返 回了消息 数据类型不被支持。2、“OraOLEDB.Oracle” 访问接口是由 Oracle 的 Oracle Provider for OLE DB 驱动提供的。 它解决了两个数据库类型不一致的问题。而且如果需要使用分布式事务，必须使用它来创建 链接服务器。在创建之前，在SQL Server中，链接服务器-访问接口 -OraOLEDB.Oracle-右键属性， 选中“允许进程内”。这一步是使我们选择的OraOLEDB.Oracle接口打开执行操作。如未设置会报如下错误：“无法初始化链接服务器 null 的 OLE DB 访问接口 OraOLEDB.Oracle 的数据源对象使用 Oracle Provider for OLE DB 驱动创建 sqlserver 链接服务器的代码：-建立数据库链接服务器EXECSp_addlinkedserverserver=NORACLEDB,-要创建的链接服务器别名srvproduct=NOracle,-产品名称 provider=NOraOLEDB.Oracle ,- OLE DB 驱动名称datasrc=NORCL-数据源 Oracle-ora11g-network-admin-tnsnames.ora 查看EXECSp_addlinkedsrvloginORACLEDB,-已建立的链接服务器名false,- 固定*/NULL,-为每个登陆SQL SERVER的用户使用此链接服务器，则写用户名*/scott，一一帐号（Oracle） tiger - 密码EXECSp_serveroptionORACLEDB,rpc out,trueEXECSp_serveroptionORACLEDB,rpc,true-这两个是打开 rpc,rpc out 的，默认为 False, 打开后可以支持远程更改分布式 事务。（如有分布式事务操作必须要设置）这样我们就建好了链接服务器，已经可以通过它对 Oracle 数据库进行查询。三、链接服务器的查询查询的两种方式：1）链接服务器别名.Oracle名.oracle表，注意使用大写；SELECT *FROMORACLEDB.SCOTT.EMP这种方式可以进行操作，优点是使用简单，一目了然，而缺点是性能太差，查询大数 据量表很慢。效率太差。2）使用 OPENQUERY；对给定的链接服务 器执行指定的传递查询。该服务器是 OLE DB 数据源。OPENQUERY可以在查询的FROM子句中引用，就象它是一个表名OPENQUERY也 可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。但这要取决于 OLEDB 访问接口的功能。尽管查询可能返回多个结果集，但是 OPENQUERY 只返回第 一个。示例：A. 执行 SELECT 传递查询SELECT*FROMOPENQUERY (ORACLEDB,SELECT * FROM SCOTT.EMP )B. 执行 UPDATE 传递查询UPDATEOPENQUERY (ORACLEDB,SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 7369 )SETENAME=SMITH;C. 执行 INSERT 传递查询INSERTOPENQUERY (ORACLEDB,SELECT * FROM SCOTT.EMP )VALUES (8888,JIAO,MANAGER,NULL,1990-12-17 00:00:00.0000000,5000.00,5000.00,10);D. 执行 DELETE 传递查询DELETEOPENQUERY (ORACLEDB,SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = 8888 );查询方式速度几乎和在 Oralce 中一样快。并且我们可以将 openquery() 当做表来用。SQL Server 链接服务器实现分布式查询启用条件：1、必须安装 Oracle Services for Microsoft Transaction Server；2、必须用Oracle Provider for OLE DB驱动提供的OraOLEDB.Oracle访问接口来创建链接服务;3、必须双方启动并配置MSDTC服务，关于MSDTC服务的配置请参考SQLServer分布式事务配置.doc示例：SETXACT_ABORTONBEGINTRANUPDATEOpenquery (ORACLEDB,SELECT * FROM EMP WHERE EMPNO = 7369 ) SETCOMM= 200INSERTtest_yiyanhua .dbo.EMPSELECT*FROMtest_yiyanhua .dbo.EMPWHEREEMPNO= 7499IFERROR 0ROLLBACKTRANELSECOMMITTRAN