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

by

Troubleshooting Guide: SQL Server Error 4147 — Non-ANSI Outer Join Operators

Why This Error Happens

This error occurs because SQL Server 2025 has fully deprecated the legacy *= and =* outer join syntax that was used in older versions of Transact-SQL. By default, the ANSI_NULLS and compatibility level settings in modern databases reject this non-standard syntax to enforce ANSI SQL-92 compliant join behavior. This typically surfaces when legacy applications, stored procedures, or migrated scripts written for SQL Server 2000 or earlier are executed against a database with a current compatibility level.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Stored Procedure

  • Capture the exact query text from the error message or SQL Server error log.
  • Use sys.dm_exec_query_stats combined with sys.dm_exec_sql_text to locate the procedure or ad-hoc batch containing *= or =*.
  • Search application source code or SSIS packages for legacy join syntax if the query originates externally.

Step 2: Rewrite the Query Using ANSI Join Syntax

  • Replace *= with LEFT OUTER JOIN ... ON syntax.
  • Replace =* with RIGHT OUTER JOIN ... ON syntax.
  • Example conversion:
  -- Old (invalid in SQL Server 2025)
  SELECT * FROM TableA, TableB WHERE TableA.ID *= TableB.ID

  -- New (ANSI-compliant)
  SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.ID = TableB.ID
  • Test the rewritten query in SQL Server Management Studio (SSMS) to confirm correct result sets.

Step 3: Check Database Compatibility Level (Temporary Workaround Only)

  • Open SSMS and right-click the database, then select Properties > Options.
  • Note the current Compatibility Level value (e.g., 160 for SQL Server 2022, 170 for SQL Server 2025).
  • Run the following to temporarily lower compatibility level as a stopgap while code is rewritten:
  ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 80;
  • Understand this is not officially supported long-term, as Microsoft has removed the old join parser entirely in SQL Server 2025 — this step may not resolve the error at all and should not be relied upon as a permanent fix.

Step 4: Update Application Code and Deployment Scripts

  • Search all .sql, .dtsx, and application connection layers (ORMs, stored procedures, views) for legacy syntax patterns.
  • Use a script like the following to scan stored procedure definitions:
  SELECT OBJECT_NAME(object_id), definition
  FROM sys.sql_modules
  WHERE definition LIKE '%*=%' OR definition LIKE '%=*%';
  • Deploy corrected scripts through your standard CI/CD or change management pipeline.

Step 5: Validate ANSINULLS and ANSIWARNINGS Session Settings

  • Ensure connections use SET ANSI_NULLS ON and SET ANSI_WARNINGS ON, as required defaults in SQL Server 2025.
  • Check client driver configuration (ODBC, OLE DB, or ADO.NET) to confirm these settings aren’t being overridden at the connection string level.

Alternative or Verification Steps

  • Re-run the corrected query or execute the stored procedure manually in SSMS to confirm Error 4147 no longer appears and result sets match expected output.
  • If immediate rewriting isn’t feasible, consider isolating legacy workloads on a separate instance running an older supported SQL Server version until code remediation is complete, since compatibility-level workarounds are not guaranteed in 2025.