Thursday, August 25, 2016

Remove SQL Replication in Publisher and Subscriber after changing server names

Remove SQL Replication even when the publisher or Subscriber Server is not available

Existing server/computer are renamed and given new names. If you are trying to update the Instance name of  SQL Server. it wont allow you to change the name.  And if you try to remove subscriber and publishers Forcefully  also will through below error.

OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

Solution to drop subscriber and publisher are as below (As per MS This procedure should be used only if other methods of removing replication objects have failed)

Declare @repdb as sysname
Set @repdb = 'MyPublisherDatabase' 

EXEC sp_removedbreplication @repdb