在链接服务器之间归档数据-交易问题 [英] Archiving data between linked servers - transaction issue

问题描述

尝试通过链接服务器连接将一些数据从一个数据库表存档到另一个数据库.

Trying to archive some data from one database table to another over linked server connection.

到目前为止，已经相当接近了，但是当前却出现了令人困惑的交易异常.

So far have gotten fairly close however getting a puzzling transaction exception currently.

这是T-SQL:

USE ArchiveDatabase

-- Declare date we are archiving to
DECLARE @ArchiveDate DATETIME
SET @ArchiveDate = DATEADD(MONTH, -2, GETDATE())

-- Create temp table
CREATE TABLE #DeleteIDs (ID int)

-- Continue looping while rows exist
WHILE EXISTS (SELECT TOP 1 * FROM [LINKEDSERVER].MasterDatabase.dbo.Logging WITH(NOLOCK) WHERE [Date] < @ArchiveDate)

    BEGIN

        BEGIN TRANSACTION

            -- Get batch of IDs to archive
            INSERT INTO #DeleteIDs ([ID])
            (
                SELECT TOP 1000 [ID]
                FROM [LINKEDSERVER].MasterDatabase.dbo.Logging WITH(NOLOCK)
                WHERE [Date] < @ArchiveDate
            )

            -- Archive where ID is within our current batch
            INSERT INTO ArchiveDatabase.dbo.DataConnect_Logging
            ([Date],[Description],[AccountName],[ConnectionIPAddress],[HttpStatusCode],[HttpMethod],[Url])
            (
                SELECT [Date],[Description],[AccountName],[ConnectionIPAddress],[HttpStatusCode],[HttpMethod],[Url]
                FROM [LINKEDSERVER].MasterDatabase.dbo.Logging WITH(NOLOCK)
                WHERE ID IN
                (
                    SELECT ID FROM #DeleteIDs
                )
            )

            -- Remove where ID is within our current batch
            DELETE FROM [LINKEDSERVER].MasterDatabase.dbo.Logging 
            WHERE ID IN (SELECT ID FROM #DeleteIDs)

            -- Empty IDs
            DELETE FROM #DeleteIDs

            WAITFOR DELAY '00:00:02'

        -- Any errors, roll back, otherwise commit
        IF @@Error <> 0
            ROLLBACK TRANSACTION
        ELSE
            COMMIT TRANSACTION

    END 

DROP TABLE #DeleteIDs

这是一个例外:

(1000 row(s) affected)
(1000 row(s) affected)
OLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 38
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "LINKEDSERVER". A nested transaction was required because the XACT_ABORT option was set to OFF. 

推荐答案

您是否尝试过设置

Have you tried setting XACT_ABORT to on at the start of your script?

SET XACT_ABORT ON

不相关，但循环不能写为:

Unrelated, but couldn't the loop be written as:

-- Continue looping while rows exist
WHILE EXISTS (SELECT 1 FROM [LINKEDSERVER].MasterDatabase.dbo.Logging WITH(NOLOCK) 
              WHERE [Date] < @ArchiveDate)

或更妙的是，重写一下，以免每次循环迭代都不会两次击中链接表.

Or even better, rewrite so that you don't hit the linked table twice per loop iteration.

这篇关于在链接服务器之间归档数据-交易问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！