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:
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.
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.
