none
Copy rows in a table from one server to another, how to ensure all the rows have been copied if a select is running on the receiving server at the time.

    Question

  • I have transfers rows that are sent from a local table to a central server where they will be picked up in a stored procedure and sent to Biztalk as XML. What happens if the select is running at the same time the rows are being copied? Can the store proc on the receiving server miss a few rows if not everything was copied before the select starts?

    What is the standard (easy) way of handling this?

    Kind Regards

    Bico

         


    Bico Bielich

    Thursday, September 06, 2012 1:25 PM

Answers

  • Yes and I suggest you to copy the data in transaction with READ_COMMITED ISOLATION. For example as follow:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
    BEGIN TRANSACTION
    BEGIN TRY
    -- TRANSFER DATA CODE GOES HERE (COPY DATA FROM LOCAL TABLE TO CENTRAL TABLE)
    COMMIT
    END TRY
    BEGIN CATCH  
        
    	DECLARE @ErrorMessage [nvarchar](4000);
        DECLARE @ErrorSeverity [int];
        DECLARE @ErrorState [int];
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    	ROLLBACK
        -- RAISERROR inside the CATCH block to return error
        -- information about the original error that caused
        -- execution to jump to the CATCH block.
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState ); -- State.
    END CATCH

    This will resolve your issue

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Thursday, September 06, 2012 2:03 PM
    • Marked as answer by bicobielich Thursday, September 06, 2012 2:27 PM
    Thursday, September 06, 2012 2:00 PM

All replies

  • Hi Bico,

    You need transactional replication between your local table and central server. This ensures as soon as the row is insterted in to the local table it will be copied to the central table.

    If you don't want to use the replication then I would suggest you to transfer rows in one transaction and then set the transaction isolation level to READCOMMITED Or SEARIALIZABLE.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.


    • Edited by Basit Farooq Thursday, September 06, 2012 1:45 PM
    • Proposed as answer by Basit Farooq Thursday, September 06, 2012 1:45 PM
    Thursday, September 06, 2012 1:43 PM
  • The only control I have is at the receiving end, the stored procedure that polls sql server to see if any orders came in ... If I set the isolation level to readcommited will it know that rows are being added from an external server?

    Thanks for your response ... 


    Bico Bielich

    Thursday, September 06, 2012 1:52 PM
  • Yes and I suggest you to copy the data in transaction with READ_COMMITED ISOLATION. For example as follow:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
    BEGIN TRANSACTION
    BEGIN TRY
    -- TRANSFER DATA CODE GOES HERE (COPY DATA FROM LOCAL TABLE TO CENTRAL TABLE)
    COMMIT
    END TRY
    BEGIN CATCH  
        
    	DECLARE @ErrorMessage [nvarchar](4000);
        DECLARE @ErrorSeverity [int];
        DECLARE @ErrorState [int];
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    	ROLLBACK
        -- RAISERROR inside the CATCH block to return error
        -- information about the original error that caused
        -- execution to jump to the CATCH block.
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState ); -- State.
    END CATCH

    This will resolve your issue

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Thursday, September 06, 2012 2:03 PM
    • Marked as answer by bicobielich Thursday, September 06, 2012 2:27 PM
    Thursday, September 06, 2012 2:00 PM