函数调用问题,请高手解答,分数多多

[emayongxiang]

如何调用链接服务器的函数

select * from [dbs2].PostBase.dbo.fnCountWorkload('','','2003-4-1','2003-5-1')

提示
服务器: 消息 170，级别 15，状态 31，行 1
第 2 行: '(' 附近有语法错误。

在本地服务器上调用函数没有问题

[emayongxiang]

我有2000分,如有解答分数多多!!!

[愉快的登山者]

先进行服务器连接，然后调用。

[stone100]

我也想知道。请高手帮忙。

[leimin]

below is a example,call remote udf is same with stored procedure!
attention:
1.sp_addlinkedserver @provider must supports RPC like "SQLOLEDB"
2.run remote sp must use OPENQUERY()

EXEC sp_addlinkedserver 'auchan','','SQLOLEDB',NULL,NULL,'DRIVER={SQL Server};SERVER=auchan;UID=sa;PWD=admin;'
go
exec sp_addlinkedsrvlogin @rmtsrvname='auchan',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='admin'
GO
/*insert into sales (stor_id,ord_num,ord_date,qty,payterms,title_id)
select stor_id,ord_num,ord_date,qty,payterms,title_id from auchan.pubs.dbo.sales
*/

SELECT *
FROM OPENQUERY(auchan, 'master.dbo.sp_help')
go
EXEC sp_droplinkedsrvlogin @rmtsrvname = 'auchan', @locallogin = 'sa'
EXEC sp_dropserver @server = 'auchan'

[leimin]

SELECT *
FROM OPENQUERY(auchan, 'master.dbo.sp_help')
------------------------^^^^^^^^^^^^^^^^^^^^ MUST BE FULL NAME!!

[emayongxiang]

注释
OPENQUERY 不接受参数变量。

fnCountWorkload
(@nvcBranchNo nvarchar(5) --局所编号(可空)
,@nvcWorkNumber nvarchar(6) --营业员工号(可空)
,@sdBeginDate smalldatetime --开始时间
,@sdEndDate smalldatetime --结束时间
)


select * from OPENQUERY(dbs2,'select * from postbase.dbo.fncountworkload('','','20030401','20030501')')

[leimin]

you try:
select * from OPENQUERY(dbs2,'select * from postbase.dbo.fncountworkload("","","20030401","20030501")')

[pengdali]

select * from OPENQUERY(dbs2,'select * from postbase.dbo.fncountworkload('''','''',''20030401'',''20030501'')')

[pengdali]

declare @变量1 int,@变量2 int
select @变量1='20030401',@变量2='20030501'


exec('select * from OPENQUERY(dbs2,''select * from postbase.dbo.fncountworkload('''''''','''''''','''''+cast(@变量1 as varchar(10))+''''','''''+cast(@变量2 as varchar(10))+''''')')

[xlhl]

study

[erigido]

好多分呀?!可惜只能看不能拿呀!!!!
郁闷.呵呵~~~~

[emayongxiang]

我试成功的

declare
@变量1 nvarchar(50)
,@变量2 nvarchar(50)
set @变量1='2003-4-1'
set @变量2='2003-5-1'


exec('select * from OPENQUERY(dbs2,''select * from postbase.dbo.fncountworkload('''''''','''''''','''''+@变量1+''''','''''+@变量2+''''')'')')