How to fix inconsistency between SQL Server server name and default Instance name

-- Cause Analysis: -- The servername attribute of the serverproperty function is similar to that returned by @ servername. The -- servername attribute provides the names of Windows servers and instances, which constitute a unique server instance. -- @ servername provides the name of the local server currently configured. -- If the default server name is not changed during installation, the servername attribute returns the same information as @ servername. If the name of the local server has been changed from the default server name to another name during installation, @ servername returns the new name after the change. -- If the default server name is not changed during installation, but the name of the Windows Server is changed after installation, @ servername still returns the name of the server during installation, the servername attribute returns the name of the modified Windows server. That is to say, @ servername does not report changes to the Windows server name. The servername attribute reports changes to the Windows server name. This will cause the @ servername and servername attributes of the default SQL server instance to be inconsistent. This problem often occurs in some applications that cannot be connected. Sometimes we need to fix this problem. -- Solution: (the solution is simple. Execute the following statement directly.) use master go select @ servername select serverproperty ('servername') ------ if serverproperty ('servername ') <> @ servername begin declare @ server sysname set @ server =@servername exec sp_dropserver @ Server = @ server set @ Server = cast (serverproperty ('servername') as sysname) exec sp_addserver @ Server = @ server, @ local = 'local' end else print 'the Instance name is the same as the host name, so no modification is required! '

If, after installing SQL, the server modifies the host name, causing inconsistency, and there is a copy release before:

The machine name is different from the instance name. solution:

Execute exec sp_addmediaserver
Always prompt when 'dmgzsrv10'

Message 15190, level 16, status 1, process sp_dropserver, 56th rows
Remote logon or link logon to 'dmgzsrv10' is still available.

Run again

Exec sp_dropserver 'dmgzsrv10', 'droplogins'

Prompt

The server 'lc04 'cannot be deleted because it is used as the Publishing Server during replication. (Microsoft SQL Server, error: 20582)

The solution is as follows:

Original Status
1. server name lc04 MSSQL Instance name MSSQLServer
2. Publishing was established on lc04, and an error occurred when subscribing to another server or remotely accessing SQL Server on lc04 using the SQL Server Manager:
Cannot connect to lc04.
-----------------------------
Other information:

An error occurred while establishing a connection with the server. When you connect to SQL Server 2005, the default setting does not allow remote connection to SQL Server may cause this failure. (Provider: named pipeline provider, error: 40-unable to open the connection to SQL Server) (Microsoft SQL Server, error: 53)

Solution

Change the computer name of lc04 to the same name as that of MSSQLServer and MSSQL.
Status
The SQL Server Manager can remotely access the lc04 database but cannot subscribe to the database. The following error occurs:
SQL Server replication requires an actual server name to connect to the server. Connection by server alias, IP address, or any other backup name is not supported. Specify the actual server name "lc04 ". (Replication. Utilities)

Solution

Run the following code on the server lc04 (now named MSSQLServer ):
Select @ servername, serverproperty ('servername ')
Two differences are found: lc04 and MSSQLServer.

Run the following code:

Exec sp_droplinkedsrvlogin 'lc04 ', null
Exec sp_dropserver 'lc04 ', 'droplogins'
Exec sp_addserver 'mssqlserver ', 'local'

Error
The server 'lc04 'cannot be deleted because it is used as the Publishing Server during replication. (Microsoft SQL Server, error: 20582)

Solution

Select * From MSDB. DBO. msdistpublishers
Delete from MSDB. DBO. msdistpublishers
Select * From MSDB. DBO. msdistpublishers
Exec sp_droplinkedsrvlogin 'lc04 ', null
Exec sp_dropserver 'lc04 ', 'droplogins'
Exec sp_addserver 'mssqlserver ', 'local'
Restart sqlserver Service

When the publishing server attribute is set, the server name of the Publishing Server is lc04, causing a publishing execution error:
An error occurred while establishing a connection with the server. When you connect to SQL Server 2005, the default setting does not allow remote connection to SQL Server may cause this failure. (Provider: named pipeline provider, error: 40-unable to open the connection to SQL Server) (Microsoft SQL Server, error: 53)

Use exec sp_helpserver
The network_name or lc04 of the server whose name is repl_distributor is found

Solution

Execute exec sp_setnetname 'repl _ distributor ', 'mssqlserver'

Set the Publishing Server again, and publish the server.

In fact, the original database name lc04 may be used in sqlserver and other applications.
You can create a new alias lc04, which basically solves the problem.