How to supply SQL-CMD variables to SQL Agent Job?

Question

I have created a Job in my SQL instance. Consider following as the SQL-CMD scripts set to run while executing the step:

:setvar DatabaseName "BillingDatabase"
:setvar ReportingDatabaseName "BillingDatabase_Reporting"

BEGIN TRANSACTION
SET NOCOUNT ON

USE [$(NzeurReportingDatabaseName)]......

COMMIT TRANSACTION

this job is set to run on various environments where the database names are expected to be different. For example, production environment may have something like - "[CompanyName].Billing.Database", and "[CompanyName].Billing.ReportingDatabase"

How can I configure this SQL job to supply these CMD variables depending upon the environment where the job is created. This is because, our deployment process is fairly automated, and we don't want to edit the variables manually in SQL Job steps once the job is created.

Any idea of how to achieve this?

Answer 1

I couldn't find any direct solution of fixing this. But wrote PowerShell scripts to replace SQL-CMD variables in the script file:

function replaceCmdletParameterValueInFile( $file, $key, $value ) {
    $content = Get-Content $file
    if ( $content -match ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]" ) {
        $content -replace ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]", ":setvar $key $value" |
        Set-Content $file     
    } else {
        Add-Content $file ":setvar $key $value"
    }
}

During the deployment, I call this function to replace database name before Invoke-SqlCmd.

replaceCmdletParameterValueInFile $scriptfile "DatabaseName" "`"$MyDatabaseName`""