T-SQL Post-Deployment Script how to reference a variable

Question

I'm deployng DACPAC with with the following post-deployment script1:

ALTER DATABASE [$(DatabaseName)] 
MODIFY FILE (NAME = [$(DatabaseName)], 
             SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 20%)

However, when I try to reference this post-deployment script2, deployment fails:

ALTER DATABASE [$(DatabaseName)] 
MODIFY FILE (NAME = [$(DatabaseName)], 
             SIZE = $(size), MAXSIZE = UNLIMITED, FILEGROWTH = 20%)

So, my question is what is the correct syntax to pass as size?

I use this command to initiate the script (first script1 works, script2 doesn't)

$resourceGroup = "SQL1"
$vmName = "SQL1"
$storageName = "sql1artifactsstorage111"
$ConfigurationPath = "C:\DSC\Ext\deployDB.ps1"
$ConfigurationName = "deployDB"
$configurationArchive = "deployDB.ps1.zip"
#Publish the configuration script into user storage
Publish-AzureRmVMDscConfiguration -ConfigurationPath $ConfigurationPath -ResourceGroupName $resourceGroup -StorageAccountName $storageName -force
#Set the VM to run the DSC configuration
$configurationArguments = 
@{
    Credential = Get-Credential;
    DatabaseName = 'Database1' 
    size = '100MB'
}
Set-AzureRmVmDscExtension -Version 2.22 -Name dscExtension -ResourceGroupName $resourceGroup -VMName $vmName -ArchiveStorageAccountName $storageName -ArchiveBlobName $configurationArchive -AutoUpdate:$true -ConfigurationName $ConfigurationName -ConfigurationArgument $configurationArguments

This is the error DSC shows:

Dac Deploy Failed: 'Exception calling "Deploy" 
with "3" argument(s): "An error occurred during deployment plan generation. 
Deployment cannot continue."'

Do you have multiple files tagged as "Post-Deploy"? You should only have one post-deploy file and use a :r tag to include other files to run in that post-deploy script. — Peter Schott
– Peter Schott, Commented Mar 17, 2017 at 17:16

Community · Accepted Answer · 2017-05-23 10:30:11Z

3

Dynamic SQL is the last refuge of the scoundrel:

EXEC ('ALTER DATABASE [$(DatabaseName)] MODIFY FILE (NAME = [$(DatabaseName)],SIZE = $(size), MAXSIZE = UNLIMITED, FILEGROWTH = 20%)');
GO

worked for me, albeit in a local deployment rather than through Azure DSC.

This is a general purpose technique for doing these "DBA-esque" activities that often don't support TSQL variable substitution.

Note to future readers: this trick apparently worked, which is to say the OP has marked it as the answer. However, it was probably only necessary due to differing versions of DacFX between the workstation and the build server, see for example another SO post relating to the same error message.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 17, 2017 at 17:05

Gavin Campbell

8366 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL Post-Deployment Script how to reference a variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related