高难度:在存储过程中进行分布式查询、更新、插入、删除等操作的问题(高手请进)
我现在写了一个存储过程,其中使用了分布式事务,在局域网内是可以执行并没有任何问题。
可通过宽带进行时就报错(该操作未能执行,因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务。
[OLE/DB provider returned message: 新事务不能登记到指定的事务处理器中。 ]
OLE DB 错误跟踪[OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]。)
通过企业管理器连接远程服务器没有问题,在查询分析器中使用存储过程中的语句也没有问题,现在是一使用存储过程就报错。
请高手帮忙。
存储过程的部分代码如下:
set @PRCservername = @mainservername
set @PRCservername = 'upinternetalan' --REPLACE(@PRCservername,'.','')
--set @PRCservername = REPLACE(@PRCservername,'-','')
select @srvid = srvid from master.dbo.sysservers where srvname = @PRCservername
if @srvid is null
begin
exec sp_addlinkedserver @PRCservername, '','SQLOLEDB', @mainservername --建立数据链接
exec sp_addlinkedsrvlogin @PRCservername, 'false', NULL, @LoginName, @LoginPassword
end
ELSE
begin
exec sp_dropserver @PRCservername,'droplogins'
exec sp_addlinkedserver @PRCservername, '','SQLOLEDB', @mainservername --建立数据链接
exec sp_addlinkedsrvlogin @PRCservername, 'false', NULL, @LoginName, @LoginPassword
END
--对远程数据进行数据的更新、插入操作
--得到需要更新的表名称(使用游标进行读取)
declare updata_table_name cursor for
select sys_updatalogtable.vc_tablename,sys_updataunitetable.vc_primaryname,sys_updataunitetable.vc_blobflag,sys_updatalogtable.vc_markfield
from sys_updatalogtable ,sys_updataunitetable
where sys_updataunitetable.vc_tablename = sys_updatalogtable.vc_tablename and sys_updatalogtable.vc_updataflag = 'N'
and sys_updataunitetable.vc_distributeflag LIKE '%上%'
open updata_table_name
fetch next from updata_table_name into @tablename,@primarycolumn,@blobflag,@markfield
while @@FETCH_STATUS = 0
BEGIN
--组合成更新及插入SQL语句
set @updatesqltext = 'update servertable set '
SET @insertvalu = ' SELECT '
set @insersqltext = 'INSERT INTO ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ' ( '
--从游标中读出更新表的字段并组合成更新语句
declare updata_columns cursor for
select COLUMN_NAME , DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tablename --取得数据表的字段
open updata_columns
fetch next from updata_columns into @columnsname,@columntype
while @@FETCH_STATUS = 0
BEGIN
--组合更新字段
IF @markfield is null or @markfield <> @columnsname
begin
SET @insersqltext = @insersqltext + @columnsname + ','
SET @insertvalu = @insertvalu + ' updatatable.' + @columnsname + ','
set @updatesqltext = @updatesqltext + ' servertable.'+ @columnsname + '=' + 'updatatable.' + @columnsname + ','
end
fetch next from updata_columns into @columnsname,@columntype
END
close updata_columns
DEALLOCATE updata_columns
--组合成更新SQL语句
set @updatesqltext = left(@updatesqltext,len(@updatesqltext) - 1 )
set @updatesqltext = @updatesqltext + ' from ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ' as servertable, ' + @tablename + ' as updatatable '
set @updatesqltext = @updatesqltext + ' where servertable.' + @primarycolumn + ' = ' + 'updatatable.' + @primarycolumn + ' AND updatatable.vc_permission = ''Y'''
--组合成插入SQL语句
set @insertvalu = left(@insertvalu,len(@insertvalu) - 1 )
set @insertvalu = @insertvalu + ' from ' + @tablename + ' as updatatable ' --@PRCservername + '.' + @dbname + '.dbo.' + @tablename + ' as servertable , ' +
set @insertvalu = @insertvalu + ' where updatatable.' + @primarycolumn + ' not in ( select ' + @primarycolumn + ' from ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ' ) ' + ' AND updatatable.vc_permission = ''Y'''
SET @insersqltext = left(@insersqltext,len(@insersqltext) - 1 )
SET @insersqltext = @insersqltext + ' ) ' + @insertvalu + ' '
--set @insersqltext = @insersqltext + ' select updatatable.* from ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ' as servertable,' + @tablename + ' as updatatable '
--set @insersqltext = @insersqltext + ' where updatatable.' + @primarycolumn + ' not in ( select ' + @primarycolumn + ' from ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ')' + ' and updatatable.vc_permission = ' + '''Y'''
-----------------------------------------------------------------------------------------------------
(到下面的语句就报错误了)
--执行更新SQL语句
BEGIN TRANSACTION localtran
exec(@updatesqltext)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN localtran
SET @errorinfo = @comm + '执行更新SQL语句未成功!'+ @tablename + '0'
close updata_table_name
DEALLOCATE updata_table_name
GOTO exitpro
END
ELSE
BEGIN
--更改已被上传更新完的本地表的上传标志
set @updatesqltext = 'update updatatable set updatatable.vc_permission = ''N'' ' + ' from ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ' as servertable , ' + @tablename + ' as updatatable '
SET @updatesqltext = @updatesqltext + ' where updatatable.' + @primarycolumn + ' in ( select ' + @primarycolumn + ' from ' + @PRCservername + '.' + @dbname + '.dbo.' + @tablename + ')' + ' and updatatable.vc_permission = ' + '''Y'''
exec(@updatesqltext)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN localtran
SET @errorinfo = @comm + '执行更新SQL语句的日志修改未成功!'+ @tablename + '0'
close updata_table_name
DEALLOCATE updata_table_name
GOTO exitpro
END
ELSE
BEGIN
--写更新日志表
update sys_updatalogtable set vc_updataflag = 'Y',dt_updatadate = @RPCdatetime
where sys_updatalogtable.vc_tablename = @tablename
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN localtran
SET @errorinfo = @comm + '执行更新SQL语句的日志修改未成功!'+ @tablename + '0'
close updata_table_name
DEALLOCATE updata_table_name
GOTO exitpro
END
ELSE
BEGIN
COMMIT TRAN localtran
END
END
END