Sqlserver distributed transaction usage instance

Time:2022-5-8

Copy codeThe code is as follows:
–BEGIN DISTRIBUTED TRANSACTION [transactionname]
–Marks the beginning of a TSQL distributed transaction managed by the distributed transaction coordinator MSDTC
–Server a server is the master server. When the connection issues a subsequent commit transaction or
–When the rollback transaction statement, the master server requests MSDTC to manage the involved servers
–Completion of distributed transactions

–Sqlserver uses a linked server or a remote server as a platform for distributed transaction processing to provide
–Remote stored procedure call and distributed query

–When using distributed transactions to make a remote stored procedure call and a distributed query, in server a
–Begin distributed transaction is issued on server B, which calls the stored procedure on server B
–And another stored procedure on server C, and the stored procedure on server C executes one on server D
–For distributed query, four sqlserver servers enter the distributed transaction, and server a is the creator of the transaction
–And control server

–Create a distributed transaction and delete a record in the local and remote databases at the same time, where the remote sqlserver
–The instance name of is remoteserver. Both local and remote databases commit or roll back the transaction at the same time.
–Note that four part name qualification rules are used when executing distributed queries or calling stored procedures

–Premise: the MSDTC service of the local machine and the MSDTC service of the remote machine should be opened
–The local machine and the remote machine can ping each other
–The database ports can communicate with each other through telnet
–Create a link server to the remote machine win7u-20130414z
USE [GPOSDB]
GO
SELECT * FROM [SystemPara] WHERE [Name]=’HDTPort’
SELECT * FROM [WIN7U-20130414Z].[GPOSDB].dbo.[SystemPara] WHERE [Name]=’HDTPort’

 

USE [GPOSDB]
GO
BEGIN DISTRIBUTED TRANSACTION
–Delete a record from the local database
DELETE FROM [JOE].[GPOSDB].[DBO].[SystemPara]
WHERE [Name]=’HDTPort’

–Delete a record from the remote database
DELETE FROM [GPOSDB].[dbo].[SystemPara]
WHERE [Name]=’HDTPort’

COMMIT TRAN
GO

–I tried the following because I used the linked server method in the two-way SQL server access,
–In this case, you only need to use the original statement to access the other party’s database:
–select  *  from  linkedServerA.dbo.table1
–Amend to read:
–select  *  from  dbo. Table1 is enough.
–Mark it down for later resolution.