Transaction reading from Linked Server without DTC? (Isolation Level?)

[Larry Bertolini]

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

[Erland Sommarskog]

Larry Bertolini (be*********@osu.edu) writes:

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).
It's possible that if you set the remote database as read-only that
you will not need DTC. I have not tried this, though.
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

Well, the obvious thought when I see this code is: why not move
the SELECT on the remote query outside the transactions?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Larry Bertolini]

Erland Sommarskog wrote:

It's possible that if you set the remote database as read-only that
you will not need DTC. I have not tried this, though.
I tried it, but it had no effect.

Well, the obvious thought when I see this code is: why not move
the SELECT on the remote query outside the transactions?

Well, sure. The code I provided was just some stuff I hacked up
as a "proof of concept", to see if setting an isolation level would
allow a remote select within a transaction, without DTC. The "real"
code belongs to a programmer in a different department, and I haven't
even seen it. I can only assume that it's inconvenient (though perhaps
necessary) to restructure the code so that the remote selects are
outside of the transaction(s).

Thanks for the help.
Larry