Cry about...
SQL Server Troubleshooting
SQLServer Enterprise Manager could not configure 'server' as the Distributor for 'server'
Symptom:
When trying to create a publication (for replication) the following error is produced:
SQLServer Enterprise Manager could not configure 'server' as the Distributor for 'server'
Error 18482: Could not connect to server 'SSSS' because 'SSSS' is not defined as a remote server.
Where ‘server’ is the name of the local server (which is also acting as the distributor) and ‘SSSS’ is a different name.
Cause:
This error has been observed on a server that had
been renamed after the original installation of SQL Server, and
where the SQL Server configuration function ‘@@SERVERNAME
’
still returned the original name of the server. This can be
confirmed by:
select @@SERVERNAME
go
This should return the name of the server. If it does not then follow the procedure below to correct it.
Remedy:
To resolve the problem the server name needs to be updated. Use the following:
sp_addserver real-server-name, LOCAL
if this gives an error complaining that the name already exists then use the following sequence:
sp_dropserver real-server-name
go
sp_addserver real-server-name, LOCAL
go
If instead the error reported is 'There is already a local server.' then use the following sequence:
sp_dropserver old-server-name
go
sp_addserver real-server-name, LOCAL
go
Stop and restart SQL Server.
Other Possible Remedies
Phillip Ushijima has told me that he had an issue
with pushing a subscription with the error messages indicated. His
solution was to edit the SQL Server registration properties in the
Enterprise Manager MMC to match the name returned by @@servername
.
Whilst he is not sure whether it was significant, he had previously added an alias into the client configuration on the distributor/publisher with a matching name (although this was unsuccessful in resolving the error on its own).
These notes are believed to be correct for SQL Server 7 and may apply to other versions as well.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.