Posted by Erland Sommarskog on 10/01/36 11:39

Larry Bertolini (bertolini.1@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Show Server Trace Missing in SQL Management Studio?
• Prev in forum: Re: How do I NTH Select in SQL????
• Thread view: Re: Transaction reading from Linked Server without DTC? (Isolation Level?)

[Reply to this message]