调用远程Sql服务器执行脚本

--本机创建链接服务器
--exec sp_dropserver '服务的别名'
exec sp_addlinkedserver  'srv_lnk','','SQLOLEDB','192.168.24.252'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','jh0101'
exec sp_serveroption 'srv_lnk','rpc out','true'  --这个允许调用链接服务器上的存储过程
go

--本机创建游标

declare Form_StockPayMSQTBCursor1 cursor for        
select BillNo from Form_StockPayMSQTB where spflag=1           
open Form_StockPayMSQTBCursor1                      
declare @billNo varchar(100)
fetch next from Form_StockPayMSQTBCursor1 into @billNo  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
while @@fetch_status=0           --判断是否成功获取数据
begin

  begin transaction operTran1
  declare @retVal int
  exec srv_lnk.srv_lnk.dbo.ChangeERPSPstatus @billNo,@retVal output
  update Form_StockPayMSQTB set spflag=3 where BillNo=@billNo
  if @@error<>0 and @retVal=-1
    begin
     rollback transaction operTran1
     return
     end
  else
    begin
    COMMIT TRANSACTION operTran1
    end

fetch next from Form_StockPayMSQTBCursor1 into @billNo  --将游标向下移1行
end

close Form_StockPayMSQTBCursor1                   --关闭游标
deallocate Form_StockPayMSQTBCursor1
 
END

--远程服务器上的存储过程

--在ERP的数据库上创建的储存过程
drop proc ChangeERPSPstatus
CREATE PROCEDURE ChangeERPSPstatus
@billno int,
@paraResult int output
AS
BEGIN
  begin transaction operTran1
  update StockPayMSQTB set FzSp=1,bSp=1,bCWSp=1 where BillNo=@billno
  if @@error<>0 or @@rowcount = 0
    begin
     select @paraResult = -1
     rollback transaction operTran1
     return
     end
  else
    begin
     select @paraResult = 1
    COMMIT TRANSACTION operTran1
    end
END
GO