SQL Change Automation version: 3.3.19211.9712 issues

dannett

I was running version 3.3.19204.9562 and the generated SQL file was ok and had the following builtin variables set:

----===============================================================================

---- SQLCMD Variables

---- This script is designed to be called by SQLCMD.EXE with variables specified on the command line.

---- However you can also run it in SQL Management Studio by uncommenting this section (CTRL+K, CTRL+U).

--:setvar DatabaseName "IMX.GTS.Database.ReadyRoll"

--:setvar ReleaseVersion ""

--:setvar ForceDeployWithoutBaseline "False"

--:setvar DefaultFilePrefix "IMX.GTS.Database.ReadyRoll"

--:setvar DefaultDataPath ""

--:setvar DefaultLogPath ""

--:setvar DefaultBackupPath ""

after upgrading to 3.3.19211.9712, the line
--:setvar DatabaseName "IMX.GTS.Database.ReadyRoll"

is not there and also the test later on

IF DB_ID('$(DatabaseName)') IS NULL

  RAISERROR ('The database [$(DatabaseName)] could not be found. Please ensure that there is a Pre-Deployment script within your project that contains a CREATE DATABASE statement (e.g. Pre-Deployment\01_Create_Database.sql).', 16, 127);

GO

does not exist either.

Is this a known change?

Many thanks

David E

Hi,

We made a change in this area in the most recent release to support running builds/releases against different databases than the one they were originally created for. This is useful if you have many databases that should be at in the same state and want to run the same update on all of them. It also aligns workflows that can be done with release artifacts for SQL Change Automation and SQL Source Control projects. For most automation scenarios the database name sqlcmd variable will be set as part of the `Use-ReleaseArtifact` PowerShell cmdlet or as one of the steps in the ReadyRoll AzureDevOps addon.

Looking at the code you've provided it looks like you're making use of the package script where these variable definitions are commented out by default. Could you tell us more about your workflow for deployments and what has changed / the issue now experienced in the update?

dannett

The SQL package script is handed off to our own package/deploy process which uncomments the :setvar lines and set the values for a client. Obviously, at the moments it all falls down as there is no DatabaseName variable. I can add it back into the script myself so customers can run it themselves in Management Studio but I just wondered if it was an oversight or not.

You may want to update your documentation as well e.g.
https://documentation.red-gate.com/display/SCA3/Deploy+using+SQL+Server+Management+Studio
to mention that once uncommented, the user has to add the missing variable back in.

Many thanks

David E

Thanks for letting us know. I've just raised a change to get the variable added back in to the package script as I can see why it is annoying to have to manually add this when all the information is available. This will not prevent the script running on other databases if needed.

dannett

The SQL package script is handed off to our own package/deploy process which uncomments the :setvar lines and set the values for a client. Obviously, at the moments it all falls down as there is no DatabaseName variable. I can add it back into the script myself so customers can run it themselves in Management Studio but I just wondered if it was an oversight or not.

You may want to update your documentation as well e.g.
https://documentation.red-gate.com/display/SCA3/Deploy+using+SQL+Server+Management+Studio
to mention that once uncommented, the user needs to add the missing variable back in

Thanks

dannett

The SQL package script is handed off to our own package/deploy process which uncomments the :setvar lines and set the values for a client. Obviously, at the moments it all falls down as there is no DatabaseName variable. I can add it back into the script myself so customers can run it themselves in Management Studio but I just wondered if it was an oversight or not.

You may want to update your documentation as well e.g.
https://documentation.red-gate.com/display/SCA3/Deploy+using+SQL+Server+Management+Studio
to mention that once uncommented, the user needs to add the missing variable back in

Thanks