避免系统版本表中的架构不匹配 [英] Avoid schema mismatch in System-Versioned tables

问题描述

正在寻找一种解决方法:

Looking for a workaround for:

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

当尝试在Visual Studio 2015的SSDT中使用SQL 2016系统版本化的(临时)表时.

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

(假设在SSDT中正确创建了[history]模式).第一次构建起来就很好.

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

如果我以后进行更改:

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

然后构建失败，并出现以上错误消息.数据类型，长度，精度或小数位数的任何更改都将导致此错误. (包括从VARCHAR更改为CHAR，从VARCHAR(50)更改为NULL；将NOT NULL更改为NULL不会产生错误.)Clean不能解决问题.

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.

我当前的解决方法是确保已将最新版本签入源代码管理，然后打开SQL Server对象资源管理器，展开Projects - XXXX文件夹并导航到受影响的表，然后将其删除.然后，我必须从源代码管理中还原代码(SSDT删除的代码).此过程很繁琐，危险，不是我想做的.

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?

我正在使用Microsoft Visual Studio Professional 2015，版本14.0.25431.01 Update 3和SQL Server数据工具14.0.61021.0.

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

推荐答案

我可以重现此问题.我们(SQL Server工具团队)将努力在SSDT的未来版本中修复此问题.同时，我相信您可以通过以下方式解决此问题:明确定义历史记录表(即，将具有所需模式的历史记录表添加到项目中)，然后手动使当前表和历史记录表的模式保持同步.

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.

如果在显式定义历史表时遇到问题，请尝试关闭Visual Studio，删除项目根目录中的DBMDL文件，然后重新打开项目.

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.

这篇关于避免系统版本表中的架构不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！