SQL Server

Troubleshooting SQL Server 2016 Bulk Copy Problems

Troubleshooting SQL Server 2016 Bulk Copy Problems

If you have problems with SQL Server 2016 bulk copy, review this troubleshooting
checklist to find potential solutions.

1. Install the latest SQL Server 2016 service pack.

Because many SQL Server 2016 bulk copy bugs were fixed in SQL Server service packs,
you should install the latest SQL Server service pack. At the time this article
was written the latest SQL Server 2016 service pack was service pack 1. You can
download the SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/kb/3182545

2. The error 674 may occur during the bulk insert operations.

This error indicates a problem related to releasing pre-allocated disk blocks used
during bulk-insert operations. To resolve this problem, you should restart the server.

3. The error 4803 occurs during the bulk copy operation.

This is the error message text: “The bulk copy (bcp) client has sent a row length of %d.
This is not a valid size. The maximum row size is %d.” To work around this problem, you
should Use a supported client application programming interface (API).

4. The error 4868 occurs when you bulk load data into table.

This is the error message text: “The bulk load failed. The codepage “%d” is not installed.”
To resolve this problem, you should Install the codepage and run the command again.

5. The error 4876 may occur during the Bulk Insert operation.

This is the error message text: “The Bulk Insert operation of SQL Server Destination
has timed out.” To resolve this problem, you can increase the value of Timeout
property on the SQL Server Destination in the dataflow.

6. The error 5301 occurs when you bulk load data into table.

This error indicates that bulk load fails because user does not have the ALTER TABLE
permission on the target table of a bulk load. This problem occurs when the target
table contains triggers or check constraints. To work around this problem, you should
grant the ALTER TABLE permission for the user.

7. When you bulk load data into a clustered columnstore index under memory pressure,
all the data is inserted into the deltastores, and the Log Optimization can’t be used.

If SQL Server 2016 restarts in that situation, it may take a long time for SQL Server
to recover the database after the restart. This bug was fixed in Cumulative update
package 1 for SQL Server 2016. You can download the Cumulative Update package 1 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

8. BULK INSERT import wrong data if UTF-8 encoded file does not have a byte-order
mark (BOM) in SQL Server 2016.

This problem occurs when the first two bytes in the file are equal to “0xEFBB”. In this
case, the first character will be wrongly recognized as a BOM. As a result, the imported
data is incorrect, and you don’t receive an error message about it. This bug was fixed
in Cumulative update package 1 for SQL Server 2016. You can download the Cumulative
Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

9. The error 8061 occurs when you run a BULK INSERT statement on a table that contains
both clustered columnstore indexes and nonclustered columnstore indexes.

This is the error message text: “Internal Query Processor Error: The query processor
could not obtain access to a required interface.” This bug was fixed in Cumulative
update package 1 for SQL Server 2016. You can download the Cumulative Update package 1
for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

10. When you use the BULK INSERT task to copy data from a source file that exists
in a FileTable, a “Non-yielding Scheduler” condition may occur.

In this case, the SQL Server generates a memory dump file. This bug was fixed in
Cumulative update package 1 for SQL Server 2016. You can download the Cumulative
Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

11. The error 4819 occurs when you run a query that contains an UPDATE statement
in SQL Server 2016.

This is the error message text: “Cannot bulk load. The bulk data stream was incorrectly
specified as sorted or the data violates a uniqueness constraint imposed by the target
table.” This bug was fixed in Cumulative update package 2 for SQL Server 2016. You can
download the Cumulative Update package 2 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3182270/cumulative-update-2-for-sql-server-2016

12. Truncation error occurs when you use bcp command to import data from large
text files on a Chinese version of Windows.

This problem occurs when the field separator is set to multiple-bytes characters.
This bug was fixed in Cumulative update package 2 for SQL Server 2016. You can
download the Cumulative Update package 2 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3182270/cumulative-update-2-for-sql-server-2016

13. SQL Server crashes and generates an access violation when you use the TRY…CATCH
construct for bulk copy.

This problem occurs when a bulk copy operation triggers a call to a CLR function.
This bug was fixed in Cumulative update package 4 for SQL Server 2016. You can
download the Cumulative Update package 4 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3205052/cumulative-update-4-for-sql-server-2016

14. An assertion occurs when you bulk insert data into a table from multiple
connections in SQL Server 2016.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 4 for
SQL Server 2016. You can download the Cumulative Update package 4 for SQL Server 2016
at here:
https://support.microsoft.com/en-us/help/3205052/cumulative-update-4-for-sql-server-2016

15. Intra-query deadlock occurs on communication buffer when you run a bulk load
against a clustered columnstore index in SQL Server 2016.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 6 for
SQL Server 2016. You can download the Cumulative Update package 6 for SQL Server 2016
at here:
https://support.microsoft.com/en-us/help/4019914/cumulative-update-6-for-sql-server-2016
To work around this problem, you can set MAXDOP to 1 to avoid the parallel INSERT or
you can remove the clustered columnstore index during the INSERT SELECT operation.

Facebooktwittergoogle_plusredditpinterestlinkedinmail