关于SQL中通过链接服务器执行分布式事务过程时只能查询不能执行insert update delete等操作
关于SQL中通过链接服务器执行分布式事务过程时只能查询不能执行insert update delete等操作,请问如何解决此问题,谢谢!
两台安装了SQL2005EXPRESS的服务器,远过互联网访问.
客户机可以成功执行下面的存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[update_spzl]
AS
BEGIN
set xact_abort ON
BEGIN TRANSACTION
if exists (select * from sysobjects where name = 'spzl') drop table spzl
if exists (select * from sysobjects where name = 'usertable') drop table usertable
if exists (select * from sysobjects where name = 'syspara') drop table syspara
if exists (select * from sysobjects where name = 'stock') drop table stock
if exists (select * from sysobjects where name = 'report') drop table report
if exists (select * from sysobjects where name = 'ckzl') drop table ckzl
if exists (select * from sysobjects where name = 'dabaodetail') drop table dabaodetail
SELECT * INTO stock FROM hst.WQJXC.dbo.stock
SELECT * INTO spzl FROM hst.WQJXC.dbo.SPZL
SELECT * INTO UserTable FROM hst.WQJXC.dbo.UserTable
SELECT * INTO syspara FROM hst.WQJXC.dbo.syspara
SELECT * INTO report FROM hst.WQJXC.dbo.report
SELECT * INTO ckzl FROM hst.WQJXC.dbo.ckzl
SELECT * INTO dabaodetail FROM hst.WQJXC.dbo.dabaodetail
UPDATE LUT SET LastUpdateTime = (GETDATE())
COMMIT TRANSACTION
END
但是这一条存储过程却执行不了:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[upload_sale]
AS
BEGIN
set XACT_ABORT on
BEGIN TRANSACTION
INSERT INTO hst.wqjxc.dbo.SaleMaster_Temp (dh,xstime,xsdate,syyid,yyyid,shoukuan,zhaohui,shuaka,yingshou,shishou,youhui,ckid,vipid,js)
SELECT dh,xstime,xsdate,syyid,yyyid,shoukuan,zhaohui,shuaka,yingshou,shishou,youhui,ckid,vipid,js
FROM SaleMaster_Temp
WHERE upload = 0
UPDATE SaleMaster_Temp SET upload = 1 WHERE upload = 0
INSERT INTO hst.wqjxc.dbo.SaleDetail_Temp (dh,bm,sl,jj,spsj,sjsj,ysxj,sjxj,zkl,yyyid)
SELECT dh,bm,sl,jj,spsj,sjsj,ysxj,sjxj,zkl,yyyid
FROM SaleDetail_Temp
WHERE upload = 0
UPDATE SaleDetail_Temp SET upload = 1 WHERE upload = 0
COMMIT TRANSACTION
END
提示错误如下:
链接服务器"bwq"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "没有活动事务。"。
消息 7391,级别 16,状态 2,过程 upload_sale2,第 8 行
无法执行该操作,因为链接服务器 "bwq" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务。
两台电脑都已开启MSDTC服务,135端口也没有被任何程序占用,不然第一条又怎会成功执行呢.
请高手指教.
两台机都是安装了XP SP3 ,SQL2005的版本也是一样的.