How can we help you today? How can we help you today?
HuwJenkins
Activity overview Badges (0) Posts (1) Comments (4)

Comments

Sort by recent activity
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 / comments
  1. Community
  2. SQL Compare
  3. BUG: wrong update script for User-defined table types
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 ...
0 votes
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 / comments
  1. Community
  2. SQL Compare
  3. BUG: wrong update script for User-defined table types
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 ...
0 votes
I am having this issue with 13.1.1.5299. It means we manually have to run upgrade scripts involving changes to UDTs. / comments
  1. Community
  2. SQL Compare
  3. BUG: wrong update script for User-defined table types
I am having this issue with 13.1.1.5299. It means we manually have to run upgrade scripts involving changes to UDTs.
0 votes
I switched to just using basic transactional replication with our secondary location just acting as a backup and in this configuration sqlcompare works. It appears that it's only when using peer-to-peer that this problem arises. The column mentioned in the error "$sys_p2p_cd_id" is used by peer-to-peer replication for conflict resolution. So I don't think sql compare should be having anything to do with this column. It is however a big issue for us as we do want to use P2P replication and we should at least be able to generate an upgrade script whilst replication is running which we can't at the moment by the looks of things. cheers, Huw / comments
  1. Community
  2. SQL Compare 11
  3. SqlException: Invalid pseudocolumn on synchronisation
I switched to just using basic transactional replication with our secondary location just acting as a backup and in this configuration sqlcompare works. It appears that it's only when using peer-to...
0 votes
Total activity
7
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
2