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(@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
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
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.