BUG: wrong update script for User-defined table types

zboziczbozic Posts: 4 New member
edited February 24, 2017 9:58AM in SQL Compare
Hi,
we notice one problem - when we change user-defined table type and than do compare with previous version, produced update script is wrong.

PROBLEM:
Sql Compare build script which will first try to drop user-defined table type, what will lead to error by execution, while user-defined table type still have dependency(s) in one or more stored procedures / functions.

SOLUTION (should be implemented):
It should first drop all dependend stored procedures/functions, then drop/create user defined table type, and finnaly create dropped stored procedures/functions again
Tagged:

Comments

  • Anu DAnu D Posts: 876 Silver 3
    Hi Zelimir,

    Thanks for your post!

    Can you confirm the detailed version you are using? Help --> About SQL Compare?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • zboziczbozic Posts: 4 New member
    Hi,
    sorry I forgot it,

    we use version - 12.1.6.3982 Professional

    bye
  • I am having this issue with 13.1.1.5299. It means we manually have to run upgrade scripts involving changes to UDTs.
  • I've been trying to raise another ticket but for some reason your system just stores it in my drafts. Anyway, this is what I wrote and the issue seems to be when a migration script needs to be run at the same time as a change to a UDT
    When sql compare is run and a migration script exists as well as a change to a User Defined Type that is used by a Stored Procedure that is also changed then the Upgrade script generated uses an Alter statement on the STored Procedure and does not drop the Stored procedure before trying to drop and create the user defined type. This will result in an error.

    this is using sql compare 13.1.1.5299

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Creating [RedGateLocal].[DeploymentMetadata] for migration script history'
    GO
    IF SCHEMA_ID(N'RedGateLocal') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [RedGateLocal] AUTHORIZATION [dbo]'
    GO
    IF OBJECT_ID(N'[RedGateLocal].[DeploymentMetadata]') IS NULL
    BEGIN
    CREATE TABLE [RedGateLocal].[DeploymentMetadata] (
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Name] NVARCHAR(max) NOT NULL,
    [Type] VARCHAR(50) NOT NULL,
    [Action] VARCHAR(50) NOT NULL,
    [By] NVARCHAR(128) NOT NULL DEFAULT ORIGINAL_LOGIN(),
    [As] NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
    [CompletedDate] DATETIME NOT NULL DEFAULT GETDATE(),
    [With] NVARCHAR(128) NOT NULL DEFAULT APP_NAME(),
    [BlockId] VARCHAR(50) NOT NULL,
    @DBTS + 1,
    [UpdatedSerial] TIMESTAMP NOT NULL,
    [MetadataVersion] VARCHAR(50) NOT NULL,
    [Hash] NVARCHAR(max) NULL
    )
    EXEC sp_addextendedproperty N'MS_Description', N'This table records deployments with migration scripts. Learn more: http://rd.gt/230GBP3', 'SCHEMA', N'RedGateLocal', 'TABLE', N'DeploymentMetadata', NULL, NULL
    END
    GO
    PRINT N'Executing: Changes before migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping [dbo].[TEST_TABLE]'
    GO
    IF OBJECT_ID(N'[dbo].[TEST_TABLE]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TEST_TABLE]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Finished executing: Changes before migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion])
    VALUES (N'Changes before migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.''', 'Compare', 'Deployed', '2017-11-02-160201 l1 auto', '5.63.0.193')
    GO
    PRINT N'Executing: Migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    /*
    This migration script replaces uncommitted changes made to these objects:
    TEST_TABLE

    Use this script to make necessary schema and data changes for these objects only. Schema changes to any other objects won't be deployed.

    Schema changes and migration scripts are deployed in the order they're committed.

    Migration scripts must not reference static data. When you deploy migration scripts alongside static data
    changes, the migration scripts will run first. This can cause the deployment to fail.
    Read more at https://documentation.red-gate.com/display/SOC6/Static+data+and+migrations.
    */

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    PRINT N'Creating [dbo].[TEST_TABLE]'
    GO
    CREATE TABLE [dbo].[TEST_TABLE]
    (
    [ID] [int] NOT NULL,
    [NAME] [nchar] (10) COLLATE Latin1_General_CI_AS NULL
    )
    GO
    INSERT INTO dbo.TEST_TABLE
    ( ID, NAME )
    VALUES ( 0, -- ID - int
    N'TEST' -- NAME - nchar(10)
    )
    GO
    PRINT N'Finished executing: Migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion])
    VALUES (N'Migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.''', 'Migration', 'Deployed', '2017-11-02-160201 l2 user', '5.63.0.193')
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NOT NULL
    DROP TYPE [dbo].[TradingParametersReferenceDataTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NOT NULL
    DROP TYPE [dbo].[VenueSecurityDetailsTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NULL
    CREATE TYPE [dbo].[VenueSecurityDetailsTableType] AS TABLE
    (
    [ID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL,
    [ISIN] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL,
    [SYMBOL] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL,
    [SECURITY_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [CURRENCY] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_INSTRUMENT_ID] [nvarchar] (36) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [EXCHANGE_MARKET_SIZE] [float] NULL,
    [MINIMUM_ORDER_SIZE] [float] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NULL
    CREATE TYPE [dbo].[TradingParametersReferenceDataTableType] AS TABLE
    (
    [ID] [nvarchar] (13) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL,
    [PRICE_TICK_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [SESSION_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [ALLOW_NAMED_ORDERS] [bit] NULL,
    [MAX_RANDOM_TIME] [int] NULL,
    [MAX_ORDER_DURATION] [int] NULL,
    [MIN_QUOTE_SIZE] [int] NULL,
    [MIN_AUCTION_VOL] [int] NULL,
    [QUOTE_TYPE] [int] NULL,
    [ALLOW_STOP_ORDERS] [bit] NULL,
    [MIN_DISCLOSED_SIZE] [int] NULL,
    [AESP] [int] NULL,
    [COMPANY_ID] [int] NULL,
    [COMPANY_TYPE] [int] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]', 'P') IS NOT NULL
    EXEC sp_executesql N'



    ALTER PROCEDURE [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]
    @venueSecurityDetailsTableType VenueSecurityDetailsTableType READONLY
    AS
    BEGIN

    MERGE INTO [VENUE_SECURITY_DETAILS] vsd
    USING @venueSecurityDetailsTableType AS details
    ON vsd.ID = details.ID
    WHEN MATCHED AND details.INSERT_ONLY_YN <> 1 AND details.DELETE_ROW_YN <> 1 THEN
    UPDATE SET vsd.LAST_UPDATE = GETDATE(),
    vsd.SECURITY_DETAILS_ID = COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx appends the venue security id with the exchange letter e.g. LLOY -> LLOYl
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY AND
    sd.SYMBOL = LEFT(details.VENUE_INSTRUMENT_ID, LEN(details.VENUE_INSTRUMENT_ID) - 1)
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    -- if we still can''t find it...
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    vsd.TRADING_PARAMETER_ID = details.TRADING_PARAMETER_ID,
    vsd.CURRENCY = details.CURRENCY,
    vsd.SECURITY_MIC =details.SECURITY_MIC,
    vsd.SYMBOL =details.SYMBOL,
    vsd.ISIN =details.ISIN,
    vsd.EXCHANGE_MARKET_SIZE = details.EXCHANGE_MARKET_SIZE,
    vsd.MINIMUM_ORDER_SIZE= details.MINIMUM_ORDER_SIZE

    WHEN MATCHED AND details.DELETE_ROW_YN = 1 THEN
    DELETE
    WHEN NOT MATCHED AND details.DELETE_ROW_YN <> 1 THEN
    INSERT( ID,
    SECURITY_DETAILS_ID,
    VENUE_INSTRUMENT_ID,
    VENUE_MIC,
    TRADING_PARAMETER_ID,
    LAST_UPDATE,
    CURRENCY,
    SECURITY_MIC,
    ISIN,
    SYMBOL,
    EXCHANGE_MARKET_SIZE,
    MINIMUM_ORDER_SIZE)
    VALUES( details.ID,
    COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    details.VENUE_INSTRUMENT_ID,
    details.VENUE_MIC,
    details.TRADING_PARAMETER_ID,
    GETDATE(),
    details.CURRENCY,
    details.SECURITY_MIC,
    details.ISIN,
    details.SYMBOL,
    details.EXCHANGE_MARKET_SIZE,
    details.MINIMUM_ORDER_SIZE
    );

    RETURN 0

    END


    '
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[TradingParametersReferenceDataTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[TradingParametersReferenceDataTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[VenueSecurityDetailsTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[VenueSecurityDetailsTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    COMMIT TRANSACTION
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    -- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
    IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
    BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
    END
    GO
    DECLARE @Success AS BIT
    SET @Success = 1
    SET NOEXEC OFF
    IF (@Success = 1) PRINT 'The database update succeeded'
    ELSE BEGIN
    @TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
    END
    GO


    If there is no migration script to be run then the upgrade script correctly generates a drop stored proc followed by the drop /create user defined type and then create the original stored proc.

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    BEGIN TRANSACTION
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NOT NULL
    DROP TYPE [dbo].[TradingParametersReferenceDataTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NOT NULL
    DROP TYPE [dbo].[VenueSecurityDetailsTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NULL
    CREATE TYPE [dbo].[VenueSecurityDetailsTableType] AS TABLE
    (
    [ID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL,
    [ISIN] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL,
    [SYMBOL] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL,
    [SECURITY_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [CURRENCY] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_INSTRUMENT_ID] [nvarchar] (36) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [EXCHANGE_MARKET_SIZE] [float] NULL,
    [MINIMUM_ORDER_SIZE] [float] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NULL
    CREATE TYPE [dbo].[TradingParametersReferenceDataTableType] AS TABLE
    (
    [ID] [nvarchar] (13) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL,
    [PRICE_TICK_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [SESSION_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [ALLOW_NAMED_ORDERS] [bit] NULL,
    [MAX_RANDOM_TIME] [int] NULL,
    [MAX_ORDER_DURATION] [int] NULL,
    [MIN_QUOTE_SIZE] [int] NULL,
    [MIN_AUCTION_VOL] [int] NULL,
    [QUOTE_TYPE] [int] NULL,
    [ALLOW_STOP_ORDERS] [bit] NULL,
    [MIN_DISCLOSED_SIZE] [int] NULL,
    [AESP] [int] NULL,
    [COMPANY_ID] [int] NULL,
    [COMPANY_TYPE] [int] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]', 'P') IS NULL
    EXEC sp_executesql N'
    CREATE PROCEDURE [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]
    @tradingParametersReferenceDataTableType TradingParametersReferenceDataTableType READONLY
    AS
    BEGIN

    MERGE INTO dbo.TRADING_PARAMETERS_REFERENCE_DATA pd
    USING @tradingParametersReferenceDataTableType AS details
    ON pd.ID = details.ID
    WHEN MATCHED AND
    details.INSERT_ONLY_YN <> 1 AND
    details.DELETE_ROW_YN <> 1 THEN
    UPDATE SET
    pd.PRICE_TICK_ID = details.PRICE_TICK_ID ,
    pd.SESSION_PARAMETER_ID= details.SESSION_PARAMETER_ID ,
    pd.ALLOW_NAMED_ORDERS= details.ALLOW_NAMED_ORDERS ,
    pd.MAX_RANDOM_TIME= details.MAX_RANDOM_TIME ,
    pd.MAX_ORDER_DURATION= details.MAX_ORDER_DURATION ,
    pd.MIN_QUOTE_SIZE= details.MIN_QUOTE_SIZE ,
    pd.MIN_AUCTION_VOL= details.MIN_AUCTION_VOL ,
    pd.QUOTE_TYPE= details.QUOTE_TYPE ,
    pd.ALLOW_STOP_ORDERS= details.ALLOW_STOP_ORDERS ,
    pd.MIN_DISCLOSED_SIZE= details.MIN_DISCLOSED_SIZE ,
    pd.AESP= details.AESP ,
    pd.COMPANY_ID= details.COMPANY_ID ,
    pd.COMPANY_TYPE= details.COMPANY_TYPE ,
    pd.TRADING_PARAMETER_ID= details.TRADING_PARAMETER_ID ,
    pd.LAST_UPDATE = GETDATE()
    WHEN MATCHED AND details.DELETE_ROW_YN = 1 THEN
    DELETE
    WHEN NOT MATCHED AND details.DELETE_ROW_YN <> 1 THEN
    INSERT( ID,
    PRICE_TICK_ID,
    TRADING_PARAMETER_ID,
    SESSION_PARAMETER_ID,
    ALLOW_NAMED_ORDERS,
    MAX_RANDOM_TIME,
    MAX_ORDER_DURATION,
    MIN_QUOTE_SIZE,
    MIN_AUCTION_VOL,
    QUOTE_TYPE,
    ALLOW_STOP_ORDERS,
    MIN_DISCLOSED_SIZE,
    AESP,
    COMPANY_ID,
    COMPANY_TYPE,
    LAST_UPDATE)
    VALUES( details.ID,
    details.PRICE_TICK_ID,
    details.TRADING_PARAMETER_ID,
    details.SESSION_PARAMETER_ID,
    details.ALLOW_NAMED_ORDERS,
    details.MAX_RANDOM_TIME,
    details.MAX_ORDER_DURATION,
    details.MIN_QUOTE_SIZE,
    details.MIN_AUCTION_VOL,
    details.QUOTE_TYPE,
    details.ALLOW_STOP_ORDERS,
    details.MIN_DISCLOSED_SIZE,
    details.AESP,
    details.COMPANY_ID,
    details.COMPANY_TYPE,
    GETDATE() );

    RETURN 0
    END

    '
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]', 'P') IS NULL
    EXEC sp_executesql N'



    CREATE PROCEDURE [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]
    @venueSecurityDetailsTableType VenueSecurityDetailsTableType READONLY
    AS
    BEGIN

    MERGE INTO [VENUE_SECURITY_DETAILS] vsd
    USING @venueSecurityDetailsTableType AS details
    ON vsd.ID = details.ID
    WHEN MATCHED AND details.INSERT_ONLY_YN <> 1 AND details.DELETE_ROW_YN <> 1 THEN
    UPDATE SET vsd.LAST_UPDATE = GETDATE(),
    vsd.SECURITY_DETAILS_ID = COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx appends the venue security id with the exchange letter e.g. LLOY -> LLOYl
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY AND
    sd.SYMBOL = LEFT(details.VENUE_INSTRUMENT_ID, LEN(details.VENUE_INSTRUMENT_ID) - 1)
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    -- if we still can''t find it...
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    vsd.TRADING_PARAMETER_ID = details.TRADING_PARAMETER_ID,
    vsd.CURRENCY = details.CURRENCY,
    vsd.SECURITY_MIC =details.SECURITY_MIC,
    vsd.SYMBOL =details.SYMBOL,
    vsd.ISIN =details.ISIN,
    vsd.EXCHANGE_MARKET_SIZE = details.EXCHANGE_MARKET_SIZE,
    vsd.MINIMUM_ORDER_SIZE= details.MINIMUM_ORDER_SIZE

    WHEN MATCHED AND details.DELETE_ROW_YN = 1 THEN
    DELETE
    WHEN NOT MATCHED AND details.DELETE_ROW_YN <> 1 THEN
    INSERT( ID,
    SECURITY_DETAILS_ID,
    VENUE_INSTRUMENT_ID,
    VENUE_MIC,
    TRADING_PARAMETER_ID,
    LAST_UPDATE,
    CURRENCY,
    SECURITY_MIC,
    ISIN,
    SYMBOL,
    EXCHANGE_MARKET_SIZE,
    MINIMUM_ORDER_SIZE)
    VALUES( details.ID,
    COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    details.VENUE_INSTRUMENT_ID,
    details.VENUE_MIC,
    details.TRADING_PARAMETER_ID,
    GETDATE(),
    details.CURRENCY,
    details.SECURITY_MIC,
    details.ISIN,
    details.SYMBOL,
    details.EXCHANGE_MARKET_SIZE,
    details.MINIMUM_ORDER_SIZE
    );

    RETURN 0

    END


    '
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]'
    GO
    GRANT EXECUTE ON [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[TradingParametersReferenceDataTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[TradingParametersReferenceDataTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    GRANT EXECUTE ON [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[VenueSecurityDetailsTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[VenueSecurityDetailsTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    COMMIT TRANSACTION
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    -- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
    IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
    BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
    END
    GO
    DECLARE @Success AS BIT
    SET @Success = 1
    SET NOEXEC OFF
    IF (@Success = 1) PRINT 'The database update succeeded'
    ELSE BEGIN
    @TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
    END
    GO
  • I've been trying to raise another ticket but for some reason your system just stores it in my drafts. Anyway, this is what I wrote and the issue seems to be when a migration script needs to be run at the same time as a change to a UDT
    When sql compare is run and a migration script exists as well as a change to a User Defined Type that is used by a Stored Procedure that is also changed then the Upgrade script generated uses an Alter statement on the STored Procedure and does not drop the Stored procedure before trying to drop and create the user defined type. This will result in an error.

    this is using sql compare 13.1.1.5299

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Creating [RedGateLocal].[DeploymentMetadata] for migration script history'
    GO
    IF SCHEMA_ID(N'RedGateLocal') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [RedGateLocal] AUTHORIZATION [dbo]'
    GO
    IF OBJECT_ID(N'[RedGateLocal].[DeploymentMetadata]') IS NULL
    BEGIN
    CREATE TABLE [RedGateLocal].[DeploymentMetadata] (
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Name] NVARCHAR(max) NOT NULL,
    [Type] VARCHAR(50) NOT NULL,
    [Action] VARCHAR(50) NOT NULL,
    [By] NVARCHAR(128) NOT NULL DEFAULT ORIGINAL_LOGIN(),
    [As] NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
    [CompletedDate] DATETIME NOT NULL DEFAULT GETDATE(),
    [With] NVARCHAR(128) NOT NULL DEFAULT APP_NAME(),
    [BlockId] VARCHAR(50) NOT NULL,
    @DBTS + 1,
    [UpdatedSerial] TIMESTAMP NOT NULL,
    [MetadataVersion] VARCHAR(50) NOT NULL,
    [Hash] NVARCHAR(max) NULL
    )
    EXEC sp_addextendedproperty N'MS_Description', N'This table records deployments with migration scripts. Learn more: http://rd.gt/230GBP3', 'SCHEMA', N'RedGateLocal', 'TABLE', N'DeploymentMetadata', NULL, NULL
    END
    GO
    PRINT N'Executing: Changes before migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping [dbo].[TEST_TABLE]'
    GO
    IF OBJECT_ID(N'[dbo].[TEST_TABLE]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TEST_TABLE]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Finished executing: Changes before migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion])
    VALUES (N'Changes before migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.''', 'Compare', 'Deployed', '2017-11-02-160201 l1 auto', '5.63.0.193')
    GO
    PRINT N'Executing: Migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    /*
    This migration script replaces uncommitted changes made to these objects:
    TEST_TABLE

    Use this script to make necessary schema and data changes for these objects only. Schema changes to any other objects won't be deployed.

    Schema changes and migration scripts are deployed in the order they're committed.

    Migration scripts must not reference static data. When you deploy migration scripts alongside static data
    changes, the migration scripts will run first. This can cause the deployment to fail.
    Read more at https://documentation.red-gate.com/display/SOC6/Static+data+and+migrations.
    */

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    PRINT N'Creating [dbo].[TEST_TABLE]'
    GO
    CREATE TABLE [dbo].[TEST_TABLE]
    (
    [ID] [int] NOT NULL,
    [NAME] [nchar] (10) COLLATE Latin1_General_CI_AS NULL
    )
    GO
    INSERT INTO dbo.TEST_TABLE
    ( ID, NAME )
    VALUES ( 0, -- ID - int
    N'TEST' -- NAME - nchar(10)
    )
    GO
    PRINT N'Finished executing: Migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.'''
    GO
    INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion])
    VALUES (N'Migration script ''Covers changes to: TEST_TABLE. Script created at 2017-11-02 16:01.''', 'Migration', 'Deployed', '2017-11-02-160201 l2 user', '5.63.0.193')
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NOT NULL
    DROP TYPE [dbo].[TradingParametersReferenceDataTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NOT NULL
    DROP TYPE [dbo].[VenueSecurityDetailsTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NULL
    CREATE TYPE [dbo].[VenueSecurityDetailsTableType] AS TABLE
    (
    [ID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL,
    [ISIN] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL,
    [SYMBOL] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL,
    [SECURITY_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [CURRENCY] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_INSTRUMENT_ID] [nvarchar] (36) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [EXCHANGE_MARKET_SIZE] [float] NULL,
    [MINIMUM_ORDER_SIZE] [float] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NULL
    CREATE TYPE [dbo].[TradingParametersReferenceDataTableType] AS TABLE
    (
    [ID] [nvarchar] (13) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL,
    [PRICE_TICK_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [SESSION_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [ALLOW_NAMED_ORDERS] [bit] NULL,
    [MAX_RANDOM_TIME] [int] NULL,
    [MAX_ORDER_DURATION] [int] NULL,
    [MIN_QUOTE_SIZE] [int] NULL,
    [MIN_AUCTION_VOL] [int] NULL,
    [QUOTE_TYPE] [int] NULL,
    [ALLOW_STOP_ORDERS] [bit] NULL,
    [MIN_DISCLOSED_SIZE] [int] NULL,
    [AESP] [int] NULL,
    [COMPANY_ID] [int] NULL,
    [COMPANY_TYPE] [int] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]', 'P') IS NOT NULL
    EXEC sp_executesql N'



    ALTER PROCEDURE [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]
    @venueSecurityDetailsTableType VenueSecurityDetailsTableType READONLY
    AS
    BEGIN

    MERGE INTO [VENUE_SECURITY_DETAILS] vsd
    USING @venueSecurityDetailsTableType AS details
    ON vsd.ID = details.ID
    WHEN MATCHED AND details.INSERT_ONLY_YN <> 1 AND details.DELETE_ROW_YN <> 1 THEN
    UPDATE SET vsd.LAST_UPDATE = GETDATE(),
    vsd.SECURITY_DETAILS_ID = COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx appends the venue security id with the exchange letter e.g. LLOY -> LLOYl
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY AND
    sd.SYMBOL = LEFT(details.VENUE_INSTRUMENT_ID, LEN(details.VENUE_INSTRUMENT_ID) - 1)
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    -- if we still can''t find it...
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    vsd.TRADING_PARAMETER_ID = details.TRADING_PARAMETER_ID,
    vsd.CURRENCY = details.CURRENCY,
    vsd.SECURITY_MIC =details.SECURITY_MIC,
    vsd.SYMBOL =details.SYMBOL,
    vsd.ISIN =details.ISIN,
    vsd.EXCHANGE_MARKET_SIZE = details.EXCHANGE_MARKET_SIZE,
    vsd.MINIMUM_ORDER_SIZE= details.MINIMUM_ORDER_SIZE

    WHEN MATCHED AND details.DELETE_ROW_YN = 1 THEN
    DELETE
    WHEN NOT MATCHED AND details.DELETE_ROW_YN <> 1 THEN
    INSERT( ID,
    SECURITY_DETAILS_ID,
    VENUE_INSTRUMENT_ID,
    VENUE_MIC,
    TRADING_PARAMETER_ID,
    LAST_UPDATE,
    CURRENCY,
    SECURITY_MIC,
    ISIN,
    SYMBOL,
    EXCHANGE_MARKET_SIZE,
    MINIMUM_ORDER_SIZE)
    VALUES( details.ID,
    COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    details.VENUE_INSTRUMENT_ID,
    details.VENUE_MIC,
    details.TRADING_PARAMETER_ID,
    GETDATE(),
    details.CURRENCY,
    details.SECURITY_MIC,
    details.ISIN,
    details.SYMBOL,
    details.EXCHANGE_MARKET_SIZE,
    details.MINIMUM_ORDER_SIZE
    );

    RETURN 0

    END


    '
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[TradingParametersReferenceDataTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[TradingParametersReferenceDataTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[VenueSecurityDetailsTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[VenueSecurityDetailsTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    COMMIT TRANSACTION
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    -- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
    IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
    BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
    END
    GO
    DECLARE @Success AS BIT
    SET @Success = 1
    SET NOEXEC OFF
    IF (@Success = 1) PRINT 'The database update succeeded'
    ELSE BEGIN
    @TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
    END
    GO


    If there is no migration script to be run then the upgrade script correctly generates a drop stored proc followed by the drop /create user defined type and then create the original stored proc.

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    BEGIN TRANSACTION
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Dropping types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NOT NULL
    DROP TYPE [dbo].[TradingParametersReferenceDataTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NOT NULL
    DROP TYPE [dbo].[VenueSecurityDetailsTableType]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating types'
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[VenueSecurityDetailsTableType]') IS NULL
    CREATE TYPE [dbo].[VenueSecurityDetailsTableType] AS TABLE
    (
    [ID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL,
    [ISIN] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL,
    [SYMBOL] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL,
    [SECURITY_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_MIC] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL,
    [CURRENCY] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL,
    [VENUE_INSTRUMENT_ID] [nvarchar] (36) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [EXCHANGE_MARKET_SIZE] [float] NULL,
    [MINIMUM_ORDER_SIZE] [float] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    IF TYPE_ID(N'[dbo].[TradingParametersReferenceDataTableType]') IS NULL
    CREATE TYPE [dbo].[TradingParametersReferenceDataTableType] AS TABLE
    (
    [ID] [nvarchar] (13) COLLATE Latin1_General_CI_AS NULL,
    [TRADING_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL,
    [PRICE_TICK_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [SESSION_PARAMETER_ID] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL,
    [ALLOW_NAMED_ORDERS] [bit] NULL,
    [MAX_RANDOM_TIME] [int] NULL,
    [MAX_ORDER_DURATION] [int] NULL,
    [MIN_QUOTE_SIZE] [int] NULL,
    [MIN_AUCTION_VOL] [int] NULL,
    [QUOTE_TYPE] [int] NULL,
    [ALLOW_STOP_ORDERS] [bit] NULL,
    [MIN_DISCLOSED_SIZE] [int] NULL,
    [AESP] [int] NULL,
    [COMPANY_ID] [int] NULL,
    [COMPANY_TYPE] [int] NULL,
    [SOURCE_TYPE] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL,
    [SOURCE_TYPE_PRIORITY] [int] NULL,
    [INSERT_ONLY_YN] [bit] NULL,
    [DELETE_ROW_YN] [bit] NULL
    )
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]', 'P') IS NULL
    EXEC sp_executesql N'
    CREATE PROCEDURE [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]
    @tradingParametersReferenceDataTableType TradingParametersReferenceDataTableType READONLY
    AS
    BEGIN

    MERGE INTO dbo.TRADING_PARAMETERS_REFERENCE_DATA pd
    USING @tradingParametersReferenceDataTableType AS details
    ON pd.ID = details.ID
    WHEN MATCHED AND
    details.INSERT_ONLY_YN <> 1 AND
    details.DELETE_ROW_YN <> 1 THEN
    UPDATE SET
    pd.PRICE_TICK_ID = details.PRICE_TICK_ID ,
    pd.SESSION_PARAMETER_ID= details.SESSION_PARAMETER_ID ,
    pd.ALLOW_NAMED_ORDERS= details.ALLOW_NAMED_ORDERS ,
    pd.MAX_RANDOM_TIME= details.MAX_RANDOM_TIME ,
    pd.MAX_ORDER_DURATION= details.MAX_ORDER_DURATION ,
    pd.MIN_QUOTE_SIZE= details.MIN_QUOTE_SIZE ,
    pd.MIN_AUCTION_VOL= details.MIN_AUCTION_VOL ,
    pd.QUOTE_TYPE= details.QUOTE_TYPE ,
    pd.ALLOW_STOP_ORDERS= details.ALLOW_STOP_ORDERS ,
    pd.MIN_DISCLOSED_SIZE= details.MIN_DISCLOSED_SIZE ,
    pd.AESP= details.AESP ,
    pd.COMPANY_ID= details.COMPANY_ID ,
    pd.COMPANY_TYPE= details.COMPANY_TYPE ,
    pd.TRADING_PARAMETER_ID= details.TRADING_PARAMETER_ID ,
    pd.LAST_UPDATE = GETDATE()
    WHEN MATCHED AND details.DELETE_ROW_YN = 1 THEN
    DELETE
    WHEN NOT MATCHED AND details.DELETE_ROW_YN <> 1 THEN
    INSERT( ID,
    PRICE_TICK_ID,
    TRADING_PARAMETER_ID,
    SESSION_PARAMETER_ID,
    ALLOW_NAMED_ORDERS,
    MAX_RANDOM_TIME,
    MAX_ORDER_DURATION,
    MIN_QUOTE_SIZE,
    MIN_AUCTION_VOL,
    QUOTE_TYPE,
    ALLOW_STOP_ORDERS,
    MIN_DISCLOSED_SIZE,
    AESP,
    COMPANY_ID,
    COMPANY_TYPE,
    LAST_UPDATE)
    VALUES( details.ID,
    details.PRICE_TICK_ID,
    details.TRADING_PARAMETER_ID,
    details.SESSION_PARAMETER_ID,
    details.ALLOW_NAMED_ORDERS,
    details.MAX_RANDOM_TIME,
    details.MAX_ORDER_DURATION,
    details.MIN_QUOTE_SIZE,
    details.MIN_AUCTION_VOL,
    details.QUOTE_TYPE,
    details.ALLOW_STOP_ORDERS,
    details.MIN_DISCLOSED_SIZE,
    details.AESP,
    details.COMPANY_ID,
    details.COMPANY_TYPE,
    GETDATE() );

    RETURN 0
    END

    '
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Creating [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    IF OBJECT_ID(N'[dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]', 'P') IS NULL
    EXEC sp_executesql N'



    CREATE PROCEDURE [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]
    @venueSecurityDetailsTableType VenueSecurityDetailsTableType READONLY
    AS
    BEGIN

    MERGE INTO [VENUE_SECURITY_DETAILS] vsd
    USING @venueSecurityDetailsTableType AS details
    ON vsd.ID = details.ID
    WHEN MATCHED AND details.INSERT_ONLY_YN <> 1 AND details.DELETE_ROW_YN <> 1 THEN
    UPDATE SET vsd.LAST_UPDATE = GETDATE(),
    vsd.SECURITY_DETAILS_ID = COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx appends the venue security id with the exchange letter e.g. LLOY -> LLOYl
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY AND
    sd.SYMBOL = LEFT(details.VENUE_INSTRUMENT_ID, LEN(details.VENUE_INSTRUMENT_ID) - 1)
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    -- if we still can''t find it...
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    vsd.TRADING_PARAMETER_ID = details.TRADING_PARAMETER_ID,
    vsd.CURRENCY = details.CURRENCY,
    vsd.SECURITY_MIC =details.SECURITY_MIC,
    vsd.SYMBOL =details.SYMBOL,
    vsd.ISIN =details.ISIN,
    vsd.EXCHANGE_MARKET_SIZE = details.EXCHANGE_MARKET_SIZE,
    vsd.MINIMUM_ORDER_SIZE= details.MINIMUM_ORDER_SIZE

    WHEN MATCHED AND details.DELETE_ROW_YN = 1 THEN
    DELETE
    WHEN NOT MATCHED AND details.DELETE_ROW_YN <> 1 THEN
    INSERT( ID,
    SECURITY_DETAILS_ID,
    VENUE_INSTRUMENT_ID,
    VENUE_MIC,
    TRADING_PARAMETER_ID,
    LAST_UPDATE,
    CURRENCY,
    SECURITY_MIC,
    ISIN,
    SYMBOL,
    EXCHANGE_MARKET_SIZE,
    MINIMUM_ORDER_SIZE)
    VALUES( details.ID,
    COALESCE(
    --best case will match security details id exactly
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --Trqx chops the symbol - nice one e.g. DETNOR -> DENTO so match by execution venue
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN AND
    sd.CURRENCY = details.CURRENCY
    ORDER BY SOURCE_TYPE_PRIORITY ASC),
    --GBX entered as GBP in Security details tab, match as long as everything else is good.
    (SELECT TOP 1 sd.ID
    FROM dbo.SECURITY_DETAILS sd
    WHERE sd.SYMBOL = details.SYMBOL AND
    sd.VENUE_OF_EXECUTION = details.SECURITY_MIC AND
    sd.ISIN = details.ISIN
    ORDER BY SOURCE_TYPE_PRIORITY ASC)
    ),
    details.VENUE_INSTRUMENT_ID,
    details.VENUE_MIC,
    details.TRADING_PARAMETER_ID,
    GETDATE(),
    details.CURRENCY,
    details.SECURITY_MIC,
    details.ISIN,
    details.SYMBOL,
    details.EXCHANGE_MARKET_SIZE,
    details.MINIMUM_ORDER_SIZE
    );

    RETURN 0

    END


    '
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA]'
    GO
    GRANT EXECUTE ON [dbo].[BULK_UPDATE_TRADING_PARAMETERS_REFERENCE_DATA] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[TradingParametersReferenceDataTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[TradingParametersReferenceDataTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS]'
    GO
    GRANT EXECUTE ON [dbo].[BULK_UPDATE_VENUE_SECURITY_DETAILS] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    PRINT N'Altering permissions on TYPE:: [dbo].[VenueSecurityDetailsTableType]'
    GO
    GRANT EXECUTE ON TYPE:: [dbo].[VenueSecurityDetailsTableType] TO [proquote]
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    COMMIT TRANSACTION
    GO
    @ERROR <> 0 SET NOEXEC ON
    GO
    -- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
    IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
    BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
    END
    GO
    DECLARE @Success AS BIT
    SET @Success = 1
    SET NOEXEC OFF
    IF (@Success = 1) PRINT 'The database update succeeded'
    ELSE BEGIN
    @TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
    END
    GO
  • Hi @HuwJenkins ,

    I'm sorry if you didn't receive my email.

    With the samples you supplied, we have reproduced the issue here locally and logged as SC-9812 in our internal bug tracking system.

    Please keep an eye on the further release note.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • rlzrlz Posts: 1 New member
    edited August 22, 2018 7:40PM
    Hi @Tianjiao_Li,
    Do you have any updates on resolving this bug you referenced as SC-9812 in your internal bug tracking system?
    Thank you!

    Later edit: please ignore this question. The bug is no longer there, I just needed to check the "Deploy all Dependencies" box.   %‑)
Sign In or Register to comment.