Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
sync exception "Bulk command BulkInsertCommand failed wth the following exception. Cannot insert the value NULL into column ***, table ****; column does not allow nulls. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    The exception is happened again and again in bulk mode.

    And I checked the schema of the table, the type of  this column is tinyint, it's not allowed NULL, the default value is 0,

    and the records of the table are the same on the machines that need to be sync.

    I don't why I have the exception.

    Does anybody have any idea on this issue?

    Thanks a lot

    Get reply from social.microsoft

    Thursday, April 12, 2012 2:47 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    sorry wasnt looking at it carefully, i thought youre referring to the table when your actually referring to the UDT...

    for tombstone records for deleted rows, only the PK is available from the tracking tables since the row in the base table has been deleted already... so the UDT has to allow nulls in there for the other columns...

    can you hook up to the ApplyChangesFailed event and see if there is a conflict? or hook up to the ChangesSelected event and see if the column is not null or null...

    • Marked as answer by Abby.Tang Friday, April 20, 2012 7:51 AM
    Saturday, April 14, 2012 2:25 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    do you have multiple scopes against the same table?

    can you compare the columns of the table that you included in the scope against the UDT for the table in SSMS->Databases->YourDatabase->Programmability->Types->User-Defined Table Types

    do they have the same number of columns?

    Thursday, April 12, 2012 2:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    June, thanks for your reply

    There are no multiple scopes against the same table.

    And they have the same number of columns for the tables that need to be synced

    Get reply from social.microsoft

    Friday, April 13, 2012 2:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello June,

    when we defined the table, we set "TAKENTOSAFE" NOT ALLOW NULL

    But why it allows NULL from the "Programmability->Types->User-Defined Table Types"

    Is this cause the exception?

    Regards

    Abby

    Get reply from social.microsoft

    Friday, April 13, 2012 2:55 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    if you changed the column from nullable to not nullable after you you provisioned, sync fx wont pick up that change.

    can you check your _selectchanges and _bulkinsert stored procs for that table if that column is in there?

    Friday, April 13, 2012 3:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    June, thanks for your reply

    We defined the column with not nullable before provision, we didn't change it.

    And I found that all the columns with not nullable in the tables schema, but they are nullable in the user table *_bulktype

    And the column is in the _selectchanges and _bulkinsert store procedure,

    Regards,

    Abby

    Get reply from social.microsoft

    Friday, April 13, 2012 4:54 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    sorry wasnt looking at it carefully, i thought youre referring to the table when your actually referring to the UDT...

    for tombstone records for deleted rows, only the PK is available from the tracking tables since the row in the base table has been deleted already... so the UDT has to allow nulls in there for the other columns...

    can you hook up to the ApplyChangesFailed event and see if there is a conflict? or hook up to the ChangesSelected event and see if the column is not null or null...

    • Marked as answer by Abby.Tang Friday, April 20, 2012 7:51 AM
    Saturday, April 14, 2012 2:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes, there are some conflict in this table, because of the initial data in both sides.

    because of the conflict, the result of the column is NULL from _selectchanges,

    But how can I resolve it?

    Regards,

    Abby

    Get reply from social.microsoft

    Thursday, April 19, 2012 2:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    check your conflict resolution then...if i were you, i'd run sql profiler and sync fx tracing and what scenario produces the null column...

    Friday, April 20, 2012 2:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I got what the issue is. I can handle it well,

    Appreciate for your help

    Regards,

    Abby

    Get reply from social.microsoft

    Friday, April 20, 2012 7:54 AM