fix for SQL Server server name that is inconsistent with the default instance name

 fix for SQL Server server name that is inconsistent with the default instance nameCategory: Personal cumulative SQl SERVER database replication 2011-08-10 09:49 10157 people read reviews (0) favorite reports SQL Server server Sqlservermicrosoftserver remote connection [SQL]View Plaincopy

1. --Cause Analysis:
2. The ServerName property of the--serverproperty function returns similar information to the @ @SERVERNAME.
3. The--servername property provides the Windows server and instance names, which together form a unique server instance;
4. [Email protected] @SERVERNAME provide the local server name for the current configuration.
5. --If the default server name is not changed during installation, the ServerName property and @ @SERVERNAME return the same information. If you changed the local server name from the default server name to a different name at installation time, the @ @SERVERNAME returns the new name after the change.
6. --If the default server name is not changed during installation, but the Windows server name is changed after installation is complete, the @ @SERVERNAME still returns the server name at the time of installation, and the ServerName property returns the name of the Windows Server after the change. That is, the @ @SERVERNAME does not report changes to the Windows Server name, and the ServerName property reports changes to the Windows server name. This causes the @ @SERVERNAME and ServerName properties of the default instance of SQL Server to be inconsistent. This situation often occurs in some applications where there is an inability to connect, and sometimes we need to fix it.
7. --Repair method: (The repair method is very simple, directly execute the following statement can be)
8. Use master
9. Go
10. SELECT @ @servername
11. Select serverproperty (' servername ')
12. ------
13. IF serverproperty (' servername ') <>@ @servername
14. BEGIN
15. DECLARE @server SYSNAME
16. SET @[email protected] @servername
17. EXEC sp_dropserver @[email protected]
18. SET @server =cast (serverproperty (' servername ') as SYSNAME)
19. EXEC sp_addserver @[email protected],@local=' local '
20. END
21. ELSE
22. PRINT ' instance name is consistent with hostname without modification! '

If, after installing SQL, the server modifies the hostname, causing inconsistencies, and there were previously replication publications:

Machine name differs from instance name, workaround

Always prompt when executing exec sp_addlinkedserver ' DMGZSRV10 '

Msg 15190, Level 16, State 1, procedure sp_dropserver, line 56th
There are still remote logins or links to the server ' DMGZSRV10 '.

Also implemented

exec sp_dropserver ' DMGZSRV10 ', ' droplogins '

This prompt

The server ' LC04 ' cannot be removed because the server is used as a publisher during replication. (Microsoft SQL Server, error: 20582)

Here's the solution

Original state
1. Server name LC04 MSSQL Instance name MSSQLServer
2. An error occurred in setting up a release on LC04, subscribing to another server, or remotely accessing SQL Server on LC04 with SQL Server Manager:
Unable to connect to LC04.
-----------------------------
Additional information:

An error occurred while establishing a connection to the server. When you connect to SQL Server 2005, SQL Server does not allow remote connections under the default settings, which can cause this failure. (provider: Named pipe provider, ERROR:40-Unable to open connection to SQL Server) (Microsoft SQL Server, error: 53)


Resolve Processing

Change the computer name of LC04 to MSSQLServer as the MSSQL instance name
State at this time
SQL Server Manager can access the LC04 database remotely, but cannot subscribe, and an error occurs:
SQL Server replication requires an actual server name to connect to the server. Connections through server aliases, IP addresses, or any other alternate names are not supported. Please specify the actual server name "LC04". (replication.utilities)


Resolve Processing

On the server that LC04 (which should now be named MSSQLServer), execute the following code
SELECT @ @SERVERNAME, serverproperty (' SERVERNAME ')
Found two different, LC04 and MSSQLSERVER, respectively


Execute the following code

exec sp_droplinkedsrvlogin ' LC04 ', null
exec sp_dropserver ' LC04 ', ' droplogins '
exec sp_addserver ' MSSQLSERVER ', ' LOCAL '

Error occurred
The server ' LC04 ' cannot be removed because the server is used as a publisher during replication. (Microsoft SQL Server, error: 20582)


Resolve Processing

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 SQL Server Services


Set the Publisher properties to find the publisher's server name or LC04, causing the Publish execution error:
An error occurred while establishing a connection to the server. When you connect to SQL Server 2005, SQL Server does not allow remote connections under the default settings, which can cause this failure. (provider: Named pipe provider, ERROR:40-Unable to open connection to SQL Server) (Microsoft SQL Server, error: 53)


Using the EXEC sp_helpserver
Discover the Network_name or LC04 of a server with name Repl_distributor

Resolve Processing

Execute exec sp_setnetname ' repl_distributor ', ' MSSQLSERVER '

Set up the publisher again, publish, finish


In fact, it is possible to use the original database name in SQL Server and other applications LC04
The new alias for SQL Server can be established as LC04, which basically resolves the

• Previous article how to remove a publisher distribution
• Next timestamp application (solving concurrency problems)-Optimistic and pessimistic lock "turn"

fix for SQL Server server name that is inconsistent with the default instance name