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

Landeros

I have the same problem, have you found what is the problem? – Zedoary

@AramGevorgyan could you take a look at my answer below? I'm troubleshooting this on my end a bit and curious if the same temporary workaround helps you as well. – Resolute

@BrianJorden: Yes, when I unchecked SR0014 it helps, but I haven't UDT declared. And I have thsese errors, only on some tables, not all. – Zedoary

@AramGevorgyan That is interesting and a little surprising. I'm assuming you are using temporal tables though? I spent some time trying to recreate the problem, but I think I managed to "fix" my repository at least. I was trying to strip all the files/objects out until it started working. Got to basically nothing left when it started working, but shockingly it kept working when I added everything back in. I think it may be something buried in the .sln or .sqlproj files around compatibility or VS versions, just not very sure at this point. – Resolute

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.

Resolute

Hi Brian, today I also faced this similar issue and this work around fixed the issue. I have added new column in one of the table and suddenly this error popped up. Just curious on why we are getting this error even though nothing else changed in the project. – Benioff

Based on my situation/setup/usage, my best guess has been that it was something to do with a combination of user-defined-data-types and temporal tables. To be clear though, this problem seems to have emerged only after upgrading to the latest versions, not a code change. Unfortunately I updated too many things at once to know what one actually introduced the issue. My DMs are open on Twitter at brian_jorden if you want to chat a little more. – Resolute

@BrianJorden thanks for this. For me if you un-select SR0014 in Visual Studio 15.6.7 and SSDT 15.1.61801.210 code analysis works fine – Tsan

@AaronHudon glad the workaround helped, I've now been contacted by or talked to a few people with this same issue and trying to determine commonalities. Are you using any temporal/system versioned tables? Also, are you using any user defined data types, such as create type type_something from int? – Resolute

@BrianJorden yes, i'm using temporal table(s), but not using UDTs. – Tsan

@BrianJorden did you ever get to the bottom of this? I have an SSDT project in Git. Both myself and my colleague are using identical commit of the project and same versions of VS and SSDT Extension. On my machine, the error is not thrown. On my colleague machine it is thrown! Is there maybe some file that is .gitignored on my machine that he needs on his? We tried with fresh project clone, same error. – Grosgrain