使用事务与链接服务器通信好吗？

发布于 2024-11-06 19:27:09 字数 1107 浏览 0 评论 0原文

我有相同的本地和远程数据库结构，并且我使用连接到远程数据库链接服务器。我的本地数据库是SQL2000，远程数据库是SQL2008 我的本地表：

tbl_name
Id       Name         flag
1      Raymond         1
2      Sara            1
3      Souzan          2

我的远程表

tbl_name
Id       Name

现在我想将每条带有 falg field value=1 的记录插入到远程表中，然后删除本地数据库中插入的记录。

    create proc TransferInsertedRecords
    as
    begin
        create temp @table
        (
          id int,
          name nvarchar(10)
        )
        insert into @temp 
            select id,name from tbl_name
                   where flag=1
        Insert into RemoteServer.dbo.Worker.tbl_Names.name
        values select name from @temp

        delete from t1
                from tbl_name t1
                     inner join @temp t2
                           on t1.id=t2.id and flag=1
end

我的工作每 5 分钟运行一次此过程。如何确保远程数据库中的每条插入记录都会在本地数据库中删除？我的意思是连接丢失。假设一些数据已插入远程数据库，但在插入其余数据并在本地数据库中删除它们之前，连接丢失。

我应该使用DISTRIBUTED TRANSACTION吗？如果是的话，我应该在哪里放置COMMIT TRANSACTION或ROLLBACk语句。

原文

I have the same local and remote databases structure and I connect to remote database using
linked server.
my local database is SQL2000 and remote one is SQL2008
My local table:

tbl_name
Id       Name         flag
1      Raymond         1
2      Sara            1
3      Souzan          2

My Remote table

tbl_name
Id       Name

Now I want to insert every record with falg field value=1 into remote table and then delete the inserted records in local database.

    create proc TransferInsertedRecords
    as
    begin
        create temp @table
        (
          id int,
          name nvarchar(10)
        )
        insert into @temp 
            select id,name from tbl_name
                   where flag=1
        Insert into RemoteServer.dbo.Worker.tbl_Names.name
        values select name from @temp

        delete from t1
                from tbl_name t1
                     inner join @temp t2
                           on t1.id=t2.id and flag=1
end

I have a job that runs this procedure every 5 minutes.
How can I be sure every inserted record in remote database will delete in local database?
My mean is about connection lose. suppose some data is inserted in remote database but before inserting the rest of data and deleting them in local database the connection lose.

Should I use DISTRIBUTED TRANSACTION if so where should I put COMMIT TRANSACTION or ROLLBACk statements.

分享到QQ

分享到微博