SQL Server could not create a subscription for Subscriber 'ServernameInstanceName'.The value for the @sync_method parameter is not valid

  • Article

Recently we worked on a support case where creation of a Subscription was failing with the following error:-

TITLE: New Subscription Wizard
------------------------------
SQL Server could not create a subscription for Subscriber 'Servername\InstanceName'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The value for the @sync_method parameter is not valid. Could not subscribe to publication 'Publication_name' because non-SQL Server Subscriber 'Servername\InstanceName' only supports values of  'character', 'bcp character', 'concurrent_c', and 'database snapshot character' for the @sync_method parameter.
The subscription could not be found.
Changed database context to 'test'. (Microsoft SQL Server, Error: 14095)

Cause:
The issue is due to an incorrect entry in system table (master..sysservers) due to which SQL thinks that current subscriber
is NON-SQL. Interestingly the entry for the current subscriber server in master..sysservers (on publisher server) was showing 1 for "nonsqlsub" column.

Solution:
First look at sys.servers and confirm whether the nonsqlsub column is set to 1 for the concerned subscriber. If the above is true then run the following TSQL to change nonsqlsub column from 1 to 0

 exec sys.sp_serveroption 'Servername\InstanceName','nonsqlsub',False

Note – Please replace Servername\InstanceName with concerned subscriber\instancename. After this, re-attempt the creation of subscription and it should succeed this time.

References: Sys.servers :- https://msdn.microsoft.com/en-us/library/ms178530.aspx

Written By :- Manikandan Veeruchamy

Support Engineer, Microsoft SQL server Support

Reviewed By: – Akbar Farishta

Technical Lead, Microsoft SQL server Support