Why is INSERT INTO … SELECT not copying all rows?

Question

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?

Answer 1

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]
')

Answer 2

it sounds like one possibility might be timeouts, so I will provide a possible solution/troubleshooting tips based on that premise.

Check server properties (in Object Explorer, right-click on your instance object --> properties) on the connections page and note the Remote Query Timeout number.

Also, Linked Servers has their own connections and query properties (expand your instance object --> Server Objects and right click on Linked Servers --> Properties. On the Server Options page, check the connection timeout and query timeout values. (Check out https://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx for information about Linked Server Properties).

If any of the three above values corresponds to the time that your query drops, you probably need to change them.

If you are running the query as a SQL Server Agent job, I would look at the job history to see if the duration corresponds to the timeout time.

That should give you a start to at least check the possibility of it being a timeout.

Answer 3

Older versions of SQL 2005 have numerous linked server bugs. Are you on the latest build?

Does the destination table have IGNORE_DUP_KEY enabled on the primary key? That could make it appear to 'drop' rows.

Are you sure that the source data is COMMITTED before the agent job runs? Because isolation levels might make it not available until then.