Guide to Decoding SQL Server Bulk Insert Error FilesGuide to Decoding SQL Server Bulk Insert Error FilesGuide to Decoding SQL Server Bulk Insert Error Files

When you perform a bulk insert in SQL Server , you have the option of specifying an error file as a part of the bulk insert statement. If any errors are encountered during the import process, two error files will typically be created.

The names of these files vary depending on the ERRORFILE statement that you have created. Consider, for example, this command:

BULK INSERT MyTable
FROM 'C:\Data\MyData.txt'
WITH (
  ERRORFILE = 'C:\Data\Error.Log'
);

This command sets the ERRORFILE equal to C:\Data\Error.Log. The files that would be created are Error.Log.txt and Error.Log.txt.Error.txt.

The Error.Log.txt file is a discard file, and it's sometimes referred to as a .BAD file. If any rows of data from your source file cannot be inserted into the SQL Server database table, then those rows will be added to this file. The format used by those data rows matches that of your original data. Unlike your original data file, however, the discard file does not contain a header row.

The discard document serves two main purposes. First, it shows you exactly which records were not imported. Second, and perhaps more importantly, this file gives you another chance at importing your data. After all, you probably won't want to try importing the entire data file again because if some records were successfully imported and then you try to perform the import process again, the records that were imported the first time will likely be duplicated within the database. The discard file contains only data that has not yet been imported, meaning that you can safely use it for secondary import attempts.

Related:An Introduction to SQL Commands, Part 1

The Error.Log.txt.Error.txt file gives you detailed information about the errors that occurred. However, the information stored within this file is often cryptic, so you will need to know how to interpret the file's contents.

As an example, here is one of the errors contained in my Error.Log.txt.Error.txt file:

Row 2 File Offset 167 ErrorFile Offset 0 - HRESULT 0x80004005

The first bit of information contained in this error is a row number (in this case, Row 2). This represents the data row in which the error occurred. Keep in mind that the header row is not counted, so this error actually occurred in the third row (counting the header) of my datafile.

The second piece of information provided is a file offset number. This offset shows you where the error occurred within the datafile. To use the file offset for any meaningful troubleshooting, you will need to use a hex editor to open the datafile that you are trying to import. There is a freeware hex editor called HxD that you can use for this purpose.

Open your original datafile in HxD and then choose the Go To option from the Search menu. When prompted, enter the offset number in decimal format, as shown in Figure 1.

Related:Introduction to SQL Commands, Part 2: Adding, Updating Data

Decoding SQL Server Bulk Insert Errors

Figure 1:Enter the offset number in decimal format.

Click OK, and the hex editor will take you to the beginning of the record that caused the problem, as shown in Figure 2.

Decoding SQL Server Bulk Insert Error Files

Figure 2: The offset corresponds to a specific location within your file.

The next part of the error message is the word "ErrorFile," followed by another offset. This offset points to a position within the discard file, not within the original datafile. In other words, you can open the discard file in a hex editor, go to the specified offset, and the hex editor will take you to the row of data that caused the error.

So far, everything I have shown you within the error message is simply designed to point you toward the problem record's location. This information is conveyed in multiple ways, thereby giving you the ability to cross-check the source of the error.

The error itself is the number that appears after the word "HRESULT." In this case, the error is 0x80004005. Unfortunately, there is no magic formula for interpreting the error. The only way to find out what the error code means is to search for the error online. Sometimes, though, even knowing what the code means will leave you guessing.

Related:Introduction to SQL Commands, Part 3: Retrieving and Sorting Data

In my case, for example, this error message often corresponds to a SQL Server Agent job . However, since I wasn't running a SQL Server Agent job, I was able to dismiss that explanation. Other explanations pointed toward connectivity problems, which I was also able to dismiss. Eventually, I found an explanation that made more sense within the context of what I was trying to do. It indicated that this is a generic error, meaning that an invalid argument was passed to a function. To put it another way, the datatype defined by the column did not match the data that I was trying to insert.

Being that this one error message can have so many different meanings, my advice would be to perform a web search on the error message and to also include any information that will give the search engine context as to what you were doing when the error occurred.

• ITPro Today's 2025 IT Priorities Report

  Aug 8, 2025
  |

  2 Min Read

• Edge Computing Trends: Adoption, Challenges, and Future Outlook

  Jul 15, 2025
  |

  2 Min Read

• ITPro Today’s 2024 State of DevOps Report

  Dec 16, 2024
  |

  2 Min Read

• BCDR Basics: A Quick Reference Guide for Business Continuity & Disaster Recovery

  Oct 10, 2024
  |

  1 Min Read