Skip to content

Schema compare issue with sensitivity classifications #474

@zijchen

Description

@zijchen
  • SqlPackage or DacFx Version: 162.3.566
  • .NET Framework (Windows-only) or .NET Core: both
  • Environment (local platform and source/target platforms): Azure and SQL 2022

Steps to Reproduce:

  1. Create 2 database projects, A.sqlproj and B.sqlproj, both targeting Sql160
  2. Add a script with the following to A.sqlproj
    CREATE TABLE [dbo].[t1] ([c0] INT, [c1] INT, [c2] VARCHAR(32) NOT NULL);
    GO
    
    
    ADD SENSITIVITY CLASSIFICATION TO [t1].[c0] WITH(LABEL = 'Label0', INFORMATION_TYPE = 'InfoType0', RANK = High);
    GO
  3. Add a script with the following to B.sqlproj (Note the only difference from A.sqlproj is the missing column c2)
    CREATE TABLE [dbo].[t1] ([c0] INT, [c1] INT);
    GO
    
    
    ADD SENSITIVITY CLASSIFICATION TO [t1].[c0] WITH(LABEL = 'Label0', INFORMATION_TYPE = 'InfoType0', RANK = High);
    GO
  4. Now open schema compare in SSDT or ADS; or compare via DacFx API by creating 2 SchemaCompareProjectEndpoint.
  5. Apply the changes to the target project. An extra definition for the sensitivity classification is added to the target.
    CREATE TABLE [dbo].[t1] ([c0] INT, [c1] INT);
    GO
    
    ADD SENSITIVITY CLASSIFICATION TO
        [dbo].[t1].[c0]
        WITH (LABEL = 'Label0', INFORMATION_TYPE = 'InfoType0', RANK = HIGH);
    GO
    
    ADD SENSITIVITY CLASSIFICATION TO [t1].[c0] WITH(LABEL = 'Label0', INFORMATION_TYPE = 'InfoType0', RANK = High);
    GO

It appears that the script generator for the table includes the sensitivity classification in its batch, but when applying schema compare changes, DacFx does not expect a second batch in the statement it's looking to replace. It does not matter if the comparison source is a project, dacpac or database.

Did this occur in prior versions? If not - which version(s) did it work in?
N/A

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions