system version tables generate invalid stored procedures

[pbolduc]

In SQL Server 2016, we can use system version tables which will have hidden auto generated columns. When running against, SQL Server 2016 or above, these columns should be excluded. This can be fixed by adding additional WHERE condition to the generated schemaSql.

if SELECT SERVERPROPERTY ('ProductMajorVersion') >= 13, then append "AND c.generated_always_type_desc NOT IN (N'AS_ROW_START',N'AS_ROW_END')"

[jonwagner]

I'm going through all of the open issues and working on a v3.0. Can you post sql for a table that exhibits this behavior?

[pbolduc]

Example from the docs https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table. AutoProc tries to insert value into generated always columns.

CREATE TABLE Department
(
DeptID int NOT NULL PRIMARY KEY CLUSTERED
, DeptName varchar(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID int NULL
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON)
;