Why is INSERT INTO ... SELECT not copying all rows? I have two servers, SERVER-01 and SERVER-00 (not their real names). SERVER-00 is a SQL Server 2005 Standard instance, SERVER-01 is a SQL Server 2014 Standard instance. I have this query that runs in a SQL Agent Job every night on SERVER-00:

truncate table DataWarehouse.dbo.documents
set identity_insert DataWarehouse.dbo.documents ON

INSERT INTO [DataWarehouse].[dbo].[documents]
    (
     [documentID]          
    ,[poNumber]
    ,[soldTo]
    ,[shipTo]
    )
SELECT 
     [documentID]
    ,[poNumber]
    ,[soldTo]
    ,[shipTo]      
FROM [server-01].[DataWarehouse].[dbo].[documents]

set identity_insert DataWarehouse.dbo.documents OFF


(The actual query includes more columns, I've trimmed it for readability.)

On SERVER-01, documents is a view. On SERVER-00, documents is a table. In this query [server-01] is a linked server connection on SERVER-00 (it uses the credentials of a sysadmin to connect to SERVER-01).

Only about half the rows are being INSERTed into documents on SERVER-00. There are no error messages or warnings being logged by the job - it always succeeds. How on earth can this be happening?
It turns out the culprit was our old friend, ARITHABORT!

In the view documents, poNumber is actually the output of a scalar-function call. One of the inputs was a BIGINT that I was trying to pass to a INT parameter, so an overflow error was being raised for some rows. Because the linked server connection from SERVER-00 to SERVER-01 was leaving ARITHABORT unset (and thus OFF), the error was being swallowed so I wasn't seeing it when running the query from that side.

Once I corrected the problem with the function (expanded the parameter to be a BIGINT), all the rows started copying over correctly!



For reference, I confirmed the linked server connection's options by running this query on SERVER-00:

SELECT * 
FROM OPENQUERY([SERVER-01], '
    SELECT 
        CASE WHEN SESSIONPROPERTY(''ARITHABORT'') = 1 
        THEN ''ON'' 
        ELSE ''OFF'' 
        END AS [ARITHABORT]
')