The SQL Error Number is 102, State is 1, Severity is 15, Error is 'Incorrect syntax near 'f8a8d2f'.', Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170

Data Integration CData Sync
, , ,
1

:45:17.636-06:00  1  [42|Q-Id]  [META|Schema] Executed  column schema query: [App_History] Success: 4 results (268 ms)
2024-09-10T21:45:17.636-06:00  2  [42|Q-Id]  [EXEC|Messag] Executed query: [SELECT *, IsPseudoColumn FROM sys_tablecolumns WHERE  SchemaName = @schema AND  TableName = @table] Success: (268 ms)
2024-09-10T21:45:17.636-06:00  2  [42|Q-Id]  [EXEC|Parsed] Executing query: [SELECT TOP 100000 * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 ORDER BY [ID] ASC]
2024-09-10T21:45:17.636-06:00  2  [42|Q-Id]  [EXEC|Parsed] Parameter: pk_b0 = 0f8a8d2f-1266-4f33-86ed-0d94f446125c
2024-09-10T21:45:17.636-06:00  5  [42|Q-Id]  [META|Schema] Engine Invalid object name ‘App_History’
2024-09-10T21:45:17.636-06:00  3  [42|Q-Id]  [TCP |Send  ] Send Begin - Length: 274
2024-09-10T21:45:17.636-06:00  5  [42|Q-Id]  [TCP |Send  ]

[SQL |Messag] Query Failed: [SELECT TOP 100000 * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 ORDER BY [ID] ASC]. Error: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘f8a8d2f’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170

Why am I getting this error. Can someone help me. Thank you

2

What is the source (database, file, etc.) connection?  Any questions like this will be highly dependent on what you are doing.

Have you tried executing a similar query in whatever querying tools are available to you, and do you get the same error?

SELECT TOP 100000 * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > [whatever] ORDER BY [ID] ASC

3

7e882cc7-afdd-475b-b52c-8707bc6facac.png
7e882cc7-afdd-475b-b52c-8707bc6facac.png1196×839 39.9 KB

The source is cdata sql and the destination is bigquery.
This is replicate query script= REPLICATE [history] SELECT * FROM [dbo].[App_History] AS  WITH(NOLOCK);
 
af4877df-4a32-4a0a-afb2-1d94033d83c2.png
af4877df-4a32-4a0a-afb2-1d94033d83c2.png1256×549 24.6 KB

4

2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: replicateoptions = ColumnNameIncludeVowels,AlterSchema,BytesPerCharacter=1,AutoTruncateStrings,ContinueOnError,ReplaceInvalidDatesWithNull,DropExisting,ReplicateInterval=180,ReplicateIntervalUnit=days,BatchSize=500000,CommandTimeout=300000000,StatusIdentifier=_id,DeletionBehavior=SoftDelete,ReplicationType=ChangeDataCapture,ClockSkewInterval=1,IncludeCDCMeta=True,UseTempTable=True,ReplicateVersion=1
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: logfile = D:\LogFiles\jobAimiClone_R_2024-09-12-00-51-26.log
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: sourcename = srcAimi
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: sourceprovider = System.Data.CData.SQL
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: syncprovider = System.Data.SQLite
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: maxlogfilesize = 100M
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: destinationname = dstAimi
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: destinationprovider = System.Data.CData.GoogleBigQuery
2024-09-12T00:51:27.919-06:00    2    [23|Q-Id]    [INFO|Connec] Connection Property: verbosity = 2
2024-09-12T00:51:27.919-06:00    1    [23|Q-Id]    [INFO|Connec] Opened Replicate connection. Version: 23.0.8972.0. Git: eda8025565f0fa1548b3a0634aabe381a0bdd7b3. Edition: [ADO.NET Provider .NET 4.0].
2024-09-12T00:51:27.919-06:00    1    [23|Q-Id]    [INFO|Connec] Connection String: autocache=False;batchsize=0;browsableschemas=‘’;cacheconnection=‘’;cachelocation=“%APPDATA%\CData\Replicate Data Provider”;cachemetadata=False;cacheprovider=‘’;cachetolerance=600;catalog=CData;connectionlifetime=0;destinationcatalog=‘’;destinationconnection=‘’;destinationname=dstAimi;destinationprovider=System.Data.CData.GoogleBigQuery;destinationschema=‘’;incrementalcheckcolumns=‘’;location=“C:\Users\loisine.ann.mendoza.BSSTEST\AppData\Roaming\CData\Replicate Data Provider\Schema”;logfile=D:\LogFiles\jobAimiClone_R_2024-09-12-00-51-26.log;logmodules=‘’;maxlogfilecount=-1;maxlogfilesize=100M;maxrows=-1;offline=False;other=‘’;poolidletimeout=60;poolmaxsize=100;poolminsize=1;poolwaittime=60;previoussynccatalog=‘’;previoussyncconnection=‘’;previoussyncprovider=‘’;previoussyncschema=‘’;querypassthrough=True;readonly=False;replicateoptions=“ColumnNameIncludeVowels,AlterSchema,BytesPerCharacter=1,AutoTruncateStrings,ContinueOnError,ReplaceInvalidDatesWithNull,DropExisting,ReplicateInterval=180,ReplicateIntervalUnit=days,BatchSize=500000,CommandTimeout=300000000,StatusIdentifier=_id,DeletionBehavior=SoftDelete,ReplicationType=ChangeDataCapture,ClockSkewInterval=1,IncludeCDCMeta=True,UseTempTable=True,ReplicateVersion=1”;schema=Replicate;sourceconnection=‘’;sourcename=srcAimi;sourceprovider=System.Data.CData.SQL;synccatalog=‘’;syncconnection=‘’;syncprovider=System.Data.SQLite;syncschema=‘’;tables=‘’;useconnectionpooling=False;verbosity=2;views=‘’
2024-09-12T00:51:28.481-06:00    2    [23|Q-Id]    [EXEC|Parsed] Executing query: [REPLICATE [history_test] WITH _id = ‘JckazNKQyY8YpKv9Y5RdeA==’ SELECT * FROM [dbo].[App_History] AS [WITH]]
2024-09-12T00:51:28.481-06:00    1    [23|Q-Id]    [RealDB:System.Data.CData.SQL]The connection is reused.
2024-09-12T00:51:28.481-06:00    1    [23|Q-Id]    [RealDB:GoogleBigQuery]The connection is reused.
2024-09-12T00:51:28.481-06:00    1    [23|Q-Id]    [RealDB:SQLite]The connection is reused.
2024-09-12T00:51:28.481-06:00    1    [23|Q-Id]    [RealDB:System.Data.CData.SQL]The connection is reused.
2024-09-12T00:51:28.481-06:00    1    [23|Q-Id]    [EXEC|Messag] Switching to Replicate1
2024-09-12T00:51:29.299-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]Verified ChangeTracking is enabled for dbo.App_History
2024-09-12T00:51:29.299-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]Invoking getColumns(, dbo, App_History)
2024-09-12T00:51:29.565-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:ID, TypeName:uniqueidentifier, TypeCode:9, Size:16, Precision:0, Scale:0, IsKey:True
2024-09-12T00:51:29.565-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:userName, TypeName:varchar, TypeCode:16, Size:50, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:29.565-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:AccessedOn, TypeName:datetime, TypeCode:6, Size:8, Precision:0, Scale:3, IsKey:False
2024-09-12T00:51:29.565-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:Token, TypeName:varchar, TypeCode:16, Size:1800, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:29.830-06:00    2    [23|Q-Id]    [EXEC|Parsed] Executing query: [CACHE [history_test] SELECT * FROM [dbo].[App_History] AS [WITH] ORDER BY [ID] ASC LIMIT 100000]
2024-09-12T00:51:29.830-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]Invoking getColumns(, dbo, App_History)
2024-09-12T00:51:30.107-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:ID, TypeName:uniqueidentifier, TypeCode:9, Size:16, Precision:0, Scale:0, IsKey:True
2024-09-12T00:51:30.107-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:userName, TypeName:varchar, TypeCode:16, Size:50, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:30.107-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:AccessedOn, TypeName:datetime, TypeCode:6, Size:8, Precision:0, Scale:3, IsKey:False
2024-09-12T00:51:30.107-06:00    2    [23|Q-Id]    [RealDB:System.Data.CData.SQL]ColumnInfo Catalog:AIMINxt, Schema:dbo, Name:Token, TypeName:varchar, TypeCode:16, Size:1800, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:30.154-06:00    2    [23|Q-Id]    [SQL |Cache ] Retrieving the live ResultSet is finished. Elapsed time: 324ms
2024-09-12T00:51:30.154-06:00    2    [23|Q-Id]    [SQL |Cache ] Transforming ResultSet is finished. Elapsed time: 0ms
2024-09-12T00:51:30.154-06:00    2    [23|Q-Id]    [SQL |Cache ] The column data type alteration is disabled.
2024-09-12T00:51:30.154-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]Invoking getColumns(, , history_test)
2024-09-12T00:51:30.311-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:ID, TypeName:varchar, TypeCode:16, Size:16, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:30.311-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:userName, TypeName:varchar, TypeCode:16, Size:50, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:30.311-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:AccessedOn, TypeName:datetime, TypeCode:6, Size:8, Precision:0, Scale:6, IsKey:False
2024-09-12T00:51:30.311-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:Token, TypeName:varchar, TypeCode:16, Size:1800, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:30.311-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:_cdatasync_deleted, TypeName:bool, TypeCode:3, Size:1, Precision:1, Scale:0, IsKey:False
2024-09-12T00:51:30.311-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]Invoking getColumns(, , history_test__r_429933948)
2024-09-12T00:51:31.877-06:00    2    [23|Q-Id]    [SQL |Bulk  ] 100000 rows are being committed.
2024-09-12T00:51:39.285-06:00    2    [23|Q-Id]    [SQL |Bulk  ] 100000 rows are committed. Elapsed time: 8529ms, Next time: 142ms, Process time: 979ms, Commit time: 7408ms
2024-09-12T00:51:39.285-06:00    2    [23|Q-Id]    [SQL |Bulk  ] The transfer is finished. Elapsed time: 8529ms, Next time: 142ms, Process time: 979ms, Commit time: 7408ms
2024-09-12T00:51:39.285-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]Invoking getColumns(, , history_test)
2024-09-12T00:51:39.379-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:ID, TypeName:varchar, TypeCode:16, Size:16, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:39.379-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:userName, TypeName:varchar, TypeCode:16, Size:50, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:39.379-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:AccessedOn, TypeName:datetime, TypeCode:6, Size:8, Precision:0, Scale:6, IsKey:False
2024-09-12T00:51:39.379-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:Token, TypeName:varchar, TypeCode:16, Size:1800, Precision:0, Scale:0, IsKey:False
2024-09-12T00:51:39.379-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]ColumnInfo Catalog:tfo-us-dev-synops-dl-raw, Schema:aimi, Name:_cdatasync_deleted, TypeName:bool, TypeCode:3, Size:1, Precision:1, Scale:0, IsKey:False
2024-09-12T00:51:41.145-06:00    2    [23|Q-Id]    [RealDB:GoogleBigQuery]Invoking getColumns(, , history_test__r_429933948)
2024-09-12T00:51:41.241-06:00    2    [23|Q-Id]    [SQL |Cache ] Caching is finished. Elapsed time: 11411ms
2024-09-12T00:51:41.241-06:00    1    [23|Q-Id]    [SQL |Cache ] Cache: 100000 rows inserted or updated.
2024-09-12T00:51:41.241-06:00    2    [23|Q-Id]    [EXEC|Messag] Executed query: [CACHE [history_test] SELECT * FROM [dbo].[App_History] AS [WITH] ORDER BY [ID] ASC LIMIT 100000] Success: (11411 ms)
2024-09-12T00:51:41.241-06:00    2    [23|Q-Id]    [RealDB:SQLite]Invoking getColumns(, , CDATA_SYNC_REPLICATE_STATUS)
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:_id, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:ReplicateId, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:Source, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:Destination, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:Query, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:LastModTime, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:LastRunTime, TypeName:datetime, OriginalTypeName:, TypeCode:6, Size:8, Precision:8, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:Status, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:Info, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:AffectedRows, TypeName:integer, OriginalTypeName:, TypeCode:11, Size:10, Precision:9, Scale:0, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:CDCStatus, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:LastPrimaryKey, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.257-06:00    2    [23|Q-Id]    [RealDB:SQLite]ColumnInfo Catalog:main, Schema:sqlite_default_schema, Name:Other, TypeName:string, OriginalTypeName:, TypeCode:16, Size:2147483647, Precision:2147483647, Scale:-1, IsKey:False
2024-09-12T00:51:41.366-06:00    2    [23|Q-Id]    [EXEC|Parsed] Executing query: [CACHE [history_test] SELECT * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 ORDER BY [ID] ASC LIMIT 100000]
2024-09-12T00:51:41.366-06:00    2    [23|Q-Id]    [EXEC|Parsed] Parameter: pk_b0 = fac86d96-7ca1-4a78-b582-0d94b83bcdc6
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [RealDB:System.Data.CData.SQL]Failed to execute SELECT TOP 100000 * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 ORDER BY [ID] ASC, [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170.
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [SQL |Messag] Query Failed: [CACHE [history_test] SELECT * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 ORDER BY [ID] ASC LIMIT 100000]. Error: [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
2024-09-12T00:51:41.413-06:00    2    [23|Q-Id]    [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
System.Data.CData.SQL.SQLException
   at ruo230q.vbk.V(Int32 , RkF`1 )
   at ruo230q.vUo.Ay()
   at ruo230q.vyy.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at ruo230q.Iv.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at ruo230q.RDq.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at ruo230q.RbZ.Ni()
   at ruo230q.RbS.Oe()
   at ruo230q.Ne.Wc(QueryParameterCollection )
   at System.Data.CData.SQL.SQLCommand.ExecuteDataReader(CommandBehavior behavior)
   at System.Data.CData.SQL.SQLCommand.ExecuteDataReader(CommandBehavior behavior)
   at System.Data.CData.SQL.SQLCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at aso230K.eg.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at aso230K.Wq.sb(String , QueryParameterCollection , Int32 , Boolean )
   at aso230K.Wq.k()
   at aso230K.Wq.fU()
   at aso230K.Wn.bU(QueryParameterCollection )
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [SQL |Cache ] [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [SQL |Cache ] Cache failed: [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
2024-09-12T00:51:41.413-06:00    0    [23|Q-Id]    [SQL |Cache ] Replication Failed. Error:[500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [RealDB:System.Data.CData.SQL]The connection is disconnected.
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [RealDB:GoogleBigQuery]The connection is disconnected.
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [RealDB:SQLite]The connection is disconnected.
2024-09-12T00:51:41.413-06:00    1    [23|Q-Id]    [SQL |Messag] Query Failed: [REPLICATE [history_test] WITH _id = ‘JckazNKQyY8YpKv9Y5RdeA==’ SELECT * FROM [dbo].[App_History] AS [WITH]]. Error: [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
2024-09-12T00:51:41.413-06:00    2    [23|Q-Id]    [500] Could not execute the specified command: The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170
aso230K.ah
   at ruo230q.vbk.V(Int32 , RkF`1 )
   at ruo230q.vUo.Ay()
   at ruo230q.vyy.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at ruo230q.Iv.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at ruo230q.RDq.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at ruo230q.RbZ.Ni()
   at ruo230q.RbS.Oe()
   at ruo230q.Ne.Wc(QueryParameterCollection )
   at System.Data.CData.SQL.SQLCommand.ExecuteDataReader(CommandBehavior behavior)
   at System.Data.CData.SQL.SQLCommand.ExecuteDataReader(CommandBehavior behavior)
   at System.Data.CData.SQL.SQLCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at aso230K.eg.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at aso230K.Wq.sb(String , QueryParameterCollection , Int32 , Boolean )
   at aso230K.Wq.k()
   at aso230K.Wq.fU()
   at aso230K.Wn.bU(QueryParameterCollection )
   at aso230K.Wn.bU(QueryParameterCollection )
   at aso230K.yhg.HY.k(Wq , HR )
   at aso230K.yhg.HY.bQ(String , QueryParameterCollection , eX )
   at aso230K.yhg.HA.e(QueryParameterCollection )
   at aso230K.yho.L(IDataStatement , QueryParameterCollection , String )
   at aso230K.yho.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at aso230K.non.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
   at aso230K.nqn.QW()
   at aso230K.nqI.fU()
   at aso230K.Wn.bU(QueryParameterCollection )
2024-09-12T00:51:42.666-06:00    1    [23|Q-Id]    [INFO|Connec] Closed Replicate connection

5

Hi  @loisine.ann.mendoza

Based on the information you have provided, the main cause of the issue you are encountering is the custom REPLICATE query that you are running:

REPLICATE [history] SELECT * FROM [dbo].[App_History] AS WITH(NOLOCK);

First of all the above query is not quite correct. The correct query should be: REPLICATE [history] SELECT * FROM [dbo].[App_History] WITH(NOLOCK)

In any case, it appears that passthrough queries similar to these are not currently supported in Sync, so we are unfortunately unable to formally use the queries with NOLOCK present. Since this query is not supported by Sync, it is being transformed to a different query for some reason which I can notice from the logs you have included:

REPLICATE [history_test] WITH _id = 'JckazNKQyY8YpKv9Y5RdeA==' SELECT * FROM [dbo].[App_History] AS [WITH]]

It looks like an ALIAS is added incorrectly at the end of the custom REPLICATE query and this is causing the “The SQL Error Number is 102, State is 1, Severity is 15, Error is ‘Incorrect syntax near ‘ca1’.’, Server name is AWDAPPINDB3, Line number is 1.The Token Type is 170” error message. I can reproduce the same error on my end when running a similar query which would indicate that the  AS [WITH] being appended at the end of the custom query is causing this.

With that being said, are you able to replicate the table correctly when omitting the WITH(NOLOCK) from your custom query?

Also, is there any particular reason why you are using WITH(NOLOCK) hints here? Would you be able to provide more context on that? We would be interested to hear more what is your end goal?

On a side note, I wanted to point out that NOLOCK hints shouldn’t be required generally because Sync isn’t going to put a lock on your entire database during the replication process. We essentially are submitting an elegant SELECT query against your source SQL database. Select queries should generally only place a shared lock on each row as the database processes those records for the corresponding query at most. Sync shouldn’t be enforcing any kind of lock on your database anyway. That shouldn’t be a concern with Sync.

In case you have any other questions or are unclear about something feel free to reach out to our support team at support@cdata.com and one of our support members will further assist you.

6

@Ethem_Q     Assuming the OP is using SQL Server, since we’re talking (NOLOCK) hints.

With SQL Server, if you’re retrieving many thousands of rows even on a SELECT statement, it will place a read table lock on the entire table.  Lock escalation.  And that read lock will prevent writes.  It won’t block other readers, but it does block updates.  It’s just not as sophisticated as Oracle in that regard, in my experience.

It will do this even if the user login for the CData connection doesn’t have write privileges to the database.

7

Hi @Ethem_Q  i tried REPLICATE [history] SELECT * FROM [dbo].[App_History] WITH(NOLOCK) and without no lock but still got the error but data still ingested. The settings is Drop table is enabled, history is enabled and not enable still same error.  I attached the log files for you reference.

023dfb7d-daf5-4985-9441-f4b810009b98.png
023dfb7d-daf5-4985-9441-f4b810009b98.png930×385 50.7 KB

0ddb0785-f303-4964-89e6-50e91987820c.png
0ddb0785-f303-4964-89e6-50e91987820c.png979×620 167 KB

Destination_logs_App_history_HistoryNotEnabled.txt (19.6 KB)
Replicate_logs_App_history_HistoryNotEnabled.txt (17.9 KB)
Source_logs_App_history_HistoryNotEnabled.txt (34.1 KB)

8

You probably shouldn’t include log files here, as they could contain sensitive information.

Your likely best bet, at this point, is to enter a formal support request through the web site, versus using the community support forum.

9

@DougN_Nouria  I understand your reasoning however what we wanted to clarify here is the end goal in order to suggest any viable solutions that might help  @loisine.ann.mendoza  for what they are looking to accomplish.

As a matter of fact, the team in care of Sync advises against using NOLOCK hints because utilizing the `WITH (NOLOCK)` hint is equivalent to setting the transaction isolation level to `READ UNCOMMITTED`. This approach risks reading uncommitted data, which may later be rolled back, resulting in the replication of data that does not persist in the source database. While this method can prevent read operations from being blocked by other transactions, it introduces the risk of retrieving data that is not finalized, and this is one of the reasons why we do not employ this function.

In any case, there have been now a few requests about this functionality and a ticket has been already submitted for looking into adding this feature. This request will first be evaluated by the product team and it might take some time before they come to a decision if they want to pursue this. We will be sure to notify you once there is an update.