Bulk operations in SQL Server are designed for speed, not forgiveness. A single data discrepancy can abort an entire multi-million-row load with a terse error code and no indication of which row caused it. The good news is that these failures cluster into five predictable categories — permissions, truncation, type mismatch, delimiter collision, and line endings — and each has a known fix.
This guide focuses on the errors themselves. For the full set of BULK INSERT options and performance flags like TABLOCK and batch sizing, see the SQL Server import methods guide and the bulk loading performance guide.
Error quick reference
| Error | Root cause | Category |
|---|---|---|
| OS Error 5: Access is denied | SQL Server service account cannot read the file | Permissions |
| Error 8152: String data would be truncated | CSV value longer than the column definition | Length |
| Error 4864: Type mismatch or conversion error | "N/A" in a numeric column; wrong date format | Type |
| Data shifted into wrong columns | Comma inside a quoted field treated as a delimiter | Delimiter |
| Entire file imported as one row | Line ending mismatch (CRLF vs LF) | Encoding |
Error 1: OS Error 5 — "Access is Denied"
The most common beginner error. You can see the file on your desktop, but SQL Server reports it doesn't exist or can't read it. The confusion stems from a fundamental misunderstanding of identity: when BULK INSERT runs, it is not your Windows account accessing the file — it is the SQL Server service account. If that service account lacks read permission on the folder, the import fails regardless of your own permissions.
How to fix it
- Find the service account identity: open Windows Services, find SQL Server, go to Properties → Log On.
- Right-click the folder containing the CSV → Security → Edit → Add.
- Add the service account (e.g.
NT Service\MSSQLSERVER) and grant Read permission. - Alternatively — and often simpler — move the CSV to a local drive on the server itself, such as
C:\Imports\, which the service account can always reach.
This is the same service account identity problem that causes failures in the remote transfer scenario — the fix is identical: grant the service account access, not your personal Windows login.
Error 2: Error 8152 — "String or Binary Data Would Be Truncated"
This error means a value in the CSV is longer than the column can hold. You defined VARCHAR(50), but row 4,500 contains a 51-character string. SQL Server rejects the row — and with the default BULK INSERT behavior, rejects the entire batch.
Widen the column: ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR(MAX). This is a schema change, so verify it's appropriate for production before applying.
Load the entire CSV into a staging table where every column is NVARCHAR(MAX) — nothing truncates. Then run SELECT MAX(LEN(ColumnName)) on each column to find the actual maximum length in your data. Set your production column widths accordingly before moving the data over.
The staging-table approach here is the same pattern recommended for all production pipelines in the schema evolution guide: import raw data into unconstrained staging, validate and measure, then load into production with confidence.
Error 3: Error 4864 — Type Mismatch or Data Conversion Error
SQL Server tried to put a non-numeric value into a numeric column, or encountered a date it couldn't parse. Two sub-cases come up constantly.
Fix: Load the column as NVARCHAR(MAX) in a staging table. Then use TRY_CAST or TRY_CONVERT to attempt type conversion on each value in T-SQL — rows where the conversion returns NULL can be routed to an error log, and the valid rows move to production.
Fix: Run SET DATEFORMAT dmy; before the BULK INSERT command if your dates are in day-first format. Alternatively, import the date column as VARCHAR in staging, then use TRY_CONVERT(DATE, DateColumn, 103) for European format or TRY_CONVERT(DATE, DateColumn, 101) for US format to convert explicitly and catch failures row-by-row.
Error 4: Data Shifted into Wrong Columns
The import appears to succeed — the row count looks right — but when you query the table, data that belongs in Column B is appearing in Column C. This isn't an import error SQL Server catches; it's a silent corruption caused by a comma inside a quoted field.
A CSV like "Smith, John",42,Manager should produce three columns: name, age, title. But if BULK INSERT isn't told to respect quoted fields, it sees four tokens: Smith, John, 42, Manager — everything shifts right by one from the comma inside the name.
The fix depends on your SQL Server version
- SQL Server 2017 and later: Add
FORMAT = 'CSV'to theWITHclause. This activates RFC 4180-compliant CSV parsing, which respects double-quoted fields and treats commas inside quotes as literal characters rather than delimiters. - SQL Server 2016 and earlier:
FORMAT = 'CSV'is not supported. Your only options are to change the delimiter to a pipe (|) or tab (\t) in the source file, or pre-process the CSV to escape internal commas before loading.
Error 5: The One-Row Bug (Line Ending Mismatch)
SQL Server imports the first row correctly, then either fails or treats the rest of the file as one enormous string. Or the opposite: the entire file arrives as a single row with no line breaks detected. Both symptoms point to the same root cause — a mismatch between the line endings in the CSV and the ROWTERMINATOR setting.
Windows line endings are two characters: carriage return + line feed (\r\n). Linux and macOS line endings are one character: line feed only (\n). If your CSV was exported on Linux but your ROWTERMINATOR expects Windows line endings, SQL Server never finds a row boundary.
| File origin | Line ending | ROWTERMINATOR to use |
|---|---|---|
| Windows (Excel, Notepad) | \r\n (CRLF) | 0x0d0a |
| Linux / macOS / Unix | \n (LF) | 0x0a |
Hex values are unambiguous — prefer them over \r\n or \n string literals, which SQL Server interprets differently in different contexts.
Can't tell which line ending the file uses?
Open the file in a hex editor, or use PowerShell: Get-Content file.csv -Raw | Select-String '\r\n'. If it returns matches, the file uses CRLF. If not, it uses LF. Alternatively, open the file in VS Code — the line ending type is shown in the status bar at the bottom right.
The Universal Debugging Tool: Error Files
All of the errors above have one thing in common: by default, a single bad row aborts the entire batch. The ERRORFILE option changes that behavior — bad rows are skipped and written to a log file, and the import continues with the remaining valid rows. Combined with MAXERRORS, you can allow a controlled number of failures before deciding to abort.
Error file options
| Option | What it does | Recommended value |
|---|---|---|
| ERRORFILE | Writes each rejected row to a text file for review | A path the SQL service account can write to |
| MAXERRORS | Maximum errors before the import aborts entirely | 0 for strict pipelines; 100–1000 for exploratory loads |
The error file reveals the failure pattern: encoding corruption shows up as garbled characters, type mismatches show the literal string that failed conversion, and primary key violations show which ID was already present. Once you know the pattern, the fix is usually one of the five categories above. For primary key conflicts specifically — where the error file is most valuable — see the identity columns and primary keys guide.
Bulk insert failures in one sentence
Almost every BULK INSERT failure is caused by one of three things: Permissions (the service account can't read the file), Length or Type (the data doesn't fit the schema), or Delimiters (the parser can't find where rows and columns end). Use a staging table to eliminate type and length errors, ERRORFILE to isolate which rows fail, and fix permissions at the service account level — not at the file level.
Dig deeper
Skip the debugging entirely
Elvity validates CSV structure, types, and encoding before a single row touches your database — surfacing errors as named, actionable issues rather than cryptic error codes.