SQL Server Distributed Transaction Usage instance

Respecting the original works: This article is for reference from http://www.jb51.net/article/43540.htm

--begin distributed TRANSACTION [Transactionname]
--Flag a TSQL distributed transaction managed by the Distributed Transaction Coordinator MSDTC starts
--server a server is the primary control server. When a connection issues a subsequent commit transaction or
--rollback TRANSACTION statement, the master server requests that MSDTC be managed between the servers involved
--Completion of distributed transactions

--sqlserver uses a linked server or remote server as a platform for distributed transaction processing, providing
--Remote stored procedure calls and distributed queries

--When a remote stored procedure call and a distributed query are made using a distributed transaction, the server A
--on the issue of Begin distributed TRANSACTION, which calls the stored procedure on server B
--and another stored procedure on Server C, and the stored procedure on Server C performs a
--distributed queries, four SQL Server servers enter the distributed transaction, and Server A is the creator of the transaction
--and control server

--Create a distributed transaction that deletes a record simultaneously on the local and remote databases, where the remote SQL Server
--the instance name is RemoteServer. Both local and remote databases commit at the same time or roll back the transaction at the same time.
--Note that when you execute a distributed query or call a stored procedure, you use the 4-part name qualification rule

--Premise: The MSDTC service of the local MSDTC and the remote machine to open
--The machine and the remote machine can ping each other
--The database port can telnet to each other
--Create a linked server to a remote machine ITSV

if exists (SELECT * from sys.servers where name= ' dbsever\mssql2008 ')
EXEC sp_dropserver ' dbsever\mssql2008 ', ' droplogins '
EXEC sp_addserver ' dbsever\mssql2008 '
EXEC sp_addlinkedsrvlogin ' dbsever\mssql2008 ', ' false ', NULL, ' sa ', ' 123321 '
EXEC sp_serveroption ' dbsever\mssql2008 ', ' data access ', ' on '-the server ' dbsever\mssql2008 ' is not configured for data access. Add this sentence.

Set XACT_ABORT on-for most OLE DB providers, including SQL Server, you must set the Xact_abort in the data modification statement in the implicit or display transaction to ON. The only case where this option is not required is when the provider supports nested transactions.

Use [Svtccdata]
GO
SELECT * FROM [Vj17a69hhc4zmas\mssqlserver1]. [Svtccdata]. [dbo]. [2014TestRemote] WHERE [name]= ' LT3 '
SELECT * FROM [dbsever\mssql2008]. [Scjzdata]. [dbo]. [2014TestRemote] WHERE [name]= ' LT3 '

Use [Svtccdata]
GO
BEGIN Distributed TRANSACTION
--Delete a record from the local database
DELETE from [Vj17a69hhc4zmas\mssqlserver1]. [Svtccdata]. [DBO]. [2014TestRemote]
WHERE [name]= ' LT3 '

--Delete a record from the remote database
DELETE from [dbsever\mssql2008]. [Scjzdata]. [dbo]. [2014TestRemote]
WHERE [name]= ' LT3 '

COMMIT TRAN
GO

--The personal attempt was made because of the chained access (LinkedServer) in two-way SQL Server access.
--In this case, you only need to make the original access to the other database statement:
--select * from LinkedServerA.dbo.table1
--Modified to:
--select * from Dbo.table1.
--marked for later resolution.

SQL Server Distributed Transaction Usage instance