SSDT code analysis error for SQL Server temporal table

Question

I have many temporal tables in my SQL Server database project. Today I upgrade my SSDT to the latest version and I got the following errors for temporal tables when building the project. For example, I have a temporal table UseCase which has a history table called UseCase_HISTORY.

It looks like a code analysis problem. If I disable the code analysis on build, there will be no errors on build.

I am using VS2015 and VS2017. Both have the same problems.

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(1292,5): Error:  SQL72039: The substring is out of the bounds for script MSSQL::dbo.UseCase_HISTORY.
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(1292,5): Error:  MSB4018: The "SqlStaticCodeAnalysisTask" task failed unexpectedly.
Microsoft.Data.Tools.Schema.SchemaModel.ScriptCacheException: The substring is out of the bounds for script MSSQL::dbo.UseCase_HISTORY.
   at Microsoft.Data.Tools.Schema.SchemaModel.ScriptCache.CheckAndGetSubstring(String cacheIdentifier, Int32 startOffset, Int32 length, CachedString value)
   at Microsoft.Data.Tools.Schema.SchemaModel.ScriptCache.GetScript(String cacheIdentifier, Int32 startOffset, Int32 length)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSourceInformation.ParseSource()
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSourceInformation.get_ScriptDom()
   at Microsoft.SqlServer.Dac.Model.SqlSchemaModelObjectService.GetOriginalSourceFragment(TSqlObject tSqlObject)
   at Microsoft.SqlServer.Dac.TSqlModelUtils.TryGetFragmentForAnalysis(TSqlObject tSqlObject, TSqlFragment& fragment)
   at Microsoft.SqlServer.Dac.CodeAnalysis.SqlRuleExecutionContext.get_ScriptFragment()
   at Microsoft.SqlServer.Dac.CodeAnalysis.Rules.Design.DataTypeCompatibilityRule.Analyze(SqlRuleExecutionContext context)
   at Microsoft.SqlServer.Dac.CodeAnalysis.Engine.RuleEngine.TryExecuteSingleRule(RuleDescriptor ruleDescriptor, SqlRuleExecutionContext executionContext)
   at Microsoft.SqlServer.Dac.CodeAnalysis.Engine.RuleEngine.ExecuteSingleRule(RuleDescriptor ruleDescriptor, SqlRuleExecutionContext executionContext, IList`1 errors)
   at Microsoft.SqlServer.Dac.CodeAnalysis.Engine.RuleEngine.ExecuteRules(SqlRuleExecutionContext executionContext, IEnumerable`1 rules, IList`1 allErrors)
   at Microsoft.SqlServer.Dac.CodeAnalysis.Engine.RuleEngine.ExecuteElementRules(TSqlModel schemaModel, List`1 allErrors, IEnumerable`1 modelElements, ISet`1 rules, List`1 allProblems)
   at Microsoft.SqlServer.Dac.CodeAnalysis.Engine.RuleEngine.ExecuteElementRules(TSqlModel schemaModel, List`1 allErrors)
   at Microsoft.SqlServer.Dac.CodeAnalysis.Engine.RuleEngine.ExecuteRules(TSqlModel schemaModel, IList`1& errors)
   at Microsoft.SqlServer.Dac.CodeAnalysis.CodeAnalysisService.ExecuteProcessRulesStep()
   at Microsoft.SqlServer.Dac.CodeAnalysis.CodeAnalysisService.ExecuteStep(Func`1 step)
   at Microsoft.SqlServer.Dac.CodeAnalysis.CodeAnalysisService.Execute()
   at Microsoft.SqlServer.Dac.CodeAnalysis.CodeAnalysisService.Analyze(TSqlModel model)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlStaticCodeAnalysisTask.ExecuteProcessRulesStep()
   at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlStaticCodeAnalysisTask.ExecuteStep(Func`1 step)
   at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlStaticCodeAnalysisTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__26.MoveNext()

Answer 1

I had the same errors you are showing after I upgraded SSMS/SSDT/DACFX all at the same time to version 17.3 (was a dumb move to do all at once). I've at least narrowed this problem down a ton in my situation.

I haven't been able to fully replicate this issue in a fresh project, but it does seem to be directly related to the SR0014 option within the code analysis settings. As soon as I turned that specific option off, everything started working again.

I think you may have spotted part of the issue, temporal tables/history tables as I'm using them extensively as well. Based on the particular rule that is throwing the hard errors, there might be another commonality in our designs. Are you also using User Defined Data Types on that same table that is throwing errors? I've aliased basically all of my data types for consistency and downstream usage.

My current hunch is that this error is associated with the combination of multiple temporal tables and user defined data types. Could you confirm if you are using those as well and if unchecking SR0014 allows you to build your project? As a side note, I'm targeting Azure SQL Database with a 140 compatibility level.

If that is the case, it is probably a very small minority of users being impacted and we'll need to actually report this one.