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’.
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)
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.
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 :- http://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