User Defined Types with dependencies

AbeAxiomatic · October 20, 2020 2:59PM

I'm looking for a way to control the order in which User Defined Types are executed on an initial deployment to a blank database for my developers. By default, they all seem to appear in alphabetical order. The issue I have is that I have a table type of name CaseInvoices that contains a column for the type DelinquentCaseNumber. Clearly, DelinquentCaseNumber needs to be created first, but the "get latest" doesn't appear to recognize this dependency

DanC · October 22, 2020 11:51AM

Hi @AbeAxiomatic

So, unfortunately, it's not possible to change the deployment order within SQL Source Control, the tool should however detect dependencies and script out the objects in the correct order.

Could I please ask what version of SQL Source Control you're using?

AbeAxiomatic · October 22, 2020 12:16PM

Hi, Dan

Thanks for the quick response! I'm currently using SQL Source Control (not Change Automation) Version 7.2.4.11418. I'll paste my CREATE scripts below for context. The scenario where this comes up is in getting a new employee set up with a blank database, and using Source Control to deploy all database objects and static data.

PRINT N'Creating types'

GO

CREATE TYPE [dbo].[CaseInvoices] AS TABLE

(

[CaseNumber] [dbo].[DelinquentCaseNumber] NULL,

[EntryDetail] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[InvoiceAmount] [decimal] (18, 2) NULL

)

GO

@ERROR <> 0 SET NOEXEC ON

GO

CREATE TYPE [dbo].[DelinquentCaseNumber] FROM int NOT NULL

GO

@ERROR <> 0 SET NOEXEC ON

GO

CREATE TYPE [dbo].[CasePayments] AS TABLE

(

[CaseNumber] [dbo].[DelinquentCaseNumber] NULL,

[PaymentAmount] [decimal] (18, 2) NULL

)

GO

DanC · October 30, 2020 2:39PM

Hi @AbeAxiomatic

Apologies, I didn't get an email that you replied to this forum post!

I'm going to chase up from a support ticket as I'll need some more details!

User Defined Types with dependencies

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk