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

by

Troubleshooting Guide: Error 1071 – Cannot Specify a JOIN Algorithm with a Remote JOIN

Why This Error Happens

Error 1071 occurs when a query hint such as LOOP JOIN, HASH JOIN, or MERGE JOIN is explicitly specified in a query that involves a remote JOIN operation against a linked server. SQL Server’s query optimizer cannot enforce a local join algorithm hint on a remote data source because the remote server (not the local instance) is responsible for determining its own execution plan for that portion of the query. This conflict typically arises in distributed queries, linked server calls, or queries using OPENQUERY/four-part naming conventions combined with join hints in the OPTION clause or inline hints.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Join Hint

  • Open SQL Server Management Studio (SSMS) and locate the query referenced in the error message or SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
  • Look for explicit join hints like INNER LOOP JOIN, INNER HASH JOIN, or INNER MERGE JOIN used alongside a four-part name (LinkedServerName.Database.Schema.Table) or OPENQUERY().
  • Confirm the remote object is indeed hosted on a linked server by running sp_linkedservers in a new query window.

Step 2: Remove or Relocate the JOIN Hint

  • Remove the explicit join algorithm hint from the T-SQL statement and allow the SQL Server Query Optimizer to choose the join strategy automatically.
  • If a specific join strategy is required, restructure the query so the remote data is pulled into a temporary table first, then apply the join hint locally:
  SELECT * INTO #RemoteData FROM OPENQUERY(LinkedServerName, 'SELECT * FROM RemoteTable');
  SELECT a.* FROM LocalTable a INNER LOOP JOIN #RemoteData b ON a.ID = b.ID;
  • Save and re-execute the modified query to confirm the hint no longer targets the remote object.

Step 3: Validate Linked Server Configuration

  • Open SSMS, navigate to Server Objects > Linked Servers, right-click the relevant linked server, and select Properties.
  • Under the Server Options tab, verify that Collation Compatible and RPC/RPC Out settings are correctly configured, as misconfigured linked servers can sometimes trigger related join-processing errors.
  • Run EXEC sp_testlinkedserver 'LinkedServerName'; in a query window to confirm connectivity before retesting the original query.

Step 4: Clear and Rebuild the Query Execution Plan Cache

  • Execute DBCC FREEPROCCACHE; to clear cached execution plans that may be referencing the invalid hint combination.
  • Re-run the corrected query and check the Actual Execution Plan (Ctrl+M in SSMS) to confirm the remote JOIN is now resolved without a local algorithm hint being pushed to the linked server.

Alternative or Verification Steps

  • Run the corrected query with SET STATISTICS PROFILE ON; to confirm Error 1071 no longer appears in the Messages tab of SSMS.
  • As an alternative, use OPENQUERY exclusively for the remote portion of the query (avoiding four-part names entirely), which forces the remote server to resolve its own execution plan and sidesteps join-hint conflicts altogether.