Hi

Can anyone tell me how I can perform the following:

Start Procedure

Query remote data table into local temporary table.

Begin Transaction
Do bunch of stuff
End Transaction

The problem I am having is that it wants to begin a distributed transaction
even though there is no reference to the remote sql server database between
the Begin and End Transaction Statements. I am not updating the the remote
server so there is no need to have to setup and rely on Distributed
Transaction Coordinator.

The help file says the following which does not seem to be true: The DTC
wants to manage the Transaction even if the remote table query is outside
the Begin and end transaction.:

*********************************
BEGIN TRANSACTION starts a local transaction. The local transaction is
escalated to a distributed transaction if the following actions are
performed before it is committed or rolled back:

a.. An INSERT, DELETE, or UPDATE statement is executed that references a
remote table on a linked server. The INSERT, UPDATE, or DELETE statement
fails if the OLE DB provider used to access the linked server does not
support the ITransactionJoin interface.


b.. A call is made to a remote stored procedure when the
REMOTE_PROC_TRANSACTIONS option is set to ON.
The local copy of SQL Server becomes the transaction controller and uses MS
DTC to manage the distributed transaction.


*********************************
Any recommended workarounds to this would be greatly appreciated.

Best Regards

Randy