Click here to Skip to main content
15,867,865 members
Articles / Database Development / SQL Server
Tip/Trick

Issues after Upgrade to SQL Server 2017 – Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
11 Jul 2018CPOL1 min read 10.1K   4   1
Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

Recently, we upgraded one of our Production Systems from SQL 2012 Enterprise to SQL 2017 Enterprise with CU8. The Upgrade process went smoothly without any issues. During the testing process, one such SQL Statement which was fetching the Data from Oracle Linked Server and inserting the same into a table hosted on SQL failed with the below error message:

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: (1-4XXF-5), primary key of second row: (1-4XXF1W). 
[SQLSTATE 42000] (Error 4819). The step failed.

The T-SQL was of the below format:

SQL
Insert into table_name
Select req_id,col2,col3,…

From Oracle Linked Server..Schema Name.Table Name

This particular Select Statement was running fine till SQL 2012 but all of a sudden, it failed after the Upgrade.

One important point over here is that the Column name Req_id has a Primary Key Constraint defined on it to ensure uniqueness.

As per the above error message; the System had an issue with the strings named 1-4XXF-5 and 1-4XXF1W. Not only with these two strings, but all the strings of the format 1-4XXF; although these 2 strings are totally different but still the system had an issue with the Sort especially due to the Clustered Index defined on the Column. Now at this stage; we took a decision to Change the Compatibility Level of the Database to Lower Version, i.e., SQL Server 2012 as it was earlier but still it didn’t work out.

We then decided to use LTRIM(RTRIM(Req_Id)); just to ensure that we trim everything from the Left and Right which should allow the System to process the Sort efficiently. Once we did this; i.e., Query changed to below format; it worked fine without any issues.

SQL
Insert into table_name
Select LTRIM(RTRIM(Req_Id)),col2,col3,…
From Oracle Linked Server..Schema Name.Table Name

It took around 9 minutes to load the data successfully into the table.

I hope you all find it useful.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect CapGemini India Private Limited
India India
Satnam Singh is a DBA Manager with Capgemini in India. Satnam has around 14 years of experience on Microsoft SQL Server Technology. His main area of expertise is T-SQL, High Availability solutions, Migrations, Upgradations, Performance Tuning etc. During his free time when he is not talking about SQL, Satnam loves spending time with his family. Satnam Lives along with his Parents in Mumbai,India.

Comments and Discussions

 
SuggestionUse the new TRIM instead of LTRIM(RTRIM(...)) Pin
mottykohn16-Jul-18 5:26
mottykohn16-Jul-18 5:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.