How to run the scripts from a SQL solution in VS

Question

I am using VS Pro 2019. I created a SQL Server DB project. I created a couple of scripts (all of them CREATE OR ALTER PROCEDURE).

At this point I am looking for a magic button to run them all (and in the darkness bind them... ops sorry ;).

Didn't find anything. I have tried to publish the project as it was my understanding. The publish operation works fine but none of the scripts have been run for what I can see.

screenshots (sorry they're in french but it basically says that publishing was OK)

What did I miss? I tried to create a script at the root of the project in case it did not support the multi directories but it didn't help.

Answer 1

An overview of SSDT

SSDT is a state-based approach.

For every version, the definition of each object in the database is stored in source control. At deployment time, the target database is compared to the state in source control (via a DacPac) and a deployment script is generated to update the target database to that specific version.

The code has to be organized in a manner of schemas and object inside them like in picture below:

^{Image source:https://devblogs.microsoft.com/wp-content/uploads/sites/31/2019/04/ssdt10_thumb.png}

Once all is set, publish the project, and the differences between project and database are applied on the target database.

The actual code to be run on DB could be checked by clicking on:

SSDT has capability to run "custom made" scripts by using Pre or PostDeployment mechanism. But I would use it as the last resort for situations that cannot be resolved in standard way.

How to: Specify Predeployment or Postdeployment Scripts

Pre-deployment and post-deployment scripts execute Transact-SQL statements before and after the main deployment script, which is generated from the database project. The pre-deployment script will not be executed when updating targets from schema compare results in Visual Studio. A project can have only one pre-deployment and one post-deployment script. These scripts can be used for many purposes

---

The structure of code we can see in the question looks like a migration-based approach which is different(it is supported by for instance Redgate Flyway/ReadyRoll). In this approach a separate SQL script is prepared for every change and scripts have to be run in specific order.

ReadyRoll Core is a migrations-based approach. ReadyRoll Core generates a migration script for each change at development timeIt can be edited so that developers have complete control over what will happen at deployment time. The migration scripts are stored in source control. At deployment time, the migration scripts are stitched together to generate the deployment script. Each migration script is only run against a target environment once

Key difference is what is the payload of the script:

+--------+-------------------+-----------------+
|        |    state-based    | migration-based |
+--------+-------------------+-----------------+
| script | object definition | change          |
+--------+-------------------+-----------------+

---

Using PostDeployment you could "force" SSDT to behave like a migration-based tool by using sqlcmd :r.

:r < filename > Parses additional Transact-SQL statements and sqlcmd commands from the file specified by into the statement cache.

PostDeployment script content:

 :r\...\Script1.sql
 :r\...\Script2.sql
 :r\...\...
 :r\...\Scriptn.sql

But doing so you lose all benefits of state-based migration like object validations, checking references between them and so on. The other challenge is that the scripts have to be idempotent or some kind of history table is used to mark which scripts were already applied.

Idempotent DDL Scripts

Idempotence is the property of certain operations in mathematics and computer science, that can be applied multiple times without changing the result beyond the first time they are run. When you are making changes to a database to deploy a new version, you need to be sure that certain changes aren’t made twice, or in the wrong order. If you neglect to do this, you will generally get an error, but the consequences aren’t entirely predictable, and a chain of events can have an unhappy ending.