--本机创建链接服务器
--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