关于Linked Server以及SQL Server的表与Oracle的表关联的小问题
鄙人注册博客园将近三年,但是一直没有写任何东西,深感惭愧,但也由于经验有限,不敢上来献丑。鉴于鄙人工作已两年有余,虽不曾在显名誉外的软件公司待过,但也算是有些开发经验,遂来此分享当前开发项目中的一些小问题,与各位前辈们交流下,若有好建议留下,鄙人深表感谢。
闲话就说几句,立即进入主题。鄙人在现在开发的报价系统中,遇到了关于Link Server的问题,主要是因为有个页面是需要同时连接Sql Server和Oracle得到数据进行显示,遂需要用到View以及Link Server。Link Server是Sql提供用于连接其他数据库管理工具的功能,具体位置如下图:
截图中的配置信息如下:
Linked server 链接名称
Server Type 选择“Other data source”
Provider 选择“Oracle Provider for OLE DB”
Product name 填写Oracle 主机地址
Provider String 填写 Oracle的连接字符串
进行以上配置之后,点击OK,Link Server即是配置好了。
下面就可以直接在SQL中访问oracle中的表,在此还涉及到Sql中的一个关键字OpenQuery,OpenQuery 是SQL Server用来与其他Server交互的一种技术,通过OpenQuery,SQL Server 可以直接访问其他数据库资源。而其他数据库在OpenQuery 表达式中是以Linked Server 存在的。
要想查看所有的Linked Server 可以使用sp_linkedservers 可以找到。
关于OpenQuery的表达式可以这么写:
Select * from OpenQuery([linkedServerName],'Select * from table1 where rownum < 10')这边需要注意的是在里面若是添加查询条件,如下语句:
Select * from tableName Where Account = 'Jack''Jack'是需要用双引号的形式进行传入,方可不会报错,如下:
Select * from OpenQuery([linkedServerName],'Select * from tableName where Account = ''Jack''')关于OpenQuery传入参数,我并没有用到,但是在查找资料过程中有找到,现将代码贴在下面,希望可以帮助到有缘人了。
DECLARE @Sql VARCHAR(1000) DECLARE @organizationID VARCHAR(10) SELECT @organizationID = (SELECT ORGANIZATION_ID FROM MYORGS WHERE ORGANIZATION_NAME = 'MMT') SET @Sql = 'SELECT * from tableName where organization_id ='+@organizationID SET @Sql = 'SELECT * FROM OPENQUERY(BETSYCRP2, ''' + REPLACE(@Sql, '''', '''''') + ''')' EXEC(@Sql)以上是关于Linked Server以及如何访问Oracle中的表讲述,下面就会开始创建View,该View主要是串联Sql和Oracle,一班串联表的代码如下:
create view [viewName]
select a.mapping_id, a.[function],a.price_type, a.product_line_id,a.authority_type,a.dm_region_name,a.dm_product_line, a.dm_function_role, a.remark,c.login_name,c.emp_No, b.Account from m_function_role_mapping a, ( select emp_no ,login_name, region_name, product_line,function_role from OPENQUERY(datamarket, 'select * from apps.xxdm_function_role_mapping_all') ) c left outer join WSCUser as b on c.empno=b.empno where a.dm_region_name=c.region_name and a.dm_product_line=c.product_line and a.dm_function_role=c.function_roleGo
但是在Excute的时候却提示如下错误:
该错误的意思在网上查询了一下:无法解决在等于操作时的排序问题。这个问题涉及到不同数据库中设定排序规则是不一样的。这个排序规则,右击数据库在属性中可以看到。一般排序规则是:Chinese_PRC_CI_AS,但由于我设计到的Oracle数据库服务器是在台湾那边,台湾那边一般是Chinese_TaiWan,所以排序规则不一样还是有可能,解决方法就是在oracle所有需要显示的栏位中添加后缀:collate Chinese_PRC_CI_AS,修正后的sql如下:
select a.mapping_id,a.[function], a.price_type,a.product_line_id,a.authority_type,a.dm_region_name, a.dm_product_line,a.dm_function_role, a.remark, c.login_name,c.empNo, b.Account from m_function_role_mapping a, ( select emp_no collate Chinese_PRC_CI_AS as empno,login_name collate Chinese_PRC_CI_AS as login_name, region_name collate Chinese_PRC_CI_AS as region_name, product_line collate Chinese_PRC_CI_AS as product_line,function_role collate Chinese_PRC_CI_AS as function_role from OPENQUERY(datamarket, 'select * from apps.xxdm_function_role_mapping_all') ) c left outer join WSCUser as b on c.empno=b.empno where a.dm_region_name=c.region_name and a.dm_product_line=c.product_line and a.dm_function_role=c.function_role如sql中的加粗放大字符,意思就是给每个显示的栏位设定排序规则,以达到Sql和Oracle中的排序规则一致即可,其实也可以修改Oracle中的排序规则,不用去麻烦的加在sql中,不过这边要是修改排序规则,那存放在表中的字符就有可能出现乱码现象。
以上就是鄙人在这段时间内遇到的问题,拿来分享于此,希望可以对一些人有些用处,写得不当之处,望请海涵!