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

  • gurvinderg

    SSC Veteran

    Points: 242

    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,

  • Lowell

    SSC Guru

    Points: 323442

    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!

  • gurvinderg

    SSC Veteran

    Points: 242

    There are no constraints on the columns that are getting updated in the Update statement.

    Table: categories

    Primary Key Clustered on CategoryID

    UNIQUE (non-clustered) CategoryTypeID, ParentCategoryID, CategoryName

  • martin 48305

    SSC Journeyman

    Points: 77

    I am having the same problem with an even simpler UPDATE statement: UPDATE ... SET ... WHERE. Even though the fields that are updated already exists with the same values.

    This is also on Azure Sql Server v12.

    The rows that are reported as part of the error message indicates that the problem has something to do with an index that are structured like this:

    CREATE UNIQUE CLUSTERED INDEX <index name> ON <view name>

    (

    <field name 1> ASC,

    <field name 2> ASC,

    <field name 3> ASC,

    ...

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    Googling reveals that SQL Server used to have an error like this, but that was fixed 4 years ago!

    http://modified187.rssing.com/browser.php?indx=4756045&item=2319

    Did the fix somehow not find its way into Azure SQL Server?

  • martin 48305

    SSC Journeyman

    Points: 77

    I am having the same problem with an even simpler UPDATE statement: UPDATE ... SET ... WHERE. Even though the fields that are updated already exists with the same value.

    This is also on Azure Sql Server v12.

    The rows that are reported as part of the error message indicates that the problem has something to do with an index that are structured like this:

    CREATE UNIQUE CLUSTERED INDEX <index name> ON <view name>

    (

    <field name 1> ASC,

    <field name 2> ASC,

    <field name 3> ASC,

    ...

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    Googling reveals that SQL Server used to have an error like this, but that was fixed 4 years ago!

    http://modified187.rssing.com/browser.php?indx=4756045&item=2319

    Did the fix somehow not find its way into Azure SQL Server?

  • Lowell

    SSC Guru

    Points: 323442

    martin 48305 (12/4/2015)


    I am having the same problem with an even simpler UPDATE statement: UPDATE ... SET ... WHERE. Even though the fields that are updated already exists with the same value.

    This is also on Azure Sql Server v12.

    The rows that are reported as part of the error message indicates that the problem has something to do with an index that are structured like this:

    CREATE UNIQUE CLUSTERED INDEX <index name> ON <view name>

    (

    <field name 1> ASC,

    <field name 2> ASC,

    <field name 3> ASC,

    ...

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    Googling reveals that SQL Server used to have an error like this, but that was fixed 4 years ago!

    http://modified187.rssing.com/browser.php?indx=4756045&item=2319

    Did the fix somehow not find its way into Azure SQL Server?

    details would be required. the exact command you are running, at a minimum. probably a setup of tables(CREATE TABLE...) and sample data (INSERT INTO...) so a testeted, complete example can be created.

    If you are updating from another table or join, the duplicates could be in the join/temp.

    if a row with fields1/2/3 exists in the target table , and you have a loose WHERE statement which would update a DIFFERENT row to have that same value, you'd get the same error mentioned in this thread.

    that's just a logical error, and can be fixed with a tighter WHERE clause. it's not a SQL server error....it's SQL server enforcing constriants when you don't really expect it.

    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!

  • sangeetha.microsoft

    Newbie

    Points: 1

    Hi ,

    Anyone got the solution for this error , I am trying to replicate data from sql 2000 to azure and ending up with this error , Any inputs appreciated.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply