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
- --Cause Analysis:
- The ServerName property of the--serverproperty function returns similar information to the @ @SERVERNAME.
- The--servername property provides the Windows server and instance names, which together form a unique server instance;
- [Email protected] @SERVERNAME provide the local server name for the current configuration.
- --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.
- --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.
- --Repair method: (The repair method is very simple, directly execute the following statement can be)
- Use master
- Go
- SELECT @ @servername
- Select serverproperty (' servername ')
- ------
- IF serverproperty (' servername ') <>@ @servername
- BEGIN
- DECLARE @server SYSNAME
- SET @[email protected] @servername
- EXEC sp_dropserver @[email protected]
- SET @server =cast (serverproperty (' servername ') as SYSNAME)
- EXEC sp_addserver @[email protected],@local=' local '
- END
- ELSE
- 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