Getting some sync warnings about a column that can't be null, even when the source table has no null values in that column.

Not sure the best way to figure out what is causing it to think that the values are null.

Sync completed with warnings in 708.02 seconds. 
Upload:   4646 changes applied/155 failed   
Download: 680 changes applied/0 failedData Sync will stop synchronizing changes for this sync group member in -60 days if the failures are not resolved.    
Upload - errors for first 5 rows that failed to apply:Error #1: SqlException Error Code: -2146232060 - 
SqlError Number:515, Message: Cannot insert the value NULL into column 'ModelID', table 'dbo.ModelYears'; column does not allow nulls. INSERT fails. 
SqlError Number:3621, Message: The statement has been terminated. Error #2: SqlException Error Code: -2146232060 - 
SqlError Number:515, Message: Cannot insert the value NULL into column 'ModelID', table 'dbo.ModelYears'; column does not allow nulls. INSERT fails. 
SqlError Number:3621, Message: The statement has been terminated. Error #3: SqlException Error Code: -2146232060 - 
SqlError Number:515, Message: Cannot insert the value NULL into column 'ModelID', table 'dbo.ModelYears'; column does not allow nulls. INSERT fails. 
SqlError Number:3621, Message: The statement has been terminated. Error #4: SqlException Error Code: -2146232060 - 
SqlError Number:515, Message: Cannot insert the value NULL into column 'ModelID', table 'dbo.ModelYears'; column does not allow nulls. INSERT fails. 
SqlError Number:3621, Message: The statement has been terminated. Error #5: SqlException Error Code: -2146232060 - 
SqlError Number:515, Message: Cannot insert the value NULL into column 'ModelID', table 'dbo.ModelYears'; column does not allow nulls. INSERT fails. 
SqlError Number:3621, Message: The statement has been terminated.     
For more information, provide tracing ID 92002a08-b374-4579-b58c-a5164e820767 to customer support.

Whats the best way to troubleshoot this?

Hi AndyJLW,

It seems that you encounter the similar issue which is described in this thread.

This issue could be due to Primary key change.  Currently, SQL Data Sync doesn't support sync primary key changes, please use the following script to detect:

-- Replace [SourceTableName] by the table name reported in error log, replace the [PK] by its primary key
SELECT * 
FROM DataSync.[SourceTableName]_dss_tracking t WHERE sync_row_is_tombstone=0 and not exists (
SELECT * 
FROM dbo.[SourceTableName] s
WHERE t.[PK] = s.[PK]
)

Thanks,
Lydia Zhang

Tried it, and it returned 0 rows.

I had seen this suggested before, and it also returned 0 rows before I truncated both the ModelYears and ModelYears_dss_tracking tables.

(I truncated both tables a few days ago and let sync refill it, but it's still throwing the warning)

Hi,

This kind of issue generally caused by primary key update, may I know whether you've update the primary key in table 'dbo.ModelYears'?

To resolve this issue, truncate the tracking table won't work. Please do the following steps:

1. unselect the table from the sync group and re-provision
2. truncate all the data in ModelYears table from other databases and make sure only one database contains data in this table, in order to avoid conflict resolution
3. reselect the table and re-provision again. In that case sync service will sync the data in that table again.

Let me know if any more question.

Regards,
Bowen

Hi,

This kind of issue generally caused by primary key update, may I know whether you've update the primary key in table 'dbo.ModelYears'?

To resolve this issue, truncate the tracking table won't work. Please do the following steps:

1. unselect the table from the sync group and re-provision
2. truncate all the data in ModelYears table from other databases and make sure only one database contains data in this table, in order to avoid conflict resolution
3. reselect the table and re-provision again. In that case sync service will sync the data in that table again.

Let me know if any more question.

Regards,
Bowen

• Proposed as answer by Bowen Wan Friday, February 13, 2015 3:34 AM

Hi,

This kind of issue generally caused by primary key update, may I know whether you've update the primary key in table 'dbo.ModelYears'?

To resolve this issue, truncate the tracking table won't work. Please do the following steps:

1. unselect the table from the sync group and re-provision
2. truncate all the data in ModelYears table from other databases and make sure only one database contains data in this table, in order to avoid conflict resolution
3. reselect the table and re-provision again. In that case sync service will sync the data in that table again.

Let me know if any more question.

Regards,
Bowen

• Proposed as answer by Bowen Wan Friday, February 13, 2015 3:34 AM

Hi,

This kind of issue generally caused by primary key update, may I know whether you've update the primary key in table 'dbo.ModelYears'?

To resolve this issue, truncate the tracking table won't work. Please do the following steps:

1. unselect the table from the sync group and re-provision
2. truncate all the data in ModelYears table from other databases and make sure only one database contains data in this table, in order to avoid conflict resolution
3. reselect the table and re-provision again. In that case sync service will sync the data in that table again.

Let me know if any more question.

Regards,
Bowen

• Proposed as answer by Bowen Wan Friday, February 13, 2015 3:34 AM

To the best of my knowledge we haven't changed the primary key, and it's only syncing to one database.

Actually, I'm not sure how to unselect the table and re-provision... It's been so long since I set this all up I don't remember how it all works.

You can go to "SYNC RULE" table, unselect the table, and click save.

Regards,
Bowen