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

by

Troubleshooting Guide: SQL Server Error 271

Why This Error Happens

Error 271 occurs when an application or T-SQL statement attempts to directly INSERT or UPDATE a column that SQL Server does not permit to be written to. This is a logical/schema-level restriction rather than a corruption issue — it happens because the target column is either defined as a computed column (its value is derived from an expression involving other columns) or because the query is running against a view built with a UNION operator, which SQL Server treats as read-only for the columns involved in the set operation. Attempting to write directly to these columns violates the underlying table or view’s schema design.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column

  • Run the failing INSERT/UPDATE statement and note the exact column name in the error message (%.*ls will show the real column name at runtime).
  • Query the table definition to confirm the column type:
SELECT name, is_computed 
FROM sys.columns 
WHERE object_id = OBJECT_ID('YourTableName');
  • If is_computed = 1, this confirms the column is computed and cannot be written to directly.

Step 2: Check for View-Based UNION Restrictions

  • If the target is a view, inspect its definition:
sp_helptext 'YourViewName';
  • Look for a UNION or UNION ALL clause combining multiple SELECT statements.
  • Note that columns involved in the UNION become read-only in the resulting view, even if the underlying base tables allow writes.

Step 3: Modify the Query Logic Instead of the Computed Column

  • Remove the computed column from your INSERT/UPDATE column list entirely — do not attempt to pass a value for it.
  • If you need to control the calculated result, update the base columns that feed into the computed column’s formula instead.
-- Incorrect
UPDATE dbo.Orders SET TotalPrice = 100 WHERE OrderID = 1;

-- Correct (assuming TotalPrice = Quantity * UnitPrice)
UPDATE dbo.Orders SET Quantity = 10, UnitPrice = 10 WHERE OrderID = 1;

Step 4: Redirect Writes to the Base Table (For Views)

  • If writing through a UNION view is required, identify which base table the specific row belongs to using a discriminator column (e.g., SourceTable or RegionID).
  • Issue the INSERT/UPDATE directly against that specific base table rather than through the combined view.
-- Instead of updating vw_CombinedSales (which uses UNION)
UPDATE dbo.SalesEast SET Amount = 500 WHERE SaleID = 42;

Step 5: Adjust the View Definition (If Persistent Write Access Is Needed)

  • If application logic strictly requires writing through a view, refactor the view to remove the UNION and use JOIN or filtered logic where feasible.
  • Alternatively, implement an INSTEAD OF trigger on the view to programmatically route the INSERT/UPDATE to the correct base table.
CREATE TRIGGER trg_InsteadOfUpdate
ON vw_CombinedSales
INSTEAD OF UPDATE
AS
BEGIN
    -- Custom logic to route update to correct base table
END;

Alternative or Verification Steps

  • Verify: Re-run the original INSERT/UPDATE statement excluding the computed column or targeting the base table directly; confirm the statement completes without Error 271.
  • Alternative: If using an ORM (Entity Framework, Dapper, etc.), check the model/mapping configuration to ensure computed columns are marked as DatabaseGenerated(DatabaseGeneratedOption.Computed) (or equivalent) so the ORM excludes them from generated INSERT/UPDATE statements automatically.