1、新建链接服务器 [img]http://files.jb51.net/file_images/article/201106/2011061700565413.png[/img]   [img]http://files.jb51.net/file_images/article/201106/2011061700565414.png[/img] 在图1中选中“链接服务器”，右键选择“新建链接服务器”，如图2，配置相关参数。 2、配置相关参数 [img]http://files.jb51.net/file_images/article/201106/2011061700565415.png[/img] 在“常规”选项中，填写“链接服务器名称”，访问接口选中 “ Microsoft OLE DB Provider for Oracle”项。其中产品名称、数据源、访问接口字符串请填写配置的Oracle客户端在本地配置的net服务名。如图3所示。 [img]http://files.jb51.net/file_images/article/201106/2011061700565416.png[/img] 在“安全性”项中，选中使用此安全上下文建立连接，填写远程登录用户和密码，即登录远程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""。该表不存在，或者当前用户没有访问该表的权限。 配置數據源： [img]http://files.jb51.net/file_images/article/201106/2011061700565417.png[/img] [img]http://files.jb51.net/file_images/article/201106/2011061700565418.png[/img] [img]http://files.jb51.net/file_images/article/201106/2011061700565419.png[/img] 工程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