SQL Compare cannot transfer correctly PERIOD FOR SYSTEM_TIME and HIDDEN attributes

IvailoIvailo Posts: 7 New member
in SQL Compare
Hello support team,
I am trying to transfer changes from lower environment to the next environment. Unfortunately PERIOD FOR SYSTEM_TIME and HIDDEN attributes are not transferred correctly.
PERIOD FOR SYSTEM_TIME is transferred after the table changes are transferred. HIDDEN attribute cannot be transferred at all. There is an error in the statement SQL Compare produces.

Step to reproduce the issue:
Create table in Env1 with PERIOD FOR SYSTEM_TIME and HIDDEN attributes on the period columns.
Create the same table in Env2 (they should be exactly the same). Remove PERIOD FOR SYSTEM_TIME on the table in Env2.

Compare and try to sync Env1 and Env2. You should be able to see the issues.

Please tell me how to sync environments if I am missing something?
Tagged:
· Share on Twitter

Answers

  • cperezcperez Posts: 56 Bronze 2
    Hi @Ivailo

    Thanks for reaching out to Redgate Support. I would like to test this, but for the sake of consistently, could you please provide the creation script you used where you saw this behavior? I did some sample testing on my end but was not getting an error.

    Best,

    Support
    · Share on Twitter
  • IvailoIvailo Posts: 7 New member
    Hello,

    Ok, I will try to explain how to reproduce the issue in more detail with an example:
    1. 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>
    2. Compare again. Should see [AddedColumn] as a new column that have to be transferred.
    3. 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
    4. Compare again. Should see missing PERIOD and HIDDEN.
    5. 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
    6. Compare again. Should see missing HIDDEN attribute.
    7. 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
    8. 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.

    · Share on Twitter
  • IvailoIvailo Posts: 7 New member
    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.
    · Share on Twitter
  • IvailoIvailo Posts: 7 New member
    Oh, I am sorry. I tried to edit the post and now it is missing.
    I will try to repost tomorrow.
    · Share on Twitter
  • IvailoIvailo Posts: 7 New member
    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)
    1. 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
    · Share on Twitter
  • IvailoIvailo Posts: 7 New member
    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'


    · Share on Twitter
  • IvailoIvailo Posts: 7 New member
    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.

    · Share on Twitter
  • cperezcperez Posts: 56 Bronze 2
    Good Afternoon @Ivailo,

    Appreciate the extensive write up, I am going to look over this shortly and test the steps again in my lab environment. Once I confirm the issue I will let you know some next steps to try.
    · Share on Twitter
  • cperezcperez Posts: 56 Bronze 2
    Good Morning @Ivailo,

    Was able to replicate this issue while utilizing the latest version of SQL Compare. Going to shoot this up to development and will circle back here and let you know next steps.
    · Share on Twitter
  • cperezcperez Posts: 56 Bronze 2
    Hello again @Ivailo,

    This issue has been logged as a bug by Development and is currently being worked on. Once work has concluded and I have next steps or a remediation available for you, I will circle back here an update you further.

    Cheers,

    Christian Perez
    · Share on Twitter
Sign In or Register to comment.