...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 Young
Hi
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/default...b;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 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 Young

...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

...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/default.aspx?scid=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 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 Young

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.