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

hi @rauf have you tried the `BEGIN TRAN` in front of your transactions? -> https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/ — Andy K, Sep 07 '17 at 14:25
@AndyK not sure that would work across linked servers. Perhaps begin distributed transaction? — Eli, Sep 07 '17 at 14:28
@Rauf: This link might help as well: https://stackoverflow.com/questions/506602/best-way-to-work-with-transactions-in-ms-sql-server-management-studio Also, sending to a staging table and verifying the staging table is correct before then trying to commit it to your ERP would give you extra confidence. — abraxascarab, Sep 07 '17 at 14:28

Sting · Accepted Answer · 2017-09-07T15:43:41.513

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

Sorry to tell, I forgot to mention SQL Server version. We are still using SQL Server 2005 — Rauf, Sep 07 '17 at 15:05
While the online documentation did not show that SQL 2005 supported XACT_STATE I just verified that it is supported in SQL 2005 via a locally installed SQL 2005 Books Online. — Sting, Sep 07 '17 at 15:22
How can I check if `MSDTC service` is installed ? Unfortunately, we don't have a DBA so that I don't want to any additional installation to both of the 'fromDB' and 'toDB'. That is I use another PC with SQL Server 2005 to get data from source db to destination db. — Rauf, Sep 10 '17 at 09:03

score 1 · Answer 2 · answered Sep 07 '17 at 15:00

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

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

2 Answers2