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.