...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B
.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
Corey YoungHi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/defaul...kb;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:

> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and
B.
> When I execute the following query from within SQL Server Management Studi
o
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
> 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young