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

Source: Internet
Author: User
Tags mssqlserver


 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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.