User profile for Ivailo

4. Compare again. Should see [AddedColumn] as a new column that have to be transferred. Everything is correct. Deploy changes with SQL Compare. Everything should be transferred, but... 5. Compare again. Unfortunately PERIOD and HIDDEN attributes are missing in DB2. OK, deploy again with SQL Compare. 6. Compare again. Should see PERIOD is transferred but HIDDEN attribute is missing. Ok, deploy again with SQL Compare. Now you should see the error: Failed to run SQL: Period column 'sysValidFrom' in a system-versioned temporal table cannot be altered. Error executing the following SQL: ALTER TABLE [dbo].[DemoTable] ALTER COLUMN [sysValidFrom] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL I think the correct command should be: ALTER TABLE [dbo].[DemoTable] ALTER COLUMN sysValidFrom ADD HIDDEN But, I prefer all changes to be deployed at once instead of 3 steps. / comments

4. Compare again. Should see [AddedColumn] as a new column that have to be transferred.Everything is correct.Deploy changes with SQL Compare.Everything should be transferred, but...5. Compare again...

0 votes

2. Transfer the table to DB2 with SQL Compare. Everything should be fine. 3. Apply some changes to the table. For example with SQL Management Studio insert a new column [AddedColumn] between [Description] and [sysValidFrom] in DB1.dbo.DemoTable and fix PERIOD and HIDDEN manually. Like the following simplified script: ALTER TABLE dbo.DemoTable DROP CONSTRAINT DF_dbo_DemoTable_sysValidFrom, CONSTRAINT DF_dbo_DemoTable_sysValidTo, CONSTRAINT PK_DemoTable CREATE TABLE dbo.Tmp_DemoTable ( Id int NOT NULL IDENTITY (1, 1) CONSTRAINT PK_DemoTable PRIMARY KEY CLUSTERED ( Id ), Description nvarchar(50) NOT NULL, AddedColumn int NOT NULL, sysValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF_dbo_DemoTable_sysValidFrom] DEFAULT SYSUTCDATETIME(), sysValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF_dbo_DemoTable_sysValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999'), PERIOD FOR SYSTEM_TIME (sysValidFrom, sysValidTo) ) DROP TABLE dbo.DemoTable EXECUTE sp_rename N'dbo.Tmp_DemoTable', N'DemoTable', 'OBJECT' / comments

2. Transfer the table to DB2 with SQL Compare. Everything should be fine.3. Apply some changes to the table. For example with SQL Management Studio insert a new column [AddedColumn] between [Descri...

0 votes

Hello, Ok, I will try to explain how to reproduce the issue in more detail with an example: (excuse me, but I will post step by step to avoid problems) create a table in DB1 with the following script: CREATE TABLE [dbo].[DemoTable]( [Id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PK_DemoTable PRIMARY KEY CLUSTERED (Id), [Description] [nvarchar](50) NOT NULL, sysValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF_dbo_DemoTable_sysValidFrom] DEFAULT SYSUTCDATETIME(), sysValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF_dbo_DemoTable_sysValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999'), PERIOD FOR SYSTEM_TIME (sysValidFrom, sysValidTo) ) GO / comments

Hello,Ok, I will try to explain how to reproduce the issue in more detail with an example:(excuse me, but I will post step by step to avoid problems)create a table in DB1 with the following script:...

0 votes

Oh, I am sorry. I tried to edit the post and now it is missing. I will try to repost tomorrow. / comments

Oh, I am sorry. I tried to edit the post and now it is missing.I will try to repost tomorrow.

0 votes

Excuse me, but may be something is wrong in the previous post. I entered 10 points in the "Ordered list" and in "Edit" mode I can see 10 points, but in "Preview" mode I can see only 8 points and some scripts are missing. I am not sure how to fix this. / comments

Excuse me, but may be something is wrong in the previous post. I entered 10 points in the "Ordered list" and in "Edit" mode I can see 10 points, but in "Preview" mode I can see only 8 points and so...

0 votes

Hello, Ok, I will try to explain how to reproduce the issue in more detail with an example: create a table in DB1 with the following script: ALTER TABLE dbo.DemoTable DROP CONSTRAINT DF_dbo_DemoTable_sysValidFrom ALTER TABLE dbo.DemoTable DROP CONSTRAINT DF_dbo_DemoTable_sysValidTo CREATE TABLE dbo.Tmp_DemoTable ( Id int NOT NULL IDENTITY (1, 1), Description nvarchar(50) NOT NULL, AddedColumn int NOT NULL, sysValidFrom datetime2(7) NOT NULL, sysValidTo datetime2(7) NOT NULL ) ALTER TABLE dbo.Tmp_DemoTable ADD CONSTRAINT DF_dbo_DemoTable_sysValidFrom DEFAULT (sysutcdatetime()) FOR sysValidFrom ALTER TABLE dbo.Tmp_DemoTable ADD CONSTRAINT DF_dbo_DemoTable_sysValidTo DEFAULT (CONVERT([datetime2],'9999-12-31 23:59:59.99999999')) FOR sysValidTo SET IDENTITY_INSERT dbo.Tmp_DemoTable ON IF EXISTS(SELECT * FROM dbo.DemoTable) EXEC('INSERT INTO dbo.Tmp_DemoTable (Id, Description, sysValidFrom, sysValidTo) SELECT Id, Description, sysValidFrom, sysValidTo FROM dbo.DemoTable WITH (HOLDLOCK TABLOCKX)') SET IDENTITY_INSERT dbo.Tmp_DemoTable OFF DROP TABLE dbo.DemoTable EXECUTE sp_rename N'dbo.Tmp_DemoTable', N'DemoTable', 'OBJECT' ALTER TABLE dbo.DemoTable ADD CONSTRAINT PK_DemoTable PRIMARY KEY CLUSTERED ( Id ) GO ALTER TABLE [dbo].[DemoTable] ADD PERIOD FOR SYSTEM_TIME (sysValidFrom, sysValidTo) ALTER TABLE [dbo].[DemoTable] ALTER COLUMN sysValidFrom ADD HIDDEN ALTER TABLE [dbo].[DemoTable] ALTER COLUMN sysValidTo ADD HIDDEN</code>CREATE TABLE [dbo].[DemoTable]( [Id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PK_DemoTable PRIMARY KEY CLUSTERED (Id), [Description] [nvarchar](50) NOT NULL, sysValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF_dbo_DemoTable_sysValidFrom] DEFAULT SYSUTCDATETIME(), sysValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF_dbo_DemoTable_sysValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999'), PERIOD FOR SYSTEM_TIME (sysValidFrom, sysValidTo) ) GO</pre></li><li>Transfer the table to DB2 with SQL Compare. Everything should be fine.</li><li>Apply some changes to the table. For example with SQL Management Studio insert a new column [AddedColumn] between [Description] and [sysValidFrom] in DB1.dbo.DemoTable. With the following script (copied from SSMS):<br><pre class="CodeBlock"><code> Compare again. Should see [AddedColumn] as a new column that have to be transferred. Deploy with SQL Compare. The deployment script of SQL Compare is: @ERROR <> 0 SET NOEXEC ON GO DECLARE @idVal BIGINT SELECT @idVal = IDENT_CURRENT(N'[dbo].[DemoTable]') IF @idVal IS NOT NULL DBCC CHECKIDENT(N'[dbo].[RG_Recovery_1_DemoTable]', RESEED, @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 (@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed' END GO Compare again. Should see missing PERIOD and HIDDEN. Deploy again with SQL Compare. The deployment script of SQL Compare is: @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 (@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed' END GO Compare again. Should see missing HIDDEN attribute. Deploy again with SQL Compare. The deployment script of SQL Compare is: @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 (@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed' END GO Should see error: Failed to run SQL: Period column 'sysValidFrom' in a system-versioned temporal table cannot be altered. Error executing the following SQL: ALTER TABLE [dbo].[DemoTable] ALTER COLUMN [sysValidFrom] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL I think the correct command should be: ALTER TABLE [dbo].[DemoTable] ALTER COLUMN sysValidFrom ADD HIDDEN But, I prefer all changes to be deployed at once instead of 3 steps. / comments

Hello,Ok, I will try to explain how to reproduce the issue in more detail with an example: create a table in DB1 with the following script:ALTER TABLE dbo.DemoTable DROP CONSTRAINT DF_dbo_DemoTable...

0 votes

How can we help you today?

Ivailo

Activity overview