4

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?

  • 2
    Show the definition of the view. Does it use TOP, or NOLOCK, or QUERYTRACEON, or OPTION, etc. etc. – Aaron Bertrand Feb 28 '17 at 19:04
  • @AaronBertrand, it has none of those things. Just for reference, I wrote documents, so I know it's not doing anything crazy, it's a pretty straightforward query, albeit with a large number of JOINs. – nateirvin Feb 28 '17 at 19:42
  • 3
    You haven't indicated HOW you know that only half the rows are being inserted into the target table. Are you saying that if you run the select from the view on the actual server-01 server, the number of rows are not the same as running the select on server-00 (without the insert) using the linked server. In other words, I'm trying to completely remove the 'insert' part of the problem and focus on the select count from server-01 and server-00 (using the linked server). Also, could there be a separate process that's deleting rows from the target after the insert? – Scott Hodgin Feb 28 '17 at 19:54
  • @ScottHodgin, I'm running SELECT COUNT(documentID) FROM documents on SERVER-01 to get the 'expected' count, then doing the same thing on SERVER-00 to get the 'actual' count. Expected is 5 million, Actual is 3.5 million. – nateirvin Feb 28 '17 at 20:43
3

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]
')
  • if ARITHABORT is on and ANSI_WARNINGS are off then it means that an overflow will result in null rather than an error. How does this affect the number of rows? – Martin Smith Mar 2 '17 at 7:48
  • @MartinSmith, ARITHABORT is unset/OFF, and ANSI_WARNINGS is set to ON in the linked server sessions. – nateirvin Mar 2 '17 at 15:57
0

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.

0

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.

  • Oh I'm certain we're not on the latest build. We're trying to retire SERVER-00 entirely, but we've gotta maintain it till then. That primary key does not have IGNORE_DUP_KEY enabled, thanks for the reminder! I am certain that the source data is COMMITTED before the job starts. – nateirvin Mar 1 '17 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.