Copy records using stored procedure from SQL Server to SQL Server ( 2005)

Question

I have one simple stored procedure to select, format and copy records from TimeCard database to our ERP database. Both are SQL Servers.

It is running on SQL Server Agent as a scheduled job. The code look like this

INSERT INTO linked_erpserver.db.SCHEMA.table01 
SELECT * 
FROM   linked_timecardserver.db.SCHEMA.tablexx X 
WHERE  X.flag = 0 

UPDATE linked_timecardserver.db.SCHEMA.tablexx 
SET    flag = 1 
WHERE  flag = 0 

Now, suppose if there is a big number of records and connection to linked servers are failed, it will be catastrophic effect.

How can I deal with this? Should I select records one by one, insert, update and commit that one record.

EDIT: We are using SQL Server 2005

Answer 1

Use a Distributed Transaction

You need to wrap your code in a transactional (all/or nothing) unit. To support this, the MSDTC service needs to be installed, running, and possibly configured on both Windows/SQL Servers. Then you need to use the BEGIN DISTRIBUTED TRANSACTION T-SQL syntax. Starting in SQL Server 2005 you have the magic of using XACT_STATE whereas we used to only have XACT_ABORT which did not offer a complete solution(only failed severity levels 16 or above).

XACT_STATE https://learn.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql

Updated: Here's the code for SQL 2005-2017:

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION;
INSERT INTO linked_erpserver.db.SCHEMA.table01 

SELECT * 
FROM   linked_timecardserver.db.SCHEMA.tablexx X 
WHERE  X.flag = 0 

UPDATE linked_timecardserver.db.SCHEMA.tablexx 
SET    flag = 1 
WHERE  flag = 0 

IF (XACT_STATE()) < 0 ROLLBACK TRANSACTION
--if XACT_STATE is 0, there is nothing to actually commit.
IF (XACT_STATE()) = 1 COMMIT TRANSACTION; 

Here's a link that shows how to get to the MS DTC configuration:

https://support.resolver.com/hc/en-ca/articles/207161116-Configure-Microsoft-Distributed-Transaction-Coordinator-MSDTC-

Note: Apparently, XACT_STATE can return a -2 as well for things like a divide by zero error--thus the < 0 Rollback in the code above. This doesn't seem to be well documented and makes me wonder how many more negative values XACT_STATE can actually have.

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-transactions