Solution for publishing subscriptions that cannot be published after the computer name is modified after the SQL statement is installed

Method 1:

When configuring data synchronization, the databases of the two synchronized machines must be connected by the default Instance name. If your default instance has been deleted, you will be prompted when you create and release the instance:
"Microsoft SQL Server cannot access these components because the replication components are not installed on this instance of SQL Server. For details, see SQL Server ......"

Note that there is an additional prompt, which means that the default Instance name and the current instance name are inconsistent. In fact, this is the main cause of the failure to copy. After solving this problem, you can copy it.

The reason is that after SQL Server is installed, the computer name is modified.

Solution: "replication requires an actual server name to connect to the server. The server alias, IP address, or any other backup name cannot be used for connection ."

I. First, check whether your default Instance name is consistent with the current instance name. We will create a query in sqlserver2005 management studio.

Select @ Servername,Serverproperty('Servername')

Two different results are displayed, indicating that the instance running first is not the default instance. You can change the running instance to the default instance in the following ways.

 

 1  Use Master
 2   Go  
 3   --  Set two variables  
  4   Declare   @ Serverproperty_servername   Varchar ( 100 ),
 5   @ Servername   Varchar ( 100 )
 6   --  Obtains information about the Windows NT server and the instance associated with the specified SQL server instance.  
  7   Select   @ Serverproperty_servername   =   Convert ( Varchar ( 100 ), Serverproperty ( '  Servername ' ))
 8   --  Returns the name of the local server running Microsoft SQL Server.  
  9   Select   @ Servername   =   Convert ( Varchar ( 100 ), @ Servername )
 10  --  Display the obtained Parameters  
  11   Select   @ Serverproperty_servername , @ Servername  
 12   --  If @ serverproperty_servername is different from @ servername (because you changed the computer name), run the following  
  13   --  Delete the wrong server name  
 14   Exec Sp_dropserver @ Server  =  @ Servername  
 15   --  Add the correct server name  
  16   Exec Sp_addserver @ Server  =  @ Serverproperty_servername , @ Local =  '  Local  ' 

 

Then restart SQL and runSelect @ Servername,Serverproperty('Servername')

 

 

Method 2:

You need to configure a publishing and subscription, but always report: "SQL Server replication requires an actual server name to connect to the server. You cannot connect to the server by alias, IP address, or any other backup name. Specify the actual server name "XXXX" (replication utlities ). "

After some analysis, we found that the machine name was modified after SQL Server was installed. Run the following two statements:

1 UseMaster
2 
3 Go 
4 
5 Select @ Servername;
6 SelectServerproperty ('Servername')
 

If the two results are inconsistent, the machine name is changed and the above error is reported during configuration replication.

 1   --  To fix this problem  
  2  --  Run the following statement to restart the SQL service.  
  3  
 4   If Serverproperty ( '  Servername  ' ) <>   @ Servername  
 5   Begin  
 6   Declare  @ Server Sysname
 7   Set   @ Server   =   @ Servername  
 8   Exec Sp_dropserver @ Server   =   @ Server  
 9   Set   @ Server   =  Cast (Serverproperty ( '  Servername  ' ) As Sysname)
 10   Exec Sp_addserver @ Server   =   @ Server , @ Local   =   '  Local  '  
11   End 

Finally, don't forget to restart the service. After the service is started, run:

 
1 UseMaster
2 
3 Go 
4 
5 Select @ Servername;
6 SelectServerproperty ('Servername')
 

Everything works, and replication works.