Home Forums Cloud Computing SQL Azure - Development SQL Azure Latest v12 update error Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violate a uniqueness constraint imposed by the target table RE: SQL Azure Latest v12 update error Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violate a uniqueness constraint imposed by the target table

  • November 30, 2015 at 6:33 am

    #1842853

    gurvinderg (11/30/2015)

    My SQL Azure database has a stored procedure that has the merge statement. When my database on Azure was updated to latest v12 update , this procedure started throwing following error on Merge statement

    Error: Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table. Sort order incorrect for the following two rows: primary key of first row: (4, 2, 153, 44667), primary key of second row: (4, 2, 90, 1897).

    I tried splitting the merge statement into separate update and insert statement but it still throws the same error on simple update statement.

    following statement gives the same error

    UPDATE pc

    SET pc.Archived = 0,

    pc.SortIndex = cc.SortIndex

    FROM dbo.Categories pc

    JOIN #ChildCategories cc ON pc.ParentCategoryID = @CategoryID and pc.CategoryTypeID = @CategoryTypeID and pc.CategoryName = cc.CategoryName

    Any one any idea about what might be causing this problem and how to fix it.

    Thanks,

    error seems to be the primary key or unique constraint on the target table, right?

    the first two columns seem to have a unique constraint, and two rows with values 4,2 exist in the bulk load.

    does the constraint need to be modified to feature 3 columns? or maybe four?

    what columns correspond to the ones idenfied with " (4, 2, 153, 44667), primary key of second row: (4, 2, 90, 1897)."

    are they ParentCategoryID ,CategoryTypeID and CategoryName ? maybe the constraint should not be in place at all?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!