各位老师好:
我用的是Sql Server2000数据库,并且把一个远程的Sql Server2000服务器加进了我的企业管理器中,我想在一个存储过程中执行跨网段的存储过程,但总是不成功。请各位老师指点。
存储过程代码如下:(其中我本机的IP为192.168.14.9,远程SQL服务器的IP为192.168.65.2,跨网段),如把存储过程中的“begin transaction”与“commit”语句支掉,存储过程执行正常;但一加上就提示出错);
CREATE procedure qch_maopi_ccgc as
set xact_abort on
begin transaction --毛重上传
--新增毛重记录插入到服务器上
insert into [192.168.65.2].qch.dbo.mao select * from mao where zt='1' --同时删除本地机与服务器上zt='2'的记录
delete [192.168.65.2].qch.dbo.mao where bh in(select bh from mao where zt='2')----推荐delete from mao where zt='2'--3.状态为3的记录(毛重更新)
--a.删除服务器上与本机毛重记录状态为3的记录相应的记录
delete [192.168.65.2].qch.dbo.mao where bh in(select bh from mao where zt='3')
--b.把状态为3的记录增加到服务器上相应记录的回皮日期小于此条记录的回皮日期。
insert into [192.168.65.2].qch.dbo.mao select * from mao where zt='3'
--毛重下载
--把服务器上状态为3的毛重记录更新到本地机上。
delete from mao where exists(select * from [192.168.65.2].qch.dbo.mao where zt='3')--新增服务器有,本地机无的毛重记录
insert into mao select * from [192.168.65.2].qch.dbo.mao as w where
not exists(select * from mao where mao.bh=w.bh)--删除本地毛重有,服务器毛重无的记录
delete mao where not exists(select * from [192.168.65.2].qch.dbo.mao where bh=mao.bh)update [192.168.65.2].qch.dbo.mao set zt='0' where zt<>'3'update mao set zt='0'
--皮重上传
--a.删除服务器上回皮日期小于此条记录的相应记录。
delete [192.168.65.2].qch.dbo.pi from [192.168.65.2].qch.dbo.pi as w,pi as p
where w.hao=p.hao and p.rq2>w.rq2
--b.把状态为3或为1的记录且回皮日期大于服务器上相应皮重记录的记录增加到服务器上
insert into [192.168.65.2].qch.dbo.pi select * from pi where
not exists(select * from
[192.168.65.2].qch.dbo.pi as w where w.hao=pi.hao)--皮重下载
--①删除本地机回皮日期小于服务器相应皮重记录的记录
delete from pi where not exists(select * from [192.168.65.2].qch.dbo.pi as w
where w.hao=pi.hao and w.rq2<pi.rq2)
--②增加服务器有本地机无的皮重记录
insert into pi select * from [192.168.65.2].qch.dbo.pi as w where not
exists(select * from pi as p where p.hao=w.hao)
--或
--insert into pi select * from wjzibm.test.dbo.pi as w where hao not in(select Hao
--from pi)commitGO
我用的是Sql Server2000数据库,并且把一个远程的Sql Server2000服务器加进了我的企业管理器中,我想在一个存储过程中执行跨网段的存储过程,但总是不成功。请各位老师指点。
存储过程代码如下:(其中我本机的IP为192.168.14.9,远程SQL服务器的IP为192.168.65.2,跨网段),如把存储过程中的“begin transaction”与“commit”语句支掉,存储过程执行正常;但一加上就提示出错);
CREATE procedure qch_maopi_ccgc as
set xact_abort on
begin transaction --毛重上传
--新增毛重记录插入到服务器上
insert into [192.168.65.2].qch.dbo.mao select * from mao where zt='1' --同时删除本地机与服务器上zt='2'的记录
delete [192.168.65.2].qch.dbo.mao where bh in(select bh from mao where zt='2')----推荐delete from mao where zt='2'--3.状态为3的记录(毛重更新)
--a.删除服务器上与本机毛重记录状态为3的记录相应的记录
delete [192.168.65.2].qch.dbo.mao where bh in(select bh from mao where zt='3')
--b.把状态为3的记录增加到服务器上相应记录的回皮日期小于此条记录的回皮日期。
insert into [192.168.65.2].qch.dbo.mao select * from mao where zt='3'
--毛重下载
--把服务器上状态为3的毛重记录更新到本地机上。
delete from mao where exists(select * from [192.168.65.2].qch.dbo.mao where zt='3')--新增服务器有,本地机无的毛重记录
insert into mao select * from [192.168.65.2].qch.dbo.mao as w where
not exists(select * from mao where mao.bh=w.bh)--删除本地毛重有,服务器毛重无的记录
delete mao where not exists(select * from [192.168.65.2].qch.dbo.mao where bh=mao.bh)update [192.168.65.2].qch.dbo.mao set zt='0' where zt<>'3'update mao set zt='0'
--皮重上传
--a.删除服务器上回皮日期小于此条记录的相应记录。
delete [192.168.65.2].qch.dbo.pi from [192.168.65.2].qch.dbo.pi as w,pi as p
where w.hao=p.hao and p.rq2>w.rq2
--b.把状态为3或为1的记录且回皮日期大于服务器上相应皮重记录的记录增加到服务器上
insert into [192.168.65.2].qch.dbo.pi select * from pi where
not exists(select * from
[192.168.65.2].qch.dbo.pi as w where w.hao=pi.hao)--皮重下载
--①删除本地机回皮日期小于服务器相应皮重记录的记录
delete from pi where not exists(select * from [192.168.65.2].qch.dbo.pi as w
where w.hao=pi.hao and w.rq2<pi.rq2)
--②增加服务器有本地机无的皮重记录
insert into pi select * from [192.168.65.2].qch.dbo.pi as w where not
exists(select * from pi as p where p.hao=w.hao)
--或
--insert into pi select * from wjzibm.test.dbo.pi as w where hao not in(select Hao
--from pi)commitGO
我公司有兩台服務器,數據庫是一樣的,位於兩個通過專線連接的局域網絡,IP分別爲:192.168.2.250 和 192.168.0.250 .
下面爲一個資料同步的示例:
-- 修正單重資料
UPDATE INVMB
SET MB014=A.MB014,MB015=A.MB015
FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=192.168.0.250;User ID=sa;Password=admin' ).ERP.dbo.INVMB A
LEFT JOIN INVMB B
ON A.MB001=B.MB001
WHERE A.MB014<>B.MB014