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

Question

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

Old Hand

Points: 352
More actions
November 30, 2015 at 6:27 am

#399403

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,

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 Old Hand Points: 352 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 323518 More actions · Answer 5

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 More actions · Answer 6

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.