当前事务未能导出到远程提供程序。已回滚了该事务。急！！！

[skkj]

我用ODBC的链接方式，采用分布式事务处理，但出现这样的报错：

当前事务未能导出到远程提供程序。已回滚了该事务。

select正常，insert不行，请问是什么原因，谢谢！

[rouqu]

ODBC可以进行trace 之后对trace进行分析 不过那个我看不来

你所指不使用linked server是正常的 具体是什么情况？

[skkj]

数据库在一台服务器，我是为了测试所以建了链接服务器，如下语句正常（能够得到返回值1）：

    begin tran

    if((select count(*) from SERVER.game.dbo.game_user where user_id = @user_id) = 0)
        insert into SERVER.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)

    if(@@error <> 0)
    begin
        rollback tran
        return -1
    end

    commit tran
    return 1

[skkj]

上贴发错了，语句是

    begin tran

    if((select count(*) from SERVER.game.dbo.game_user where user_id = @user_id) = 0)
        insert into game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)

    if(@@error <> 0)
    begin
        rollback tran
        return -1
    end

    commit tran
    return 1

[rouqu]

楼主 我没看出2和4的不同啊

[skkj]

没有用链接服务器原来是
insert into server.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
改为
insert into game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
后正常

[rouqu]

感觉有点什么 说不好

[rouqu]

insert into server.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
改为
insert into game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
--------
看下两句的执行计划相同吗？

[ojuju10]

插入到链接服务器应该是没有问题的，但是本地对链接服务器进行操作不能使用事务

[skkj]

insert into server.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
改为
insert into game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
--------
看下两句的执行计划相同吗？

======================

这两句是完全一样的，一个使用了链接服务器一个没有使用……

[skkj]

插入到链接服务器应该是没有问题的，但是本地对链接服务器进行操作不能使用事务
==================================================================

我把game库放到另一台机器上，然后建链接也是提示这个错误，不知道是为什么？？？

[rouqu]

哦 为什么那？楼主在查询分析器里执行insert into server.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)可以通过吗？

[skkj]

通过了，这是没问题的，只是加载事务里不行……，郁闷

[rouqu]

FROM SQL2K Book Online
===================
Loopback Linked Servers
Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network.

For example, executing the sp_addlinkedserver stored procedure on a server named MyServer defines a loopback linked server:

sp_addlinkedserver @server = N'MyLink',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'MyServer',
@catalog = N'Northwind'
GO

Transact-SQL statements that use MyLink as the server name loop through the SQLOLEDB provider and back to the local server.

Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction causes an error:

Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.

©1988-2004 Microsoft Corporation. All Rights Reserved.

[rouqu]

你注意看
Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction causes an error"

也就是说 你一个tran里面涉及了两台服务器 因此属于DTC Tran. 但分布式事务中不允许使用lookback linked server,也就是指向自身的linked server

[skkj]

可我game库放到了另一台机器上，再建ODBC，再建链接服务器，还是出现了这种问题啊，咳……

[rouqu]

晚上我测测

[skkj]

谢谢了~

[rouqu]

做了测试跟你的情况差不多 没有申明begin (distributed) tran的时候select、insert都是可以的 加了之后select/insert都不能通过 郁闷 由于我是英文版 错误如下：

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

一篇与此相关的KB文章
===============
Q839279 You may receive a 7391 error message in SQL Server 2000 when you run a distributed transaction against a linked server after you install Windows Server 2003 or Windows XP Service Pack 2
http://support.microsoft.com/kb/839279/en-us

说明这属于产品的一个问题！

我按照workaround三个步骤在两台机器上做下来 仍然是这个错误 没招了
最后说一句 微软的产品就是烂啊！

[rouqu]

便于楼下发表高见 稍微总结一下

楼主问题很简单 创建一链接SQL服务器为SERVER 直接运行如下语句是OK的

select count(*) from SERVER.game.dbo.game_user where ....
insert into SERVER.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)

但是如果运行一下就会产生错误

begin distributed tran
select count(*) from SERVER.game.dbo.game_user where ....
insert into SERVER.game.dbo.game_user(user_id,user_name) values(@user_id,@user_name)
commit tran
go

[skkj]

谢谢 石林#黄果树,你的那个报错我也遇见过，经过多次试验，发现用ODBC得连接方法可以避免你那种错误，

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

还有经过昨天的偶然想法，发现如果把这INSERT语句写到game库作为一个过程，然后用SERVER.game.dbo.存储过程名（负责处理INSERT），就完全正常了，呵呵，这种变通得解决方法，也可以，只要能正常运行就不管他了

begin distributed tran
exec @return_value = SERVER.game.dbo.存储过程名
commit tran
go

先结贴了，如果那位还有高见，欢迎提出，小弟受教了~