MSDTC forcing distributed transaction

We're performing the following query from a stored procedure on our
SQL Server database:
Begin Transaction
insert into local SQL Server table
select *
from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
When this runs, SQL Server escalates it to a distributed transaction
and MSDTC manages the transaction.
The problem is that this transaction is not always successfully
committed on the DB2 side. As part of trying to figure out why, we're
trying to figure out why this is even a distributed transaction, since
the only update is occuring locally. Why does this query force a
distributed transaction? Is there some way to reconfigure it so that
a distributed transaction is not forced?
Kees VanTilburg
VanTilburg EnterprisesHello kees,
If I learned correctly that you want to know why the following sql
statements use the distributed transaction.
Begin Transaction
insert into local SQL Server table
select * from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
As far as I know, When you execute a distributed query while in a local
transaction. If the OLE DB data source supports the ITransactionJoin
interface, the transaction is promoted to a distributed transaction, even
if the query is a read-only query. Therefore, I am afraid there is not an
easy way to reconfigure so that a distributed transaction is not used in
this situation.
I found the following article for your reference.
274348 INF: Distributed Queries Executed Within a Trigger Enlist MSDTC
http://support.microsoft.com/?id=274348
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.