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

by

Troubleshooting Guide: Error 5347 — USETYPEDEFAULT Option Cannot Be Provided Without WITH Schema

Why This Error Happens

This error occurs when a BULK INSERT, OPENROWSET(BULK...), or format file operation specifies the USE_TYPE_DEFAULT option without also declaring the WITH clause schema definition required to interpret column data types. SQL Server 2025 requires an explicit schema context so it knows how to apply default values to columns containing missing or NULL data during a bulk load. Without this schema declaration, the engine cannot resolve how USE_TYPE_DEFAULT should map to target column types, causing the statement to fail at parse time.

Step-by-Step Resolution Guide

Step 1: Review the Bulk Operation Syntax

  • Open the T-SQL script or stored procedure that triggered the error (check SSMS query window or the calling application’s SQL log).
  • Confirm whether USE_TYPE_DEFAULT is being passed as a standalone option inside BULK INSERT, OPENROWSET, or a format file reference.
  • Identify the exact command, for example:
  BULK INSERT dbo.MyTable
  FROM 'C:\Data\import.csv'
  WITH (USE_TYPE_DEFAULT);

Step 2: Add the Required WITH Schema Clause

  • Modify the statement to include a full WITH clause that defines formatting and schema-related options alongside USE_TYPE_DEFAULT.
  • Example corrected syntax:
  BULK INSERT dbo.MyTable
  FROM 'C:\Data\import.csv'
  WITH (
      FORMATFILE = 'C:\Data\import.fmt',
      USE_TYPE_DEFAULT = 1,
      FIRSTROW = 2
  );
  • Ensure the format file path (C:\Data\import.fmt) exists and is accessible by the SQL Server service account on Windows Server 2025.

Step 3: Validate the Format File Schema

  • Open the .fmt file in a text editor and confirm it contains valid column definitions matching the target table structure.
  • If the format file is missing or corrupted, regenerate it using the bcp utility:
  bcp dbo.MyTable format nul -c -f C:\Data\import.fmt -S localhost -T
  • Re-run the BULK INSERT statement after confirming the format file is valid.

Step 4: Check Permissions on File and Database Objects

  • Verify the SQL Server service account (visible in Services.msc on Windows Server 2025 / Windows 11) has Read access to the source file and format file directories.
  • Confirm the executing login has ADMINISTER BULK OPERATIONS or bulkadmin server role permissions.
  • Restart the SQL Server (MSSQLSERVER) service if permission changes were made.

Alternative or Verification Steps

  • Re-execute the corrected BULK INSERT or OPENROWSET statement and confirm it completes without Error 5347 in SQL Server Management Studio’s Messages tab.
  • If the error persists, bypass USE_TYPE_DEFAULT entirely and instead define explicit column defaults in the target table schema using ALTER TABLE ... ADD CONSTRAINT DEFAULT.