【SQL SERVER】OPENQUERY 远程函数引用及参数传值问题

本贴最后更新于 2862 天前,其中的信息可能已经渤澥桑田

模拟数据

两台服务器,都安装有数据库,服务器 A,服务器 B。

服务器 B 的数据库上配置有指向服务器 A 的数据库的链接数据库。

服务器 A 的数据库 Test 里创建以下两个函数,一个函数返回一个表值,一个返回单个字符串值。

CREATE FUNCTION FUN_ReturnTable
(
	@para VARCHAR(50)
)
RETURNS @table TABLE(
	col VARCHAR(50)
)
AS
BEGIN
	INSERT INTO @table
	        ( col )
	VALUES  ( 'This is Test.'),
			( @para )
RETURN
END 

GO 
CREATE FUNCTION FUN_ReturnValue (@para VARCHAR(50)) RETURNS varchar(50)
AS
BEGIN
    	RETURN ('Input para is '+@para)
END 
GO

调用测试

服务器 A 上的数据库可以执行

SELECT * FROM  Test.dbo.FUN_ReturnTable('haha') --返回表
SELECT dbo.FUN_ReturnValue('haha') --返回单个值

然后在服务器 B 上的数据库运行以下语句调用(假设配置的链接服务器为 LS)

SELECT LS.Test.dbo.FUN_ReturnValue('haha')

于是就报错了:"不允许远程函数引用 'LS.Test.dbo.FUN_ReturnValue',找不到列名 'LS',或者列名不明确。"

解决方法

可以看到这样直接通过链接数据库调用是不行的。需要换个方式,改用 OPENQUERY。

DECLARE @a VARCHAR(50)
set @a = (select * from openquery(LS,'select Test.dbo.FUN_ReturnValue(''haha'')'))
select @a --返回的字符串值

SELECT * from openquery(LS,'select * from Test.dbo.FUN_ReturnTable(''haha'')') --返回的表值

以上的调用,函数的参数值是写死的,如果传一个动态参数,改成下面这样:

DECLARE @a VARCHAR(50)
DECLARE @b VARCHAR(50)
set @a = (select * from openquery(LS,'select Test.dbo.FUN_ReturnValue('''+@b+''')'))
select @a

是无法运行的,提示错误:

SQL-SERVER-RPC-FUNCTION-CALL-1

这个问题我就费解了,OPENQUERY 的第二个参数不能直接带有动态变量,不知道啥原因,官网上也备注说明不可以含有参数。

解决方法是换种方式调用,改用 EXEC 的方法。

DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
set @b = 'haha';
set @sql = 'select a from openquery(LS,''select Test.dbo.FUN_ReturnValue('''''+@b+''''') AS a'')';
exec(@sql);

以上语句是可以执行的,输出“Input para is haha”单行的字符串。那么现在问题来了,我想将现在输出的结果赋值给一个本地变量 @a,所以想当然就这样写

DECLARE @a VARCHAR(50)
DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
set @b = 'haha';
set @sql = 'select a from openquery(LS,''select Test.dbo.FUN_ReturnValue('''''+@b+''''') AS a'')';
set @a = exec(@sql); --是错误的,语法错误
set @a = (select * from exec(@sql)); --是错误的,语法错误
select @a;

以上给 @a 变量赋值方法都不行。于是将 @a 改为表变量。

DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
DECLARE @a table( name varchar(50))
set @b = 'haha';
set @sql = 'select a from openquery(LS,''select Test.dbo.FUN_ReturnValue('''''+@b+''''') AS a'')';
insert into @a exec(@sql); --将结果插入表里
select name from @a

这样结果就保存到表变量了。调用返回表值的函数也是可以的。

DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
DECLARE @a table( name varchar(50))
set @b = 'haha';
set @sql = 'select * from openquery(LS,''select * from Test.dbo.FUN_ReturnTable('''''+@b+''''') AS a'')'; --调用表值函数。
insert into @a exec(@sql);
select name from @a

总结

  • 1.SQL SERVER 通过链接服务器是可以调用远程数据库的存储过程的。
    • 通过 exec LS.Test.dbo.testSP 'haha' 的方式,其中 LS 是链接服务器,Test 是数据库名称。但是这种方式需要将链接服务器配置为用户 RPC。
    • 通过 select * from openquery(LS,'exec Test.dbo.testSP ''haha'' ') 的方式,其中 LS 是链接服务器,Test 是数据库名称。
  • 2.SQL SERVER 通过链接服务器远程调用函数不能通过 LS.Test.dbo.testFun('haha') 的方式直接调用,改用 OPENQUERY 的方式。
  • 3.OPENQUERY 的第二个参数不能含有参数变量。如果需要传递参数,改成拼接字符串的形式,EXEC 命令执行字符串。
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    330 引用 • 614 回帖
  • SQLServer

    SQL Server 是由 [微软] 开发和推广的关系数据库管理系统(DBMS),它最初是由 微软、Sybase 和 Ashton-Tate 三家公司共同开发的,并于 1988 年推出了第一个 OS/2 版本。

    19 引用 • 31 回帖 • 1 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...