Developer Forum

I'm getting the above error when trying to perform a DML across a linked
server. A select works fine. I've tried setting XACT_ABORT ON and IMPLICIT
TRANSACTIONS OFF but still throws error. Is this a behavior change between
the SQL Native Client VS the SQL OLEDB provider? In SQL 2000, if I could
wrap a select inside a transaction, then everything else worked fine as
well.


Throws Error:

begin tran

delete from linkedserver.db.dbo.table1
where col1 = '12345'

commit tran

Works:

begin tran

select * from linkedserver.db.dbo.table1
where col1 = '12345'

commit tran

First off, post the error that you're getting. "An error" is
ridiculously general.

Second, check to make sure that MS DTC is running on both servers. Look
in your system and application event logs to make sure that it's not
spitting out any error messages or anything like that.

Hi Dave,
The error is in the subject line above. Yes. Network DTC is
enabled and the DTC service is running. If it wasn't I wouldn't even be able
to wrap the select statement inside a transaction. There are no errors in
the event logs. Also I'm running SQL 2005 RTM. The linked server is running
SQL 2000.

Hello Dave,

The first query succeeds because it executes under the context of a local
transaction on the remote server. The second query is failing as it needs a
distributed transaction as it involves operations in both local and remote
server and the remote transaction fails to join the local DTC transaction.
The following must be enabled for the distributed transaction to work
correctly:


-XACT_ABORT should be on.

-REMOTE_PROC_TRANS must be set to 1

-MSDTC must be enable to allow dtc access. You may want to try the
following:

1. start the MSDTC service
2. from "administrative tools" -> "component services" -> "computers" ->
"my computer" right click "properties"
goto MSDTC tab and select "security configuration", enable checkbox
for "network dtc access" and "allow outbound".

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

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

This posting is provided "AS IS" with no warranties, and confers no rights.

> Hi Dave,