Posted by Larry Bertolini on 10/01/74 11:39

Is there a way to read data from a linked server,
within a transaction, without using DTC?

The data on the linked server is static, therefore
there is no need for two-phase commit. There is
no need for locking data on the linked server, because
it is not being updated (either from the remote server,
or from the local server).

I don't want to run DTC because:
1.) there have been security-related flaws with DTC in
the past

2.) the application doesn't do distributed updates, and
because the data on on the remote server is static,
there is really no data integrity exposure without
DTC.

I cannot specify "WITH (NOLOCK)" on the select from
the linked server:
Server: Msg 7377, Level 16, State 1, Line 6
Cannot specify an index or locking hint for a remote data source.

I tried setting the isolation level:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
but that seems to have no effect on the requirement to use DTC.

I still get the message:
MSDTC on server 'LOCALSERVER' is unavailable.

Is there some other way around this? Is it possible to provide some
connection string parameter, in the linked server setup, that would
specify the "READ UNCOMMITTED" isolation level for the linked server,
so that DTC wouldn't be necessary.

(In other words, can I tell SQL Server, "trust me, this won't hurt"?)

Environment: SQL Server 2000 sp4

The SQL does something like:

declare @x char(4), @k int, @rc1 int, @rc2 int
set @k=123
BEGIN TRAN
select @x=x
from remoteserver.remotedatabase.dbo.table
where k=@k
update localdatabase.dbo.table1
set x=@x
where k=@k
set @rc1=@@error
update localdatabase.dbo.table2
set x=@x
where k=@k
set @rc2=@@error
if (@rc1 = 0 AND @rc2=0) COMMIT TRAN
else ROLLBACK TRAN

[Back to original message]