Wednesday, 29 April 2015

SQL Server is unable to connect to server 'ServerName' - Error while setup replication by serverku

One day when I was setting up replication in my local machine, when I clicked to create a new publication from replication tab, I faced one error and the details,

Error :
TITLE: New Publication Wizard
------------------------------

SQL Server is unable to connect to server 'PARESH\MSSQLSERVER2012'.
------------------------------
ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server.
Specify the actual server name, 'ADMIN\MSSQLSERVER2012'. (Replication.Utilities)


This is because of earlier my machine name was “ADMIN” and SQL server installed at that time. After some time I changed my machine name to “PARESH”. Currently SQL Server instance showing “PARESH\MSSQLSERVER2012” from SSMS and showing “"ADMIN\MSSQLSERVER2012” while running @@SERVERNAME from query analyzer. So this is the reason why I am facing above mentioned error. This will raise same error while trying to configure replication and the default SQL instance connected with “(local)” or “.” as a name.

Solution :
I have one option which we have to connect SQL Server with the actual server name and it will be done with change SQL server name to machine name\instance name first. You can change the SQL Server instance name with following script,
EXEC sp_dropserver 'ADMIN\MSSQLSERVER2012' -- <Old Name\Instance>
GO

EXEC sp_addserver 'PARESH\MSSQLSERVER2012','local' -- <New Name\Instance>
GO
When you run a first statement which in above mentioned script, you may face the following error too,

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'ADMIN\MSSQLSERVER2012'.


And we have solved too,
EXEC sp_dropremotelogin 'ADMIN\MSSQLSERVER2012'; -- <Old Name\Instance>
GO
After running all steps we can go ahead for the next step of replication. To check renamed server name with @@SERVERNAME. Among you also faced this issue also. Can we have the other options for the solution here?

No comments:

Post a Comment

Please Use Good Leanguage