Why is SSDT Schema Compare showing difference in DefaultExpressionScript?

Question

While comparing Database instance to a Database project in VS2010 using SSDT, it shows a difference in one of the Stored Procedures. The Store Procedure DDL script is exactly the same but when I expanded the Properties folder under the Store Procedure it show a difference in the DefaultExpressionScript.

The source SP shows this in the DefaultExpresionScript:

DefaultExpressionScript = at

While the destination SP has this:

DefaultExpressionScript = -1

I don't see where I can change this there is no such object called DefaultExpressionScript in my database project. where should I fix this?

Constantine

Can you use a newer version of SSDT? Visual Studio 2010 is pretty old now and there has been a lot of work on it in the last couple of years – Muffle

I am actually doing migration from Visual Studio 2010 to VS 2013 and part of the steps to migrate is to migrate the database projects first to SSDT project within VS2010 before migrating fully to VS2013. Then I encountered this problem. – Constantine

VS 2017... still there – Tintinnabulum

Answer 1

This bug is still present in VS 2015 Update 3 with SSDT 14.0.61021.0. However, just scripting the stored procedure with ALTER ... in SSMS and running that query (which shouldn't change anything) fixed it for me. The schema comparison didn't show differences anymore afterwards.

Annotation

This changed something but another problem occurred. Rather then reporting that the DefaultExpressionScript isn't the same, after executing the ALTER, it says that the BodyScript is not the same however visually it is the same. – Socalled

Wow after executing a second ALTER, then and only then the difference disappeared. – Socalled

This should be the accepted answer - even in VS 2019. To elaborate: Right click the Stored Proc in SSMS, choose Script Stored Proc as >> ALTER To >> New Query Editor Window; and then Execute that script right there in SSMS. That somehow fixes it. ... so weird ... – Biannulate

Answer 2

The DefaultExpressionScript is the default value of one of the stored procedure's parameters. I suggest using Script As to get the TSQL definition of the stored procedure from the database and verifying that the stored procedure default values match what's in your database project.

Also, note that there have been a large number of bug fixes to schema comparison that aren't available in VS2010. Have you considered installing the latest version of Visual Studio 2015 Express with SSDT?

Granlund

This problem happens from the oldest VS I used to the newest VS 2015. – Gerge

VS 2017... still there. – Tintinnabulum

VS 2019 still does this. – Catchweight

VS 2022 Preview still does this – Inscribe

Answer 3

I finally found a workaround. You simply script the affected SP(s) for Modify and run the Alter statement, without changing anything. It corrects the values.

Shipwreck