Wednesday, 6 May 2015

Cannot drop server 'x' because it is used as a Distributor in replication - Error when deleting linked server in SQL Server by serverku

Recently, while I was working with failover, I was needing to change data source of linked server. So linked server name remains same but the data source of SQL server point change. I tried to change data source and also tried to delete it too, but it was not worked and raised an error below,
Drop failed for LinkedServer 'x'.
Cannot drop server 'x' because it is used as a Distributor in replication.
I checked in replication and it's not used in replication. Finally, I got solution using sp_serveroption system object which sets server options for remote servers and linked servers as follows script which run against master database.
EXEC master.dbo.sp_serveroption 
@server=N'LinkedServer', -- Put linked server name here
@optname=N'dist',
@optvalue=N'false'
GO
This script makes option value to ‘false’ for option name ‘Dist’ for passing specified server. Please read all option names here. This changed can be done from the user interface with linked server Property –> Server Options.


May be you received such relevant errors and solution too. Please share your thoughts for such kinds of error.

No comments:

Post a Comment

Please Use Good Leanguage