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

by

Troubleshooting Guide: SQL Server Error 405 – Remote Table as DML Target with OUTPUT Clause

Why This Error Happens

This error occurs because SQL Server’s remote query processor cannot support an OUTPUT clause or nested DML statements (such as an INSERT containing an UPDATE/DELETE) when the target table resides on a linked server. The transactional consistency required to return modified rows via OUTPUT cannot be guaranteed across a distributed connection using the OLE DB provider. This is a fundamental architectural limitation rather than a configuration bug, meaning it typically requires a query rewrite rather than a server-side fix.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Locate the DML statement (INSERT, UPDATE, DELETE, or MERGE) that targets a linked server table (e.g., [LinkedServerName].[Database].[Schema].[Table]).
  • Check specifically for an OUTPUT INTO or OUTPUT clause referencing this remote table.
  • Confirm the remote target using sys.servers in SSMS: SELECT * FROM sys.servers WHERE is_linked = 1;

Step 2: Remove the Direct OUTPUT Clause from the Remote DML

  • Rewrite the query to eliminate the OUTPUT clause from the statement executing against the remote table directly.
  • Split the operation into two distinct steps: first, execute the remote DML without the OUTPUT clause, then perform a separate SELECT to verify results if row-tracking is needed.
  • Example fix:
  -- Instead of:
  -- INSERT INTO LinkedServer.DB.dbo.RemoteTable OUTPUT INSERTED.* INTO @LocalTable VALUES (...)

  -- Use:
  INSERT INTO LinkedServer.DB.dbo.RemoteTable (Col1, Col2) VALUES (@Val1, @Val2);

Step 3: Use a Local Staging Table to Simulate the OUTPUT Behavior

  • Insert the data into a local temporary table (#TempStaging) first with the OUTPUT clause capturing the inserted rows.
  • Once local data is verified, push the finalized rows to the remote table using a straightforward INSERT INTO...SELECT without an OUTPUT clause.
  • This decouples the “logging/tracking” requirement (local) from the “data transfer” requirement (remote).

Step 4: Verify Linked Server Provider Settings (If Rewrite Isn’t Feasible)

  • Open SQL Server Management Studio (SSMS) and navigate to Server Objects > Linked Servers > Providers.
  • Right-click the specific OLE DB provider (e.g., MSOLEDBSQL) and select Properties.
  • Ensure “Allow InProcess” is checked, as this can sometimes resolve related distributed transaction handling errors, though it will not bypass Error 405 for direct OUTPUT clauses.

Alternative or Verification Steps

  • Verification: Run the modified query in SSMS on Windows Server 2025; if the syntax error disappears and rows are successfully transferred to the remote table without the engine throwing Error 405, the rewrite was successful.
  • Alternative: If application logic strictly requires the returned IDs/rows from the remote insert, consider using a Linked Server Stored Procedure call (EXEC LinkedServer.DB.dbo.YourProc) where the logic (including OUTPUT handling) is processed entirely on the remote server’s side rather than initiated from the local distributed query.