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

by

Troubleshooting Guide: SQL Server Error 11104

Why This Error Happens

Error 11104 (“The provider could not support insertion on this table”) occurs when SQL Server attempts to perform a distributed query or linked server operation (via INSERT, OPENQUERY, or OPENROWSET) against a target table that the OLE DB provider cannot handle for write operations. This typically happens because the linked server’s OLE DB provider does not support the IRowsetChange interface required for row insertion, or because the target table contains unsupported data types, missing primary keys, or read-only constraints. It can also surface when provider-specific settings like AllowInProcess or DynamicParameters are misconfigured on Windows Server 2025 / Windows 11 hosts.

Step-by-Step Resolution Guide

Step 1: Verify Linked Server Provider Capabilities

  • Open SQL Server Management Studio (SSMS) and navigate to Server Objects > Linked Servers > Providers.
  • Right-click the relevant OLE DB provider (e.g., MSOLEDBSQL, Microsoft.ACE.OLEDB.16.0) and select Properties.
  • Ensure “Allow InProcess” is checked, as out-of-process providers frequently fail on insert operations.
  • Confirm the provider version supports IRowsetChange; outdated providers (pre-2019) often lack this support.

Step 2: Check Target Table Structure

  • Confirm the destination table has a defined Primary Key or unique identifier column; providers often reject inserts on heap tables without one.
  • Verify no columns use unsupported data types (e.g., sql_variant, XML, or large deprecated TEXT/NTEXT types) for the linked provider.
  • Run the following to inspect constraints:
EXEC sp_help 'YourTargetTable';
  • Remove or modify any INSTEAD OF triggers that may block the insertion interface.

Step 3: Reconfigure the Linked Server Settings

  • Open a new query window and update the linked server options:
EXEC sp_serveroption 'YourLinkedServer', 'rpc out', 'true';
EXEC sp_serveroption 'YourLinkedServer', 'data access', 'true';
  • Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to apply provider-level changes.
  • If using OPENROWSET, ensure Ad Hoc Distributed Queries is enabled:
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Step 4: Switch to a Staging Table Workaround

  • If the provider inherently lacks insert support (common with legacy Jet/ACE or ODBC-based providers), insert into a local staging table first.
  • Use a standard INSERT INTO LocalStagingTable SELECT * FROM OPENQUERY(...) pattern instead of a direct remote insert.
  • Push data to the final destination using a native INSERT statement from the staging table rather than through the OLE DB provider directly.

Alternative or Verification Steps

  • Verify the fix: Re-run the original INSERT/OPENQUERY statement; if successful, check sys.dm_exec_connections to confirm the linked server connection is stable and using the correct provider.
  • Alternative: If the error persists, replace the legacy OLE DB linked server connection with a native ODBC Driver 18 for SQL Server connection, which has broader support for write-back operations on Windows Server 2025.