以文本方式查看主题

-  昂捷论坛  (http://enjoyit.com.cn/bbs/index.asp)
--  □-技术研讨会  (http://enjoyit.com.cn/bbs/list.asp?boardid=36)
----  如何将变量传递给链接的服务器查询  (http://enjoyit.com.cn/bbs/dispbbs.asp?boardid=36&id=6831)
--  作者：czg1981
--  发布时间：2010/1/28 16:12:36
--  如何将变量传递给链接的服务器查询

数据库之间通过链接服务器访问另一台服务有两种方法，一种是我们常用的使用四部分名称，另外一种就是用行集函数代入参数法。

四部分名称法就是用 “链接服务器.数据库名.用户名.表名”的形式，这个如何加参数我们就不介绍了。

行集函数指的是：OPENQUERY、OPENROWSET、OPENXML、OPENDATASOURCE等，我们通过看语法就知道这类函数不能带入变量。行集函数是直接将写好的语句在服务器上执行，然后再把返回结果显示出来，因此它的效率要比四部分名称法要快，如果说在传语句中再加上条件，那这个差别就更快了，这个在远程服务器非Server SQL 更明显。我们这边做了一个测试，本地是SQL 2008，远程服务器是Oracle 9i，在一个近200W行的表中，显示id=10000的一行记录，用四部分名称平均用时超过54秒，用OPENQUERY不到1S就完成。

1 传递基本值

在基本的 TRANSACT-SQL 语句已知，但必须在一个或多个特定的值传递时使用类似于下面的示例代码

      DECLARE @TSQL varchar(8000), @VAR char(2)
      SELECT  @VAR = \'CA\'
      SELECT  @TSQL = \'SELECT * FROM OPENQUERY(MyLinkedServer,\'\'SELECT * FROM pubs.dbo.authors WHERE state = \'\'\'\'\' + @VAR + \'\'\'\'\'\'\')\'
      EXEC (@TSQL)
2 传递整个查询

您必须传递整个 TRANSACT-SQL 查询或链接的服务器 （或两者），名称中使用的代码类似于下面的示例：

DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = \'MyLinkedServer\'
SET @OPENQUERY = \'SELECT * FROM OPENQUERY(\'+ @LinkedServer + \',\'\'\'
SET @TSQL = \'SELECT au_lname, au_id FROM pubs..authors\'\')\'
EXEC (@OPENQUERY+@TSQL)

例子截选自：http://support.microsoft.com/kb/314520/zh-cn
--  作者：czg1981
--  发布时间：2010/1/29 20:46:58
--  

用 EXEC (\'……\') AT linkedserver 访问远程服务器。

在上面介绍的OPENQUERY当只使用 select 的时候，确实解决我的问题，后来使用update 时，确又出问题了，报“无法使用书签从链接服务器的OLE DB 访问接口 "OraOLEDB.Oracle"提取行”，后来查询没有什么好的解决方案，只能放弃！

后来在同事的帮助下，找到 用EXEC (\'……\') AT linkedserver的方式，后来发行这个比OPENQUERY好用，关键是可以直接带入参数。

1 select、update、insert

--动态SQL在Oracle链接服务器上执行
EXEC(\'select * from table_name \') AT R2000

2带参赛的

declare @deposit int,@psword varchar(20),@cardno varchar(20)

set @deposit =1

set @psword=\'111111\'

set @cardno=\'111111\'

EXEC(\'update table_name set column_name1=\'+@deposit+\',column_name2=\'+@psword+\' where column_name3=\'\'\'+@cardno+\'\'\'\') AT R2000

3调研存储过程

--执行Oracle上存储过程
EXEC(\'BEGIN UP_KPI_DATA(\'\'05\'\',\'\'2007\'\'); END;\') AT R2000