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

Sergey Rybalkin 20 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,897 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

    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

    0 comments No comments