Update statement fails on linked SQL Server 2022 with data masking used

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;

Error message:

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

This error occurs if I use Dynamic Data Masking on any tables in the calling database. More details can be found in the following SO discussion - https://dba.stackexchange.com/questions/322764/update-fails-on-linked-server-after-upgrading-to-sql-server-2022

Answer 1

Seeing that Paul White has investigated the case on StackOverflow, I see no need make my own investigations. I have full faith in Paul.

If this is a blocking issue for you, you should open a support case with Microsoft. If you only want to let Microsoft know, you can report the bug on this site: https://feedback.azure.com/forums/908035-sql-server. But to repeat: if you need a fix, you need to open a support case.

Answer 2

Hi @Sergey Rybalkin

Judging from the error message, this should be a case that needs to be reported to Microsoft.

You can refer to Erland's advice.

Best regards,

Percy Tang