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.)
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?