0

Apology

First off, apologies for length - this is a weird issue so I'm trying to give enough detail to make it reproducible.


tl;dr

So I've think found an issue while exporting a *.bacpac Data Tier Application between two MS SQL Servers to migrate an instance of an Indeo ProGet database which creates an inconsistent bacpac. Trying to import this bacpac results in an error, and I'd appreciate a second pairs of eyes on the problem to make sure I'm not doing anything daft...

I think the root cause is a possible bug in the "Export data-tier application" process when there are multiple schema columns using the same User Defined Type, and that UDT is bound to a validation Rule - the result seems to be a corrupt *.bacpac file that can't be imported without doing some manual tweaking first.

My question is:

  • Part 1: Am I doing something wrong, or is this a known (or new) bug?
  • Part 2: If it's a bug, do you have any idea where I can report it?

Schema

The following re-creates a small part of the schema from the Inedo ProGet database that demonstrates the issue. (FWIW, I'm currently using SQL Server 2022 v16.0.1105.1, but I'm pretty sure it occurs in other versions as well).

It basically does this:

  • Creates a User Defined Type YNINDICATOR
  • Binds it to a rule that restricts the values to Y and N
  • Creates two tables that each have a column of type YNINDICATOR (see [dbo].[CustomLanguage].[Active_Indicator] and [dbo].[ClusterNodes].[Primary_Indicator])
CREATE TYPE [dbo].[YNINDICATOR] FROM [char](1) NULL
GO

CREATE RULE [dbo].[YNINDICATOR_Domain] 
AS
@Ind COLLATE Latin1_General_BIN IN ('Y', 'N');

EXEC sp_bindrule 'YNINDICATOR_Domain', 'YNINDICATOR'
GO

CREATE TABLE [dbo].[CustomLanguages](
    [CustomLanguage_Id] [int] IDENTITY(1,1) NOT NULL,
    [Culture_Name] [varchar](50) NOT NULL,
    [Language_Name] [nvarchar](100) NOT NULL,
    [Active_Indicator] [dbo].[YNINDICATOR] NOT NULL,
    [CustomLanguage_Xml] [xml] NOT NULL,
 CONSTRAINT [PK__CustomLanguages] PRIMARY KEY CLUSTERED 
(
    [CustomLanguage_Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[ClusterNodes](
    [Server_Name] [nvarchar](64) NOT NULL,
    [NodeType_Code] [char](1) NOT NULL,
    [LastUpdated_Date] [datetime] NOT NULL,
    [Primary_Indicator] [dbo].[YNINDICATOR] NOT NULL,
    [Node_Configuration] [xml] NOT NULL,
 CONSTRAINT [PK__ClusterNodes] PRIMARY KEY CLUSTERED 
(
    [Server_Name] ASC,
    [NodeType_Code] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClusterNodes]  WITH CHECK ADD  CONSTRAINT [CK__ClusterNodes__NodeType_Code] CHECK  (([NodeType_Code]='S' OR [NodeType_Code]='W'))
GO
ALTER TABLE [dbo].[ClusterNodes] CHECK CONSTRAINT [CK__ClusterNodes__NodeType_Code]
GO

Error

Exporting the above schema into a Data Tier Application bacpac file completes without any errors, but attempting to re-import it as a new database gives the following error:

enter image description here

Microsoft SQL Server Management Studio

Could not import package.

Error SQL72014: Framework Microsoft SqlDataClient Data Provider: MSG 2714, Level 16, State 3, Procedure YNINDICATOR_Domain, Line 1 There is already an object named 'YNINDICATOR_Domain' in the database.

Error SQL72045: Script execution error. The executed script:

CREATE RULE [dbo].[YINDICATOR_Domain]

AS @Ind COLLATE Latin1_General_BIN IN ('Y', 'N')

(Microsoft.SqlServer.Dac)


Investigation

I've unzipped the bacpac and found that when there is more than one column defined using the YNINDICATOR UDT, the model.xml file inside the bacpac contains something like this, with two SqlRule elements defined for [dbo].[YNINDICATOR_Domain] - the first contains the column references, and the second contains the UDT reference:

Broken bacpac - two column references and a UDT reference -> two SqlRule elements

... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
    <Property Name="ExpressionScript">
        <Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
    </Property>
    <Relationship Name="BoundTargets">
        <Entry>
            <References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
        </Entry>
    </Relationship>
    <Relationship Name="Schema">
        <Entry>
            <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
    </Relationship>
</Element>
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
    <Property Name="ExpressionScript">
        <Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
    </Property>
    <Relationship Name="BoundTargets">
        <Entry>
            <References Name="[dbo].[YNINDICATOR]" />
        </Entry>
    </Relationship>
    <Relationship Name="Schema">
        <Entry>
            <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
    </Relationship>
</Element>
... snip ...

This is presumably the cause of the already an object named 'YINDICATOR_Domain' error because it tries to create the SqlRule twice - once for each Element.

If I DROP either of the columns using the YNINDICATOR UDT and re-export a Data Tier Application, the bacpac contains a single Element Type="SqlRule" node which contains both the column and UDT References:

Working bacpac - one column reference and a UDT reference -> one SqlRule element

... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
    <Property Name="ExpressionScript">
        <Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
    </Property>
    <Relationship Name="BoundTargets">
        <Entry>
            <References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[YNINDICATOR]" />
        </Entry>
    </Relationship>
    <Relationship Name="Schema">
        <Entry>
            <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
    </Relationship>
</Element>
... snip ...

and if I use sp_unbindrule to remove the binding the bacpac contains just a single Element - it no longer creates a second one for the UDT binding as it obviously doesn't exist any more:

Working bacpac - two column references and no UDT reference -> one SqlRule elements

<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
    <Property Name="ExpressionScript">
        <Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
    </Property>
    <Relationship Name="BoundTargets">
        <Entry>
            <References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
        </Entry>
    </Relationship>
    <Relationship Name="Schema">
        <Entry>
            <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
    </Relationship>
</Element>

I can't really use either of these options though as it doesn't match the actual database schema for the ProGet application, but it's interesting to note how the column and UDT binding get serialised depending on whether there's one or more-than-one column using the UDT.


Workaround

If I manually hack around with the model.xml in the original bacpac file and merge the two SqlRule Elements so there's only one that contains the columns and Rule binding I can import it fine and it creates all the schema objects without error:

This isn't very useful though as I don't want to have to keep doing this each time I migrate the database (I won't be doing it many times, but enough for this to be a painful workaround).

Working bacpac - two column references and a UDT reference hacked into one SqlRule element

<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
    <Property Name="ExpressionScript">
        <Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
    </Property>
    <Relationship Name="BoundTargets">
        <Entry>
            <References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[YNINDICATOR]" />
        </Entry>
    </Relationship>
    <Relationship Name="Schema">
        <Entry>
            <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
    </Relationship>
</Element>

Side note: if I then re-export this database it reverts back to the original form of 2 SqlRule elements - one that contains the columns and one that contains the rule binding.


Update

The problem also happens with the Az CLI:

PS> az version
{
  "azure-cli": "2.56.0",
  "azure-cli-core": "2.56.0",
  "azure-cli-telemetry": "1.1.0",
  "extensions": {
    "storage-preview": "1.0.0b1"
  }
}

PS> az sql db export `
  --subscription     "My Subscription" `
  --resource-group   "my-resource-group" `
  --server           "my-sql-server" `
  --name             "ProGet" `
  --admin-user       "my-admin-user" `
  --admin-password   "my-admin-password" `
  --storage-uri      "https://mystorageaccount.blob.core.windows.net/proget-db-backups/proget.bacpac" ``
  --storage-key-type "StorageAccessKey" `
  --storage-key      "my storage key"

Downloading the blob and unpacking it shows the problem with multiple SqlRule Elements.


Recap

If you've got this far then thanks for reading, and just to recap, my question in the "tl;dr" at the top was:

  • Part 1: Am I doing something wrong, or is this a known (or new) bug?
  • Part 2: If it's a bug, do you have any idea where I can report it?

Any help greatly appreciated.

1
  • RULE objects were a backwards-compatibility feature back in SQL Server 2000 and deprecated for about 20 years. I suspect a bug because since it's unlikely they've been well-tested in all the SQL tools. You can submit a bug here.
    – Dan Guzman
    Jan 11 at 23:00

1 Answer 1

0

tl;dr

Download and use the latest SqlPackage.exe command line utility instead of SSMS or az cli - it has a later version of the DacFx library which includes a fix for this specific issue.

References

So after a full day of digging, it looks like this is actually a known issue:

microsoft/DacFX

  • issue #245 - Publish DACPAC file failed due to bind rule with a * user-defined data type

When cx try to publish a DACPAC file to an existing databse (Azure SQL MI) that has bind rule with a user defined data type they will get error :SQL72018: SqlRule could not be imported but one or more of these objects exist in your source.

It's a different error code - SQL72018 rather than SQL72014, but exactly the same issue.

It also looks like it was fixed in this release:

162.0.52 SqlPackage

Fixes

  • Deployment - Fixes an issue where deployment would fail when the target database contains a rule bound to a column with a user-defined type. GitHub issue

I don't know how to check the exact version of the SqlPackage / DacFx components being used by SSMS, but I'm seeing the issue in SSMS version 19.3.4.0 which is the latest version as the time of writing.


Workaround

One workaround is to download and install the latest standalone SqlPackage tool and use that on the command-line instead:

Download and install SqlPackage

dotnet tool install -g microsoft.sqlpackage

On Windows this installs it to %USERPROFILE%\.dotnet\tools\sqlpackage.exe which is in the %PATH% variable (at least it is on my machine) so it can be invoked with something like this to export the bacpac:

PS> SqlPackage.exe -version
162.1.172.1

PS> SqlPackage.exe `
  /SourceConnectionString:"Server=(local);Database=ProGet;Trusted_Connection=True;TrustServerCertificate=True;" `
  /Action:Export `
  /TargetFile:"c:\temp\proget-sqlpackage.bacpac" `
  /OverwriteFiles:True

This version includes a fix for the GitHub issue, so it generates a valid backpac file with the schema in the original question:

model.xml

... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
    <Property Name="ExpressionScript">
        <Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
    </Property>
    <Relationship Name="BoundTargets">
        <Entry>
            <References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
        </Entry>
        <Entry>
            <References Name="[dbo].[YNINDICATOR]" />
        </Entry>
    </Relationship>
    <Relationship Name="Schema">
        <Entry>
            <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
    </Relationship>
</Element>
... snip ...

The column and UDT references are now all generated in a single <Element Type="SqlRule" ... node, which then imports successfully.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.