SQL72014 error importing bacpac with multiple columns using a UDT bound to a rule

#### 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

* Binds it to a rule that restricts the values to

and

* Creates two tables that each have a column of type

(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: > **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

UDT, the

.xml

file inside the bacpac contains something like this, with **two**

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 ...

	
		
	
	
		
			
		
		
			
		
	
	
		
			
		
	


	
		
	
	
		
			
		
	
	
		
			
		
	

... snip ...

This is presumably the cause of the

an object named 'YINDICATOR_Domain'

error because it tries to create the SqlRule twice - once for each

. If I **DROP** either of the columns using the

UDT and re-export a Data Tier Application, the bacpac contains a single

Type="SqlRule"

node which contains both the column *and* UDT References: **Working bacpac - one column reference and a UDT reference -> one SqlRule element**

... snip ...

	
		
	
	
		
			
		
		
			
		
	
	
		
			
		
	

... snip ...

and if I use

to remove the binding the bacpac contains just a single

- 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**

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

.xml

in the original bacpac file and merge the two SqlRule

s 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**

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

s. --- ### 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.