通过创建SQL Server 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案

转载 2011年05月19日 11:15:00

1、新建链接服务器的方法

 

在图1中选中“链接服务器”,右键选择“新建链接服务器”,如图2,配置相关参数。

2、配置相关参数

在“常规”选项中,填写“链接服务器名称”,访问接口选中

“ Microsoft OLE DB Provider for Oracle”项。其中产品名称、数据源、访问接口字符串请填写配置的Oracle客户端在本地配置的net服务名。如图3所示。

在“安全性”项中,选中使用此安全上下文建立连接,填写远程登录用户和密码,即登录远程Oracle服务器的用户和密码。如图4所示。

3、异构数据库访问语句格式

比如我们建立了链接服务器ORADBCONN,而Oracle中在HRSOFT用户下面建立了表WEBUSER,那么我们的SQL语句就是:

--清空Oracle表中的数据

DELETE  FROM  ORADBCONN..HRSOFT.WEBUSER

--将SQLServer中的数据写到Oracle中

INSERT  into  ORADBCONN.. HRSOFT. WEBUSER

SELECT * FROM WEBUSER

如果报告成功,那么我们的数据就已经写入到Oracle中了。

用SELECT *  FROM  ORADBCONN..HRSOFT.WEBUSER

查看Oracle数据库中是否已经有数据了。

4、链接服务器应用

A、查询Oracle数据表方式一(这种方式,当Oracle与SQLServer的数据类型不一致时经常报错,且速度稍慢):

select * from [LINK2ORACLE]..[ORACLE_USER_NAME].TABLE_NAME;

  我在执行该语句经常报类似错误信息:链接服务器 "LINK2ORACLE" 的 OLE DB 访问接口 "MSDAORA" 为列提供的元数据不一致。对象 ""CMCC"."OS2_GIS_CELL"" 的列 "ISOPENED" (编译时序号为 20)在编译时有 130 的 "DBTYPE",但在运行时有 5。

  B、查询Oracle数据表方式二(经试验,这种方式使用起来很顺畅,不报错,且速度几乎和在Oralce中一样快):

  select * from openquery(LINK2ORACLE,'select * from OracleUserName.TableName')

  您可以把openquery()当成表来使用。

  C、举个例子(将Oralce用户HRSOFT下的用户表WEBUSER导入到SQLServer2005数据库中):

  select * into OS_GIS_WEBUSER from openquery(LINK2ORACLE,'select * from HRSOFT. WEBUSER)

  D、更便捷的方式:通过创建同义词进行便捷查询:

  CREATE SYNONYM OS_GIS_CELL FOR [ORACLELK]..[CMCC].OS_GIS_CELL;

  select * from os_gis_cell;

  select * from os_gis_cell a where a.CellName is null;

注意:涉及 Oracle 部分的 SQL 语句,尤其是 [ORACLELINK]..[ORACLE_USER_NAME].TABLE_NAME 一定要大写,否则会报类似错误:

消息 7314,级别 16,状态 1,第 1 行

链接服务器 "ORACLELK" 的 OLE DB 访问接口 "MSDAORA" 不包含表 ""CMCC"."OS2_gis_CELL""。该表不存在,或者当前用户没有访问该表的权限。

配置數據源:

工程INPLAN:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.6)(PORT=1521)))(CONNECT_DATA=(SID=sa)(SERVER=DEDICATED)))

ERP:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.3.25)(PORT = 1528)))(CONNECT_DATA =(SID= TJTEST)))

  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.3.25)(PORT = 1528)))(CONNECT_DATA =(SID= TJTEST)))

新建作業里的代碼:

delete  from XBLD_Information

INSERT INTO  XBLD_Information(job_name, num_layers, JB_PP1, JB_PC1, JB_PP2, JB_PC2, JB_PP3, JB_PC3, PP_PP1, PP_PC1, PP_PP2, PP_PC2, PP_PP3, PP_PC3, PP_PP4,PP_PC4,BM)

select * from openquery(INPLAN,'

select  mjs15.job_name, mjs15.num_layers,mjs1.MM1 JB_PP1,mjs1.MC1 JB_PC1,mjs2.MM2 JB_PP2,mjs2.MC2 JB_PC2,mjs3.MM3 JB_PP3,mjs3.MC3 JB_PC3,mjs11.MM1 PP_PP1,mjs11.MC1 PP_PC1,mjs12.MM2 PP_PP2,mjs12.MC2 PP_PC2,mjs13.MM3 PP_PP3,mjs13.MC3 PP_PC3,mjs14.MM4 PP_PP4,mjs14.MC4 PP_PC4,Substr(mjs15.job_name,5,2) BM

from

(select mjl.job_name,

        mjl.num_pcbs,  

        mjl.num_arrays,

        mjl.num_panles_,

        mjl.num_layers,

        substr(mjl.op_size_y_string_,instr(mjl.op_size_y_string_,''='')+1,length(mjl.op_size_y_string_)-instr(mjl.op_size_y_string_,''='')+1) y_size, --祇へ糴

        substr(mjl.op_size_x_string_,instr(mjl.op_size_x_string_,''='')+1,length(mjl.op_size_x_string_)-instr(mjl.op_size_x_string_,''='')+1) x_size  --祇へ

 from   tj.my_job_list mjl

 ) mjs15,

(select *

 from

(select mjs.job_name,

        decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),1,material_name) MM1,

        decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),1,material_count) MC1

  from tj.my_job_stackup_material_count mjs

  where mjs.type_T=''Core''

  )where MM1 is not null

) mjs1,

(select  *

 from

 (select mjs.job_name,

         decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),2,material_name) MM2,

         decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),2,material_count)MC2

  from tj.my_job_stackup_material_count mjs

  where mjs.type_T=''Core''

 ) where MM2 is not null

) mjs2,

(select *

 from

  (select mjs.job_name,

          decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),3,material_name) MM3,

          decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),3,material_count)MC3

   from tj.my_job_stackup_material_count mjs

   where mjs.type_T=''Core''

  ) where MM3 is not null

) mjs3,

(select  *

 from

 ( select mjs.job_name,

        decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),1,material_name) MM1,

        decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),1,material_count) MC1

   from tj.my_job_stackup_material_count mjs

   where mjs.type_T=''Prepreg''

 )where MM1 is not null

)  mjs11,

(select *

  from (

   select mjs.job_name,

         decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),2,material_name) MM2,

         decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),2,material_count)MC2

   from tj.my_job_stackup_material_count mjs

  where mjs.type_T=''Prepreg''

  )where MM2 is not null

  ) mjs12,

  (select  *

 from

 ( select mjs.job_name,

          decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),3,material_name) MM3,

          decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),3,material_count)MC3

   from  tj.my_job_stackup_material_count mjs

   where mjs.type_T=''Prepreg''

 )

 where MM3 is not null

 ) mjs13,

 (select distinct *

  from

  (   select mjs.job_name,

             decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),4,material_name) MM4,

             decode(DENSE_RANK() over( partition by job_name  order by material_name,material_count),4,material_count)MC4

      from tj.my_job_stackup_material_count mjs

      where mjs.type_T=''Prepreg''

  ) where MM4 is not null

  ) mjs14

  where mjs15.job_name=mjs1.job_name(+)

  and mjs15.job_name=mjs2.job_name(+)

  and mjs15.job_name=mjs3.job_name(+)

  and mjs15.job_name=mjs11.job_name(+)

  and mjs15.job_name=mjs12.job_name(+)

  and mjs15.job_name=mjs13.job_name(+)

  and mjs15.job_name=mjs14.job_name(+)

')

 UPDATE XBLD_Information SET BM=b.Describe from XBLD_Information a, XBLD_BM b where a.bm=b.id

SQL Server 2005数据库高级应用

掌握数据库的设计思想,需求分析能力,熟练使用数据库高级查询,函数与索引,视图,数据库编程,游标,事务处理,存储过程,触发器
  • 2016年07月08日 08:24

SQL2005如何通过写语句建立链接服务器

exec   sp_addlinkedserver  'ITSV',   '',   'SQLOLEDB','114.91.185.117'   exec   sp_addlinkedsrvlogin...
  • quentinlees
  • quentinlees
  • 2010-10-19 15:28:00
  • 2213

怎样在SQL-SERVER中创建Oracle的链接服务器

  • 2013年03月30日 15:24
  • 924B
  • 下载

SQL创建链接服务器(脚本)

if exists (select * from master.dbo.sysservers where srvname = 'srv_lnk')     exec sp_dropserver 'sr...
  • markloong
  • markloong
  • 2015-06-05 17:45:05
  • 2042

数据库操作大全

  • 2011年12月19日 14:49
  • 41KB
  • 下载

SQLSERVER建立链接服务器命令

sp_serveroption linkserver,rpc out, true其中 linkserver ,是你所要建立链接服务器名称!
  • flm2003
  • flm2003
  • 2006-04-12 09:35:00
  • 1549

SQL Server2005连接Excel、Access,链接服务器的设置

  • 2013年08月21日 16:45
  • 937KB
  • 下载

让oracle访问到sqlserver中的数据(异构服务总结)

                                             采用透明网关技术,让oracle能轻松访问到sqlserver数据库中的数据。透明网关oracle在企业版本里...
  • tangnf
  • tangnf
  • 2007-05-25 21:14:00
  • 3762

通过sql server 连接mysql,也就是链接服务器

1、在SQL SERVER服务器上安装MYSQL ODBC驱动;驱动下载地址:http://dev.mysql.com/downloads/connector/odbc/2、安装好后,在管理工具-OD...
  • yuanhong55
  • yuanhong55
  • 2017-08-16 23:27:17
  • 2479

sql2005 链接服务器 连接Oracle...

一、建立链接服务器        有人喜欢调用系统过程来建立,但我个人对系统过程没有特别的学习 ,所以用的是界面设置,当然有兴趣也可以研究一下的,因为可以把SQL执行导出来。 USE [master]...
  • akuoma
  • akuoma
  • 2010-01-29 15:19:00
  • 9598
收藏助手
不良信息举报
您举报文章:通过创建SQL Server 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案
举报原因:
原因补充:

(最多只允许输入30个字)