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, Mar 17 '17 at 17:16

score 3 · Accepted Answer · edited May 23 '17 at 10:30

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.

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

1 Answers1