SQL Server linked database: Insert does not happen on local table after writing to certain remote tables

Question

I have the following code:

CREATE PROCEDURE [dbo].[USER_ARC_EXPORT_$sp]  
AS
    DECLARE @errmsg VARCHAR(255),    
            @errno  INT,    
            @next_batch_id  NUMERIC(12,0),  
            @object_name    NVARCHAR(255),  
            @operation_name NVARCHAR(100)

    SELECT @errmsg = NULL, 
           @next_batch_id = 100  --to simplify my explanation, just setting this to 100. In the actual code it goes through a process generate a batch number

    -- Insert process start into tracking table
    INSERT INTO LocalDB.[dbo].[ARC_PROCESS_TRACKING] ([BATCH_ID],[TRAN_CREATE_TS], [TRAN_STATUS], [ETL_START_TS], [ETL_END_TS])     
        SELECT batch_id, TRAN_CREATE_TS, 'START', ETL_START_TS, ETL_END_TS  
        FROM ARC_PROCESS_TRACKING   
        WHERE BATCH_ID = @next_batch_id

    SELECT @errno = @@error     

    IF @errno <> 0    
    BEGIN       
        SELECT @errmsg = 'An error occurred while inserting LocalDB.[dbo].[ARC_PROCESS_TRACKING] with 2.',      
        GOTO error    
    END

    ---- Move data to remote server 
    INSERT INTO REMOTE.RemoteDB.[dbo].[ARC_TRANS_HEADER] 
        SELECT *    
        FROM LocalDB.[dbo].ARC_TRANSACTION_HEADER

    SELECT @errno = @@error     

    IF @errno <> 0    
    BEGIN   
        SELECT @errmsg = 'An error occurred while inserting REMOTE [ARC_TRANS_HEADER].',        
        GOTO error    
    END

    ---- Insert Tracking with status of 2 to show the data has moved
    -- **This ROW DOES NOT INSERT **
    INSERT INTO LocalDB.[dbo].[ARC_PROCESS_TRACKING] ([BATCH_ID],[TRAN_CREATE_TS], [TRAN_STATUS], [ETL_START_TS], [ETL_END_TS])     
        SELECT batch_id, TRAN_CREATE_TS, 'END', ETL_START_TS, ETL_END_TS    
        FROM ARC_PROCESS_TRACKING   
        WHERE BATCH_ID = @next_batch_id

    SELECT @errno = @@error      

    IF @errno <> 0    
    BEGIN       
        SELECT @errmsg = 'An error occurred while inserting LocalDB.[dbo].[ARC_PROCESS_TRACKING] with 2.',      
        GOTO error    
    END

    RETURN

    /* Error Handler */ 
    error:  

    INSERT INTO error_table 
        SELECT @errno, @errmsg  

    RETURN

The issue is that the last insert to the ARC_PROCESS_TRACKING table does not happen. The first insert to the ARC_PROCESS_TRACKING table works. The insert to the Remote.RemoteDB.dbo.ARC_TRANS_HEADER works for all records (20,000).

No errors are logged. If I change the procedure to write to a temp table, it works. Something with the insert into the remote table is causing the last insert not to fire. Is there a requirement with the DBLINK table that I am missing to get the subsequent insert statement to work?

Your error handling is very old style. Please convert it to a try catch where you can see the exact error, if any, that is occurring. Why don't you also try moving the last insert to the top to see if it works. You are missing a lot of code. I only see one insert into remote.remotedb statement. — benjamin moskovits, Jul 18 '19 at 23:51
@benjaminmoskovits, thanks for the reply. Yes, the error trapping is old since this is part of a legacy application which needs to run older version of SQL. In this particular case, we are running on SQL 2016. When I move the last insert statement to be before the remote server insert then it works. This doesn't solve the problem however since I need a tracking row to be inserted after the remote insert has completed. (This flags another process that it can start picking the transactions) — SRev, Jul 19 '19 at 02:16

score 0 · Answer 1 · answered Jul 19 '19 at 14:59

0

Since when you switched statements and the second select worked leads me to believe that the problem is not with MS SQL Server its most likely with the linked server you are connecting to. What may be happening is (and this happened to me when connecting to DB2) the source is not cleaning up its connection right away and is not reporting anything. It may be falling off a cliff.

Eventually the target will clean up the connection pool. You can try using a waitfor time statement between the reads. I would start with an hour

waitfor time '01:00';

and if it works slowly narrow the waitfor time until it stops working.

answered Jul 19 '19 at 14:59

benjamin moskovits

5,261
1
12
22

Thanks Benjamin, I will give that a try. I am also wondering if this is being caused by an authentication. Perhaps I should be writing this as a pull process instead of a push. – SRev Jul 19 '19 at 19:52
If it was authentication the first connection would not work. – benjamin moskovits Jul 19 '19 at 21:36

SQL Server linked database: Insert does not happen on local table after writing to certain remote tables

1 Answers1