How to fix Error 4847 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 4847 – Bulk Copy Operation Incompatible with bigint Columns

Why This Error Happens

Error 4847 occurs when the bulk copy program (bcp) or a bulk insert operation uses a legacy data format file (version 7.0 or earlier) or an outdated client provider that does not recognize the bigint data type, which was only introduced in SQL Server 2000. This typically happens when a format file was generated by an older version of bcp.exe, or when the connection is using an outdated OLE DB/ODBC driver that defaults to legacy compatibility levels. Essentially, the client-side tool is attempting to map an 8-byte integer to a data type schema that predates its existence, causing SQL Server 2025 to reject the operation.

Step-by-Step Resolution Guide

Step 1: Regenerate the Format File

  • Locate the existing .fmt file used in your bulk insert command (often found in your ETL scripts or C:\BCP\ directory).
  • Delete the old format file and regenerate it using the native format flag to ensure compatibility with modern data types:
  • bcp YourDatabase.dbo.YourTable format nul -c -x -f YourTable.xml -S YourServer -T
  • Ensure the version tag at the top of the generated XML or non-XML format file does not reflect a pre-2000 schema version.

Step 2: Update the Client Connectivity Drivers

  • Verify you are not using the legacy “SQL Server” driver (ODBC) which has compatibility ceilings.
  • On Windows 11 or Windows Server 2025, open ODBC Data Source Administrator (search odbcad32.exe).
  • Update the connection to use the ODBC Driver 18 for SQL Server or the native OLE DB Driver 19 for SQL Server.
  • Modify your connection string or DSN to point to the updated driver before re-running the bcp utility or BULK INSERT command.

Step 3: Check the BULK INSERT T-SQL Syntax

  • If running the command via T-SQL rather than the command line, open SQL Server Management Studio (SSMS) 21.
  • Verify your script is not explicitly specifying a legacy version flag in the WITH clause.
  • Remove any instance of DATAFILETYPE = 'char' paired with old fixed-length assumptions if the source data contains large integers; switch to native or widechar formats:
  BULK INSERT YourTable
  FROM 'C:\Data\import.dat'
  WITH (
      DATAFILETYPE = 'native',
      FORMATFILE = 'C:\BCP\YourTable.xml'
  );

Step 4: Validate SQL Server Native Client Installation

  • Navigate to Control Panel > Programs and Features on the Windows Server 2025 host.
  • Confirm that “Microsoft SQL Server Native Client” (if required for legacy app support) is updated or replaced by the Microsoft.Data.SqlClient library if running via an application rather than CLI.
  • Restart the SQL Server Agent service to clear any cached legacy metadata:
  • Restart-Service -Name 'SQLSERVERAGENT'

Alternative or Verification Steps

  • Verification: Re-run the bcp command with the -V80 or higher version flag explicitly set (e.g., bcp ... -V170) to force the utility to acknowledge modern bigint support before attempting the full data load.
  • Alternative: If the format file continues to cause issues, bypass it entirely by using the OPENROWSET(BULK...) function in a T-SQL query, which often handles modern data type mapping more gracefully than the standalone bcp.exe tool.