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

by

Troubleshooting Guide: SQL Server Error 8107 – IDENTITY_INSERT Conflict

Why This Error Happens

This error occurs because SQL Server only allows the IDENTITY_INSERT property to be set to ON for one table per session, per database, at any given time. If a previous script or transaction enabled IDENTITY_INSERT for a table and failed to turn it back OFF due to an error or premature termination, the session retains a “dirty” state. Consequently, when a new script attempts to enable IDENTITY_INSERT on a different table within the same session without first disabling it on the original table, SQL Server rejects the request to prevent data integrity conflicts.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Table

  • Read the specific error message text carefully; SQL Server explicitly names the table that currently has the property enabled (e.g., dbo.Table_A).
  • Note this table name, as you will need to target it directly to reset the session state.

Step 2: Manually Toggle the Property OFF

  • Open a New Query window in SQL Server Management Studio (SSMS) connected to the same session or database.
  • Execute the following T-SQL command, replacing the table name with the one identified in the error message:
  • SET IDENTITY_INSERT dbo.Table_A OFF;
  • This clears the “ON” flag associated with that specific table, freeing up the session to work with other tables.

Step 3: Audit the Script for Missing OFF Statements

  • Review the stored procedure or script that triggered the initial error.
  • Ensure that every SET IDENTITY_INSERT [Table] ON command is immediately followed by a corresponding INSERT statement and a SET IDENTITY_INSERT [Table] OFF command before the script attempts to touch a different table.
  • Add TRY...CATCH blocks around the insert logic to guarantee the OFF command executes even if the INSERT statement fails.

Step 4: Restart the Session (If Manual Reset Fails)

  • If you cannot identify the specific table or the manual OFF command returns an error stating the property wasn’t enabled, the session state might be corrupted.
  • Close the specific query tab/connection in SSMS and open a brand new connection to the database.
  • Since IDENTITY_INSERT is scoped to the session, closing the connection automatically resets the property for the entire database.

Alternative or Verification Steps

  • Verification: Run your intended INSERT statement immediately after executing the SET IDENTITY_INSERT [TargetTable] ON command. If the insert succeeds without Error 8107 appearing, the session state has been successfully cleared.
  • Alternative: If the issue persists across multiple sessions, check for orphaned transactions using sp_who2 to ensure no other blocking session is holding a lock that interferes with the schema state.