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

Sergey Rybalkin 20 Reputation points
2023-10-02T10:30:23.43+00:00

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
    2023-10-02T20:53:51.3+00:00

    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
    2023-10-03T02:57:57.6766667+00:00

    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