Update fails on linked server after upgrading to SQL Server 2022

Question

After upgrading SQL Server from 2019 to 2022 the following query with linked server has started to fail (I've omitted some column names and details for simplicity):

UPDATE [<remote server>].[<remote db>].dbo.Projects
SET    RemoteColumnName = p.LocalColumnName
FROM   prod.Projects p
WHERE  p.ProjectID = Projects.ProjectID;

However basic updates without involving local table work just fine. SQL Server reports the following error which I cannot even find in documentation:

Msg 15905, Level 16, State 6, Line 44
Query not supported: Cannot determine result column sources.  Invalid metadata.

Both servers are SQL Server 2022, prior to upgrading everything worked just fine.

Adding DDL for local and remote tables just in case. However, the problem doesn't seem to be schema related and reproduces on different tables with different data types.

-- Table on the remote linked server
CREATE TABLE dbo.Projects
(
    ProjectID               BIGINT           NOT NULL,
    RemoteColumnName        VARCHAR(255)     NOT NULL,
    CONSTRAINT PK_Projects PRIMARY KEY CLUSTERED (ProjectID)
)

-- Table on the local server
CREATE TABLE prod.Projects
(
    ProjectID               BIGINT           NOT NULL,
    LocalColumnName         VARCHAR(255)     NOT NULL,
    CONSTRAINT PK_Projects PRIMARY KEY CLUSTERED (ProjectID)
)

Adding linked server definition script. Account RemoteUser is in db_owner role for the target database on the remote server. Both databases are in 160 compatibility level. The problem reproduces even on empty tables.

EXEC master.dbo.sp_addlinkedserver @server = N'IS', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IS',@useself=N'False',@locallogin=NULL,@rmtuser=N'RemoteUser',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'IS', @optname=N'remote proc transaction promotion', @optvalue=N'false'

Answer 1

This error occurs if you use Dynamic Data Masking on any tables in the calling database (not just the table you reference in your query).

It is enough to have ever used DDM in the calling database. Once used, the error persists even if you drop every table that used DDM. I haven't found a way to reset this status yet.

You can check for tables using DDM using the query in that documentation link:

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;

This error is unexpected behaviour that you should report to Microsoft Support so they can investigate a fix.

Many workarounds are possible before a fix is available, including replacing the update with a delete & insert sequence as you suggested.

Another option is to copy the matching source rows to a temporary table or table variable and reference that in your update instead of the source table directly:

SELECT P.* 
INTO #Projects
FROM prod.Projects AS P
WHERE EXISTS
(
    SELECT * 
    FROM [<remote server>].[<remote db>].dbo.Projects AS RP
    WHERE RP.ProjectID = P.ProjectID
);

UPDATE RP
SET RP.RemoteColumnName = P.LocalColumnName
FROM #Projects AS P
JOIN [<remote server>].[<remote db>].dbo.Projects AS RP
    ON RP.ProjectID = P.ProjectID;

---

For those interested, the error occurs on the second call to ExtractSourceNames during compilation: