none
Azure Data Sync trying to insert null in non nullable column.

    Question

  • Hi,

    I recently set up Azure data sync to sync between an on-premises DB and an Azure DB. Specifically, we're trying to sync a customer table. Both customer tables have a matching schema. I also used data sync in visual studio to sync the data between the two tables before trying azure data sync. I keep receiving this error when trying to sync - 

    Sync failed with the exception "GetStatus failed with exception:Sync worker failed, checked by GetStatus method. Failure details:Sync was aborted because more than 1000 changes failed to apply. Examine your table schemas to look for conflicting constraints or incompatible data types that may prevent sync from succeeding.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 'IsTaxExempt', table 'govxcloudregistrationdbstaging.dbo.Customer'; column does not allow nulls. INSERT fails. SqlError Number:3621, Message: The statement has been terminated. 

    This is taking place on km0ao8ja7f in the West US. The date on the shown log was 10/17/2013 5:40:28 PM and the tracing Id is 1caca875-0128-4942-86e6-af5b095377c3

    I've found this thread on the issue - http://social.msdn.microsoft.com/Forums/windowsazure/en-us/9183cf48-9dd2-4d0b-9da2-9e4e301372a9/database-provisioning-failed-trying-to-insert-null-value-into-provisiontimestamp?forum=ssdsgetstarted

    But I've never manually touched any of the datasync tables nor have I recreated the table after the data sync existed.

    Any ideas? 

    Thanks,

    Nick Peterson

    Monday, October 21, 2013 4:03 PM

All replies

  • Also, I tried deleting the sync group and the sync agent and recreating them and I'm still getting the same errors.
    Monday, October 21, 2013 4:13 PM
  • Hi Nickelbob,

    Thanks for trying Data sync services,

    Could you please check if the column  'IsTaxExempt' has been changed as Not null in the table you want to sync to?  whether the columns of the two tables in two databases are mathching and allowing NULL?

    and has this sync successed before?

    Thanks,

    Yan

    Tuesday, October 22, 2013 8:12 AM
  • Hi Yan,

    The column is (bit, not null) in both tables and has not been changed recently to my knowledge.

    This sync has never worked, I've ran into this issue from the start.

    Thanks!

    Tuesday, October 22, 2013 3:36 PM
  • Hi Nick,

    Have you tried to update primary key in one of the "govxcloudregistrationdbstaging.dbo.Customer" table before you launch first sync? Since update primary key may cause this kind of inconsistent error.

    To check whether it is the root cause, you can re-create the sync group again, and DO NOT make any changes in each member database, then launch a sync to see whether you'll get the same error message.

    Thanks,

    Bowen

    Monday, October 28, 2013 2:27 AM
  • Still no luck. I haven't changed anything concerning any primary keys. Here's the latest tracing ID - 24a589a4-109c-41e9-b164-ba2e3b2632c6
    Thursday, October 31, 2013 7:42 PM
  • I even tried giving the not null column a default value and I'm still getting the same error. What is going on here?
    Thursday, October 31, 2013 8:09 PM
  • have you tried deprovisioning completely? download the Data Sync Agent, install it and use the deprovisioning utility to get rid of all Data Sync created objects and then recreate the sync group
    Thursday, October 31, 2013 11:40 PM
    Answerer
  • Ok I tried that too and I still get the same error. Here's my tracing ID - dae590e6-3b82-4c62-a065-f9bd35d7eddc

    Do these tracing ID's tell you anything? Maybe if I could see what the error is on your side I could figure this out.

    Thanks!

    Monday, November 04, 2013 7:55 PM
  • Here's something interesting - 

    I used schema and data sync in visual studio to replicate the local db on another local computer, then re-set up the sync and it worked fine. So what sort of settings on a local DB would cause issues for a data sync?


    • Edited by Nickelbob Tuesday, November 05, 2013 6:35 PM
    Monday, November 04, 2013 10:14 PM
  • I think I found the issue. I believe the clustered index in my local table is corrupt. I'm attempting a restore, I'll report back.
    • Edited by Nickelbob Thursday, November 07, 2013 12:39 AM
    Thursday, November 07, 2013 12:39 AM
  • Yup, that worked. It was a corrupted clustered index the whole time. So frustrating.

    Thanks for your assistance guys, I'd probably have given up if it weren't for your suggestions!

    Thursday, November 07, 2013 5:25 PM