Selecting and Inserting into linked server

Question

I am having a situation with linked server which I am not able to understand.

So we have a linked server from a 2008R2 server to a 2014 server. The below sample query is executing from 2008R2 server and it works fine.

SET XACT_ABORT ON;
 Declare @BatchSize int = 10
 DELETE from LINKEDSRV.DB.DBO.Table
 INSERT INTO LINKEDSRV.DB.DBO.Table (ECN)
 SELECT TOP (10) C1 from LINKEDSRV.DB.DBO.Table22 --order by C1
 SELECT * FROM LINKEDSRV.DB.DBO.Table

But when I execute the same thing with the order by C1 it does not return any results.

Second case - If I replace the TOP(10) with TOP(@BatchSize) and no order by also I dont get any results out. eg

SELECT TOP (@BatchSize) C1 from LINKEDSRV.DB.DBO.Table22 

All of the scenarios work if I SET XACT_ABORT OFF. So does XACT_ABORT has any kind of restriction when it comes to linked server?

Edit - Did some more testing and looks like it has to do something to do with the number of rows also

Possible Repo

On ServerA

use testdb
go
create table t1( c1 int, c2 datetime)
create table t2( c1 int, c2 datetime)
insert into t2 select 1, GETDATE()
insert into t2 select * from t2 -- insert close to 5000 rows, I found the issue around over around 35000 rows

On ServerB

Create linked server to ServerA

SET XACT_ABORT ON;
Declare @BatchSize int = 10
delete from ServerA.testdb.dbo.t1
insert into ServerA.testdb.dbo.t1 (c1) 
select top (@BatchSize) c1 from ServerA.testdb.dbo.t2 --order by c2
select * from ServerA.testdb.dbo.t1

No output. But if you reduce the number of rows in table t2 to say around 2000, it works fine.

Answer 1

All of the scenarios work if I SET XACT_ABORT OFF. So does XACT_ABORT has any kind of restriction when it comes to linked server?

The setting of XACT_ABORT is propagated to the remote SQL Server, as documented in Handling Errors in Server-to-Server Remote Stored Procedures.

The setting also affects if updates are possible, and how they are handled, as documented in Distributed Queries and Distributed Transactions. Your INSERT is allowed with XACT_ABORT OFF because SQL Server supports nested transactions.

Nevertheless, there appears to be an implementation bug, because tracing the activity on the 2014 server during the insert reveals that an error occurs when SQL Server attempts to release a schema lock:

trace excerpt

This error aborts the statement (attempting to release the schema lock) on the remote server, but when XACT_ABORT is OFF, the remote server continues processing the next statement. The insert completes, despite the schema lock release error.

When XACT_ABORT is ON, the whole remote batch is aborted, so the insert is rolled back.

I was able to reproduce your problem locally, but the ORDER BY clause was not significant.

You can avoid the problem in several ways, including wrapping the INSERT in an explicit transaction (assuming DTC is available to you).

I recommend you avoid four-part name syntax for remote changes, since the implementation is based on a cursor model. You will normally get better performance using a bulk method, or by pulling the data at the remote server (rather than pushing it from the local server).

See the related Q & A Which one is more efficient: select from linked server or insert into linked server?

Answer 2

In my experience executing multi part commands through linked server usually face with issues, especially when the tables are big. Try this :

SET XACT_ABORT ON;
Declare @BatchSize int = 10
delete from ServerA.testdb.bbo.t1
select top (@BatchSize) c1 INTO #t2temp from ServerA.testdb.dbo.t2
insert into ServerA.testdb.dbo.t1 (c1) 
SELECT * FROM #t2temp
select * from ServerA.testdb.dbo.t1