BUG: wrong update script for User-defined table types
zbozic
Posts: 4 New member
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
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
Thanks for your post!
Can you confirm the detailed version you are using? Help --> About SQL Compare?
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
sorry I forgot it,
we use version - 12.1.6.3982 Professional
bye
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
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'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.
Tianjiao Li | Redgate Software
Have you visited our Help Center?
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.