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

by

Troubleshooting Guide: SQL Server Error 681

Why This Error Happens

Error 681 occurs when an INSERT or UPDATE statement attempts to explicitly assign a NULL value to a column that was defined with a NOT NULL constraint at the schema level. This is typically caused by application code sending incomplete or improperly mapped data, a missing DEFAULT constraint that should have supplied a fallback value, or an ETL/import process pulling source data that contains blank fields where the destination schema demands a mandatory value. The database engine correctly rejects the transaction to preserve data integrity, but the failure will persist until either the incoming data or the schema definition is corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Statement

  • Open the failed query in SQL Server Management Studio (SSMS) and review the full error text; it will explicitly name the table and column violating the constraint.
  • Run the following to confirm the column’s nullability:
  SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'YourTableName';
  • Cross-reference the application source code or import mapping file to see where the NULL value originates.

Step 2: Correct the Source Data or Application Logic

  • If the NULL is unintentional (e.g., a bug in application code or a missing form validation), patch the application layer to enforce a valid value before submission.
  • For ETL jobs (SSIS packages, BCP, or bulk insert scripts), open the package/configuration and add a Derived Column transformation or ISNULL() wrapper to substitute a default value:
  UPDATE YourTable SET YourColumn = ISNULL(YourColumn, 'DefaultValue') WHERE YourColumn IS NULL;
  • If using command-line tools like bcp on Windows Server 2025, verify the format file (.fmt) correctly maps source fields and does not leave gaps for required columns.

Step 3: Add a Default Constraint (If Business Logic Allows)

  • If it’s acceptable for the column to auto-populate rather than fail, add a DEFAULT constraint via SSMS or T-SQL:
  ALTER TABLE YourTable 
  ADD CONSTRAINT DF_YourColumn DEFAULT ('N/A') FOR YourColumn;
  • This ensures that omitted values (not explicit NULLs) are handled gracefully going forward, though explicit NULL in an INSERT statement will still fail as expected.

Step 4: Test the Corrected Transaction

  • Re-run the original INSERT/UPDATE statement in SSMS or Azure Data Studio on Windows 11 to confirm the transaction now commits successfully.
  • Check SQL Server Error Logs (via EXEC sp_readerrorlog or Event Viewer under Applications and Services Logs > MSSQLSERVER) to confirm Error 681 no longer appears for this table.

Alternative or Verification Steps

  • Run DBCC CHECKCONSTRAINTS('YourTable') to validate that no existing rows violate the NOT NULL constraint after your schema/data fix.
  • If the issue persists across multiple tables, consider temporarily enabling a server-side trace or Extended Events session filtered on error_reported with Error Number = 681 to catch every occurrence in real time and pinpoint the exact application module generating the faulty inserts.