console error

[dry11orange]

I tried like this ./SyncChanges.Console.exe ../test/config.json
config.json like this

{
  "ReplicationSets": [
    {
      "Name": "Test",
      "Source": {
        "Name": "Primary",
        "ConnectionString": "Data Source=10.0.0.3;Initial Catalog=testdb;Integrated Security=false;MultipleActiveResultSets=True;User Id=sa;Password=123456"
      },
      "Destinations": [
        {
          "Name": "Secondary 1",
          "ConnectionString": "Data Source=10.0.0.6;Initial Catalog=testdb;Integrated Security=false;MultipleActiveResultSets=True;User Id=sa;Password=123456"
        }
      ],
      "Tables": [ "Table1", "Table2", "Table3" ]
    }
  ]
}

And error like this

2021-02-06 23:08:05.6353|INFO|Getting replication information for replication set Test 
2021-02-06 23:08:05.7683|FATAL|Error getting tables to replicate from source database System.Data.SqlClient.SqlException (0x80131904): '=' 附近有语法错误。
'X' 附近有语法错误。
   在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   在 System.Data.SqlClient.SqlDataReader.get_MetaData()
   在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   在 NPoco.Database.ExecuteReaderHelper(DbCommand cmd)
   在 NPoco.Database.<ExecuteDataReader>d__182.MoveNext()
--- 引发异常的上一位置中堆栈跟踪的末尾 ---
   在 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   在 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   在 NPoco.AsyncHelper.RunSync[T](Task`1 task)
   在 NPoco.Database.<QueryImp>d__181`1.MoveNext()
   在 System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   在 System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   在 SyncChanges.Synchronizer.GetTables(DatabaseInfo dbInfo)
ClientConnectionId:5f6ce11c-c9b4-4bff-a462-a8a886253391
Error Number:102,State:1,Class:15 HelpLink.ProdName: Microsoft SQL Server;HelpLink.ProdVer: 10.50.1600;HelpLink.EvtSrc: MSSQLServer;HelpLink.EvtID: 102;HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink;HelpLink.LinkId: 20476
2021-02-06 23:08:05.7838|ERROR|Error synchronizing databases for configuration ../test/config.json System.Data.SqlClient.SqlException (0x80131904): '=' 附近有语法错误。
'X' 附近有语法错误。
   在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   在 System.Data.SqlClient.SqlDataReader.get_MetaData()
   在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   在 NPoco.Database.ExecuteReaderHelper(DbCommand cmd)
   在 NPoco.Database.<ExecuteDataReader>d__182.MoveNext()
--- 引发异常的上一位置中堆栈跟踪的末尾 ---
   在 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   在 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   在 NPoco.AsyncHelper.RunSync[T](Task`1 task)
   在 NPoco.Database.<QueryImp>d__181`1.MoveNext()
   在 System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   在 System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   在 SyncChanges.Synchronizer.GetTables(DatabaseInfo dbInfo)
   在 SyncChanges.Synchronizer.Init()
   在 SyncChanges.Synchronizer.Sync()
   在 SyncChanges.Console.Program.Sync()
ClientConnectionId:5f6ce11c-c9b4-4bff-a462-a8a886253391
Error Number:102,State:1,Class:15 HelpLink.ProdName: Microsoft SQL Server;HelpLink.ProdVer: 10.50.1600;HelpLink.EvtSrc: MSSQLServer;HelpLink.EvtID: 102;HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink;HelpLink.LinkId: 20476

I have no idea what to do

• could example file config.example.json more exactly

[mganss]

It seems to fail when executing the query from here:

SyncChanges/SyncChanges/Synchronizer.cs

Lines 206 to 219 in 78c21cb

	var sql = @"select TableName, ColumnName, iif(max(cast(is_primary_key as tinyint)) = 1, 1, 0) PrimaryKey from
	(
	select ('[' + s.name + '].[' + t.name + ']') TableName, ('[' + COL_NAME(t.object_id, a.column_id) + ']') ColumnName,
	i.is_primary_key
	from sys.change_tracking_tables tr
	join sys.tables t on t.object_id = tr.object_id
	join sys.schemas s on s.schema_id = t.schema_id
	join sys.columns a on a.object_id = t.object_id
	left join sys.index_columns c on c.object_id = t.object_id and c.column_id = a.column_id
	left join sys.indexes i on i.object_id = t.object_id and i.index_id = c.index_id
	where a.is_computed = 0
	) X
	group by TableName, ColumnName
	order by TableName, ColumnName";

What happens if you execute the following query in SSMS (using the same login as in the config.json above)?

select TableName, ColumnName, iif(max(cast(is_primary_key as tinyint)) = 1, 1, 0) PrimaryKey from
(
select ('[' + s.name + '].[' + t.name + ']') TableName, ('[' + COL_NAME(t.object_id, a.column_id) + ']') ColumnName,
i.is_primary_key
from sys.change_tracking_tables tr
join sys.tables t on t.object_id = tr.object_id
join sys.schemas s on s.schema_id = t.schema_id
join sys.columns a on a.object_id = t.object_id
left join sys.index_columns c on c.object_id = t.object_id and c.column_id = a.column_id
left join sys.indexes i on i.object_id = t.object_id and i.index_id = c.index_id
where a.is_computed = 0
) X
group by TableName, ColumnName
order by TableName, ColumnName

Also, which version and edition of SQL Server are you running?

[dry11orange]

I got error message like this by sql server 2008 R2

消息 102，级别 15，状态 1，第 1 行
'=' 附近有语法错误。
消息 102，级别 15，状态 1，第 12 行
'X' 附近有语法错误。

[mganss]

Does this error message occur when you run the query in SSMS?

[mganss]

Got it. It's the use of iif which is supported only from SQL Server 2012 onwards. I've replaced it with coalesce in 2.0.278 and now it should work in SQL Server 2008.

[dry11orange]

iif -> coalesce

[mganss]

@dry11orange You can just grab the latest release from here: https://github.com/mganss/SyncChanges/releases/tag/v2.0.278
The issue has been fixed there.