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

by

Troubleshooting Guide: SQL Server Error 4071 (XP Callback Failure in INSERT-EXEC)

Why This Error Happens

This error occurs because SQL Server restricts extended stored procedures executed inside an INSERT...EXEC statement to returning only a single result set—no print messages, error messages, or output parameters are permitted through the XP callback interface. The conflict typically arises when a legacy extended procedure (such as a custom XP or xp_cmdshell-style wrapper) attempts to send informational messages via srv_message or similar ODS API calls while its output is simultaneously being captured by the INSERT...EXEC construct. This is a protocol-level limitation enforced by the SQL Server Open Data Services (ODS) layer, not a permissions or connectivity issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Extended Procedure and Query

  • Capture the exact query text using Extended Events (sqlserver.error_reported filtered on Error = 4071) or by reviewing the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
  • Note the extended procedure name referenced in the error message (the %.*ls placeholder will resolve to the actual XP name, e.g., xp_myCustomProc).
  • Confirm whether the call is wrapped in INSERT INTO #TempTable EXEC xp_myCustomProc.

Step 2: Refactor the Code to Avoid INSERT-EXEC with XPs

  • Rewrite the extended procedure call to execute standalone (without INSERT...EXEC), and instead have the XP write directly to a table using an internal SQLExecDirect/ODBC call from within the C/C++ XP code if you control its source.
  • If you do not control the XP source, modify the calling T-SQL batch to capture output using OLE Automation Procedures (sp_OACreate) or a CLR Table-Valued Function instead of INSERT...EXEC.
  • Example safe pattern:
  EXEC xp_myCustomProc;
  -- Avoid: INSERT INTO #Temp EXEC xp_myCustomProc;

Step 3: Update or Patch the Extended Procedure DLL

  • Check the vendor or internal development team for an updated build of the XP DLL that suppresses srv_message calls when invoked in a result-set-only context.
  • Locate the DLL under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and verify version/timestamp against the vendor’s latest release.
  • Replace the DLL, restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER, and re-test.

Step 4: Enable Trace Flag for Diagnostic Logging (Optional)

  • Add Trace Flag 3621 or enable verbose XP logging temporarily via SQL Server Configuration Manager under Startup Parameters to capture more context on which callback (print, error, or message) triggered the failure.
  • Remove the trace flag once root cause is confirmed, as it adds overhead.

Step 5: Migrate Away from Legacy Extended Procedures

  • Evaluate replacing the extended procedure entirely with a CLR Integration stored procedure, which natively supports structured result sets without ODS callback restrictions.
  • Enable CLR integration if not already active:
  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

Alternative or Verification Steps

  • Re-run the original query outside of INSERT...EXEC (standalone EXEC) to confirm the XP executes cleanly and the 4071 error does not reproduce.
  • If immediate remediation isn’t possible, wrap the XP call in a stored procedure that uses SET NOCOUNT ON and suppresses PRINT/RAISERROR statements internally, then validate via SQL Server Profiler/Extended Events that no error-class messages are emitted during the INSERT...EXEC execution.