Temporal Tables

ben.collins

Does any know if ReadyRoll is compatible with SQL Server temporal tables?

We are trying to use it with Octopus Deploy and I'm getting syntax error messages when I try to enable system versioning.

ALTER TABLE dbo.Accounts
    SET (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.AccountsHistory));

Error: : Incorrect syntax near SYSTEM_VERSIONING

Using ReadyRoll.MSBuild.1.13.10.1457

dnlnln

We have confirmed this to be a bug in the current version of ReadyRoll (1.13.21), with projects that target the Microsoft Azure SQL Database platform (projects that target SQL Server 2016 should not be affected).

I've sent you a private build that resolves the issue, however it should be included in the next ReadyRoll release.

professor

Is this thing fixed yet? I'm getting invalid syntax with next code targeting Azure V12, ReadyRoll.MSBuild 1.16.18078.8962 (18088.9228 appears to be totally broken):

ALTER TABLE [dbo].[MyTable] ADD
[SysStartTime] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF__MyTable__SysSt__6FB49575] DEFAULT (sysutcdatetime()),
[SysEndTime] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF__MyTable__SysEn__70A8B9AE] DEFAULT (CONVERT([datetime2],'9999-12-31 23:59:59.99999999',(0))),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
GO

dnlnln

professor said:

Is this thing fixed yet? I'm getting invalid syntax with next code targeting Azure V12, ReadyRoll.MSBuild 1.16.18078.8962 (18088.9228 appears to be totally broken):

The issue should have been resolved in 1.13.23, however if you're continuing to see this issue after checking the Target platform setting in the project designer, please let me know.

I'm concerned about the problem you experienced with 1.16.18088. Was this also related to the temporal table issue or was it a more general issue with your installation?

professor

Hi Daniel,

I actually tried both 2016 and Azure V12 as targets, both of them fail while deploying script from powershell:

***** EXECUTING MIGRATION "Migrations\0025_20180403-1655_andriy.sql", ID: {819a4783-bb9b-430a-b8d0-5610705adbf7} *****
Altering [dbo].[MyTable]
Msg 102, Level 15, State 1, Server MyMachine\LOCALDB#2BBA9446, Line 4
Incorrect syntax near 'SysEndTime'.
D:\develop\MySolution\MyDb\bin\Debug\MyDb_DeployPackage.ps1 : A deployment error
occurred: sqlcmd.exe exited with a non-zero exit code.
At line:1 char:70
+ ... uth='True'; D:\develop\MySolution\MyDb\bin\Debug\MyDb ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,MyDb_DeployPackage.ps1

sqlcmd.exe exited with a non-zero exit code.
At D:\develop\MySolution\MyDb\bin\Debug\MyDb_DeployPackage.ps1:209 char:5
+     throw 'sqlcmd.exe exited with a non-zero exit code.'
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (sqlcmd.exe exit...zero exit code.:String) [], RuntimeException
    + FullyQualifiedErrorId : sqlcmd.exe exited with a non-zero exit code.

Surprisingly, same migration via Visual Studio add-in just works. 

18088 problem was more general and had nothing to do with temporal tables (we introduced them later). In particular, 18088 in combination with most recent add-in at the time wasn't able to create database on local with windows auth. Powershell, on the other hand, worked fine. But I confirm it to work with current add-in, so I think this one can be considered resolved.

Thank you,
Andriy

dnlnln

professor said:

I actually tried both 2016 and Azure V12 as targets, both of them fail while deploying script from powershell:

***** EXECUTING MIGRATION "Migrations\0025_20180403-1655_andriy.sql", ID: {819a4783-bb9b-430a-b8d0-5610705adbf7} *****
Altering [dbo].[MyTable]
Msg 102, Level 15, State 1, Server MyMachine\LOCALDB#2BBA9446, Line 4
Incorrect syntax near 'SysEndTime'.

Thank you for reporting this. I'm able to reproduce this and have notified the team (issue record 1694). Sorry for the inconvenience. I'll let you know once there is a resolution.

professor

We have temporal temporary workaround, looking forward for proper solution.
And thank you personally, ReadyRoll is great product.

dnlnln

You're very welcome and thank you for your kind words.

Note that a fix for temporal tables in the deployment script (issue 1694) was released today in 1.16.18100.

If you continue to encounter issues, please let me know.

professor

Fix confirmed. Annoying build warnings also gone.

Thank you!

dnlnln

That's great to hear, thanks for confirming the fix(es)

Btw, in case anyone is wondering, professor is referring to the following items in the release notes:

• OctoPack no longer logs warnings at build time when packaging a sqlproj file for deployment with Octopus (Note: MSBuild 15.0 or Visual Studio 2017 is required). If you want OctoPack to keep logging such warnings anyway, add the following argument within your Continuous Integration build configuration: /p:MSBuildWarningsAsMessages=[blank]

• Fixed an issue where the final character of a batch ending in a temporal table statement might be cut off during package script generation

(please correct me if wrong, however)