sql server (alternate): Hi Jo

You don't really give much information to work on! I assume that running
forever means that it has not completed? Therefore you may want to profile
the query to see what is exactly happening. You may also want to try
OPENQUERY or OPENROWSET instead.

I would expect the loop back to fail as you can not use them in a
distributed transaction. From BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:

Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.John
[quoted text, click to view]

Hi all,

I would like to perform an
INSERT INTO LINKEDSVR.dbo.xyz.abc
SELECT ... FROM dbo.dfg

where LINKEDSVR is a linked server on another machine. Both servers are
running SQLServer 2000 and have the DTC running.

When I run this batch from QueryAnalyzer without explicitly using
transactions, it works well (takes about 5 sec) - however, when I
enclose it using
begin [distributed] tran/commit tran
the query runs forever.

I also tried to use the local server as linked server (loopback) but it
did not work either.

Any suggestions?

Thanks,

Jo Siffert (jo.siffert@gmx.net) writes:
[quoted text, click to view]

Have you checked for blocking? I have a recollection that distributed
transactions are by default seriliazable, which have more concurrency
problems.

Use sp_who (on both servers) and keep an eye on the Blk column. A non-
zero value in that column means that this spid is blocking the spid
on this row. And if this spid is your distributed transaction, you bave
the reason.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at