SQL Server database project pre- and post-deployment script

Question

I've added an extra column to a table which I want to initialize using a query in the post deployment script. Unfortunately I can't seem to write a query which can be run every time so I'm looking for a way to check in the pre-deployment script if the column is available and pass an argument or variable to the post-deployment script which will then run the initialization query once.

Attempt 1: I tried setting a sqlcmd var in the pre-deployment script but the following syntax isn't allowed:

IF COL_LENGTH('dbo.Table','NewColumn') IS NULL
    :setvar PerformInitQuery 1

Attempt 2: I've also tried using a normal variable in the pre-deployment script:

DECLARE @PerformInitQuery BIT = 0
IF COL_LENGTH('dbo.Table','NewColumn') IS NULL
    SET @PerformInitQuery = 1

And accessing it in the post-deployment script:

IF @PerformInitQuery = 1
BEGIN
    :r ".\DeploymentScripts\PerformInitQuery.sql"
END

This last attempt seemed to work when publishing the project from Visual Studio but not on our build server; which uses SqlPackage.exe to publish the generated .dacpac file to the database.

Error SQL72014: .Net SqlClient Data Provider:

Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@PerformInitQuery"

A normal variable won't work because it only exists within a single batch. The deployment will use multiple batches. Just check the create script and you'll see multiple GO statements. Why not add the same check that you do in your pre-deployment to your post-deployment? — JodyT
– JodyT, Commented Sep 19, 2016 at 9:36
Because the build will create the column before the post-deployment script runs so I have no way to determine if it was created this run or a run before — Zenuka
– Zenuka, Commented Sep 19, 2016 at 9:50

Edward Comeau · Accepted Answer · 2016-09-19 09:51:56Z

You could try using a temp table to hold values you wish to pass from pre to post scripts;

/*
     Pre-Deployment Script Template                         
    --------------------------------------------------------------------------------------
     This file contains SQL statements that will be executed before the build script.   
     Use SQLCMD syntax to include a file in the pre-deployment script.          
     Example:      :r .\myfile.sql                              
     Use SQLCMD syntax to reference a variable in the pre-deployment script.        
     Example:      :setvar TableName MyTable                            
                   SELECT * FROM [$(TableName)]                 
    --------------------------------------------------------------------------------------
    */

    select 'hello world' as [Col] into #temptable

picked up in post deployment script;

/*
Post-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.       
 Use SQLCMD syntax to include a file in the post-deployment script.         
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the post-deployment script.       
 Example:      :setvar TableName MyTable                            
               SELECT * FROM [$(TableName)]                 
--------------------------------------------------------------------------------------
*/

declare @var nvarchar(200)
select @var = [Col] from #temptable

print @var

hello world

Update complete.

Collectives™ on Stack Overflow

SQL Server database project pre- and post-deployment script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related