sql server – Visual Studio’s publish Database fails, but the script works in SSMS


I have an SQL database project in Visual Studio, with a pretty complex PostDeployment script.
It runs several scripts, which each contains a lot of INSERTS into some tables (not showing the contents of the subscripts here):

:r Scripts\Script1.sql
:r Scripts\Script2.sql
:r Scripts\Script3.sql
   ...

Now, when publishing the database project to an SQL server database I get some errors like this (in “View Results”):

SQL72014: Framework Microsoft SqlClient Data Provider: Msg 547, Level 16, State 0, Line xyz The INSERT statement conflicted with the FOREIGN KEY constraint…

So I obviously have an error in the ordering of the INSERTs (dependency order is wrong).

But troubleshooting turned out to be a struggle. I clicked “View script” to look at the resulting SQL statements. But the line numbers do no match any SQL statement that corresponds to the errors.
And when I copied and executed the resulting script in SSMS, it completed without errors!

Could someone help explain why the script fails in VS, but not in SSMS?
I assume that the script in “View Script” contains exactly the SQL statements run in Publish (and in the same order)

Leave a Reply

Your email address will not be published. Required fields are marked *