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.

Your table doesn't look like a valid system-versioned table - it's missing the `PERIOD FOR SYSTEM TIME` and `PERIOD` columns - https://msdn.microsoft.com/en-us/library/mt590957.aspx — Ed Harper, Dec 05 '16 at 10:22
You are correct! I forgot to include those when I built the examples. I've fixed the post. — Andrew Barnett, Dec 05 '16 at 19:07
I can't replicate this on SQL 2016 RTM by executing an `ALTER TABLE` statement. How are you making your schema changes? — Ed Harper, Dec 06 '16 at 08:16
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. — Andrew Barnett, Dec 06 '16 at 19:37

score 17 · Accepted Answer · answered Dec 06 '16 at 20:44

17

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.

answered Dec 06 '16 at 20:44

Steven Green

3,387
14
17

The workaround is good... a lot of extra typing, but it works. Thanks! – Andrew Barnett Dec 09 '16 at 18:40
4

Still hasn't been fixed as of 20170512 – Anthony Genovese May 12 '17 at 21:57
7

This works - close solution/project, delete dbmdl file, re-open project and it builds successfully again. Crazy. – Peter Schott Sep 29 '18 at 00:59
3

Still hasn't been fixed as of 05-17-2019 but the above work around by @PeterSchott - works. Thanks! – Shiva Naru May 17 '19 at 22:07
Not fixed in VS2019 but @PeterSchott work around works – Neil Billingham Mar 16 '21 at 15:05

score 17 · Answer 2 · answered Dec 12 '16 at 14:51

17

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).

answered Dec 12 '16 at 14:51

Johnathon Trout

171
3

Thank you - this worked great for me on visual studio 2019 – jlnorsworthy Dec 18 '19 at 19:53

score 1 · Answer 3 · answered Oct 30 '19 at 21:07

1

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!

answered Oct 30 '19 at 21:07

Khaled Saleh

116
1
9

Avoid schema mismatch in System-Versioned tables

3 Answers3

Linked