1

I have a Microsoft SQL Data Sync Agent job set up to sync our local server to Azure. It was working great, then we made a few updates locally and it didn't remove the deleted rows from Azure, and now the sync fails with a warning:

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.

The ModelID column does not allow nulls on either table, so I'm not sure what is causing the error.

I truncated ModelYears_dss_tracking and ModelYears on Azure as one forum suggested, and it did repopulate the tables, but it's still throwing the warnings.

AndyD273
  • 7,177
  • 12
  • 54
  • 92

2 Answers2

4

Could you please confirm that whether you have changed PK of this table?

A quick way to dismiss the error is to 1) delete the sync group 2) delete all tables and SP under the schema 'DataSync' on both hub and member 3) delete triggers contain the word 'dss' 4) create sync group again

Regards, Gary

Gary Zhu
  • 141
  • 3
  • You know, I think that's how I ended up fixing it, but it was so long ago, and I forgot about asking this question... Thanks for the answer, and bringing this one back to my memory – AndyD273 May 20 '15 at 14:18
  • 1
    For anyone interested here is the script [link](https://github.com/microsoft/sql-server-samples/blob/master/samples/features/sql-data-sync/clean_up_data_sync_objects.sql) to achieve point 2 and 3 (delete all tables, sp and triggers created by DataSync) – Denise Audirac Kushida Jun 11 '20 at 03:16
  • The script above is useful, but be careful, it is created with a LIKE statement, so if you place "USER" table and you have "UserDetails", "UserMetrics", "UserAdress" and "User"... it will drop all of them. – Yogurtu May 09 '22 at 15:15
1

I have a better solution (less drastical) than the one approved.

With this approach, you will not need to drop your entire sync Group.

The reasson the approved solution works, it's because you are refreshing the whole replica, but what if you have tons of information already present? It might take forever to redo all.

The solution to fix one table only is:

  1. Ensure your replica is off while you perform these steps

  2. Use the script provided by @denise here LINK Remember, be careful, the script works with LIKE statement, so ensure you get a sigle result only.

  3. DROP all objects provided in that result. If you run your replica now, it will fail saying that the procedure is not found, so don't do it!,

  4. Open the "TABLES", refresh schema, and UNSELECT the table you have issues, press SAVE.

  5. Open the "TABLES" again, refresh schema just incase, and SELECT the table you have issues, press SAVE.

  6. Now you can safely start replica, it will recreate missing objects for the table that was failing.

Yogurtu
  • 2,656
  • 3
  • 23
  • 23