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

Answer 2

Inserting records one by one is always considered a bad practice, but the same could be said if you're inserting millions of records in a single select/insert.

I would suggest creating a process that inserts n records at a time, while at the same time wrapping everything in a transaction to make sure that either all records are updated in that step or, in case of error, none are updated.

DECLARE @NumRecords INT
SELECT @NumRecords=COUNT(*) FROM Linked_TimeCardServer.DB.Schema.TableXX X
WHERE X.FLAG = 0

WHILE @NumRecords > 0
BEGIN
    BEGIN TRAN

    -- Get 100 Records
    SET ROWCOUNT 100

    INSERT INTO Linked_ERPServer.DB.Schema.Table01
    SELECT * FROM Linked_TimeCardServer.DB.Schema.TableXX X
    WHERE X.FLAG = 0

    If @@error <> 0 GOTO Error

    -- Update the Records that were copied
    SET ROWCOUNT 0

    Update Linked_TimeCardServer.DB.Schema.TableXX
    SET FLAG =1
    WHERE PrimareyKeyColumnId NOT IN (
        SELECT PrimareyKeyColumnId FROM Linked_ERPServer.DB.Schema.Table01
    )

    If @@error <> 0 GOTO Error

    COMMIT TRAN

    SELECT @NumRecords=COUNT(*) FROM Linked_TimeCardServer.DB.Schema.TableXX X
    WHERE X.FLAG = 0

END

Error:
    ROLLBACK TRAN