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

Question

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'

Answer 1

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

Answer 2

注释
OPENQUERY 不接受参数变量。fnCountWorkload 
(@nvcBranchNo nvarchar(5) --局所编号(可空)
,@nvcWorkNumber nvarchar(6) --营业员工号(可空)
,@sdBeginDate smalldatetime --开始时间
,@sdEndDate smalldatetime --结束时间
)
select * from OPENQUERY(dbs2,'select * from postbase.dbo.fncountworkload('','','20030401','20030501')')

Answer 3

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

Answer 4

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

Answer 5

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))+''''')')

Answer 6

我试成功的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+''''')'')')