BUG: Migration Scripts, User Defined Types and Stored procedures
HuwJenkins
Posts: 6 Bronze 1
in SQL Compare
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
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
