Experience in [SQL SERVER Distributed Transaction solution]

You often need to modify the data in the database through the stored procedure. You need to configure distributed transactions on the source and target database servers. The configuration process is cumbersome, distributed transactions are caused by both the operating system and SQL Server. After reading the article "SQL Server Distributed Transaction solution", I feel that a set of standardized configuration steps have basically been formed, however, section 6th does not mention the measures to be taken when the SQL server name is different from the computer name.

 

For newly installed machines, there should be no problems according to the key points in the Chinese document. If you still have problems with the above configuration, refer to the English document. It is likely that the computer name (Network Name) has been modified after SQL Server is installed, resulting in the inconsistency between the SQL server name and the computer name.

-- Query the SQL Server Name
Select @ servername

It is best to ping each other's computer names on the server first to see if the returned IP address is correct, to prevent duplicate computers on the network. If the IP address resolved by the computer name is correct, modify the SQL server name according to the computer name.

-- SQL Server 2005 modify server name
Sp_dropserver <old_name \ InstanceName>
Go
Sp_addserver <new_name \ InstanceName>, local
Go

-- SQL Server 2000 modify server name
Use master
Go
Sp_configure 'Allow up', 1
Reconfigure with override
Go
Update sysservers set srvname = <new_name> where srvid = 0
Update sysservers set datasource = <ip> where srvid = 1
Go
Sp_configure 'Allow up', 0
Reconfigure with override
Go

 

Chinese Reference document: SQL Server Distributed Transaction Solution

How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server