Avoid schema mismatch in System-Versioned tables

Question

Looking for a workaround for:

Error: SQL71609: System-versioned current and history tables do not have matching schemes. Mismatched column: 'XXXX'.

When trying to use SQL 2016 System-Versioned (Temporal) tables in SSDT for Visual Studio 2015.

I've defined a basic table:

CREATE TABLE [dbo].[Example] (
    [ExampleId] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [ExampleColumn] VARCHAR(50) NOT NULL,
    [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE=[history].[Example]))
GO

(Assuming the [history] schema is properly created in SSDT). This builds fine the first time.

If I later make a change:

CREATE TABLE [dbo].[Example] (
    [ExampleId] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [ExampleColumn] CHAR(50) NOT NULL, -- NOTE: Changed datatype
    [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE=[history].[Example]))
GO

Then the build fails with the error message above. Any change to the data type, length, precision, or scale will result in this error. (Including changing from VARCHAR to CHAR and VARCHAR(50) to VARCHAR(51); changing NOT NULL to NULL does not produce the error.) Doing a Clean does not fix things.

My current workaround is to make sure I have the latest version checked in to source control, then open the SQL Server Object Explorer, expand the Projects - XXXX folder and navigate to the affected table, then delete it. Then I have to restore the code (which SSDT deletes) from source control. This procedure is tedious, dangerous, and not what I want to be doing.

Has anyone found a way to fix this? Is it a bug?

I'm using Microsoft Visual Studio Professional 2015, Version 14.0.25431.01 Update 3 with SQL Server Data Tools 14.0.61021.0.

Orientalism

Your table doesn't look like a valid system-versioned table - it's missing the PERIOD FOR SYSTEM TIME and PERIOD columns - msdn.microsoft.com/en-us/library/mt590957.aspx – Hayleyhayloft

You are correct! I forgot to include those when I built the examples. I've fixed the post. – Orientalism

I can't replicate this on SQL 2016 RTM by executing an ALTER TABLE statement. How are you making your schema changes? – Hayleyhayloft

I think this is a Visual Studio and SSDT problem, more than a SQL Server problem. Using Visual Studio 2015, with the latest SSDT installed, create a new SQL database project. Make sure the project is set to build SQL Server 2016. Add a new table, and put the first bit of SQL from above in. Build the SSDT project. Then change the table SQL as in the second code block (alter the datatype), and try building again. Visual Studio should throw an error. – Orientalism

Answer 1

I can reproduce this problem. We (the SQL Server tools team) will work to get this fixed in a future version of SSDT. In the meantime, I believe you can work around this by explicitly defining the history table (i.e. add the history table with its desired schema to the project), and then manually keep the schema of the current and history table in sync.

If you encounter problems with explicitly defining the history table, try closing Visual Studio, deleting the DBMDL file in the project root, and then re-opening the project.

Natasha

The workaround is good... a lot of extra typing, but it works. Thanks! – Orientalism

Still hasn't been fixed as of 20170512 – Friendly

This works - close solution/project, delete dbmdl file, re-open project and it builds successfully again. Crazy. – Indiaindiaman

Still hasn't been fixed as of 05-17-2019 but the above work around by @PeterSchott - works. Thanks! – Beezer

Not fixed in VS2019 but @PeterSchott work around works – Engelbert

Answer 2

We just experienced this issue. We found a workaround by commenting out the system versioning elements of the table (effectively making it a normal table), building the project with the schema change we needed (which succeeds), and then putting the system versioning lines back in place (which also succeeds).

Messapian

Thank you - this worked great for me on visual studio 2019 – Etti

Worked for me! Thanks – Tyrannize

Answer 3

Just in case someone faced the same issue:

The fix is to go to [YourDatabaseProject]/bin/Debug folder and clear it and then build without removing anything.

Hope this helps!

Darwinism