Search code examples
powershellsql-server-2016sql-agent-job

'SRVR' scripting variable not defined


I have a powershell script that picks up a bunch of sql files and executes them against a server. Everything has been running fine until I started working with the scripts to drop and recreate jobs.

The part of the sql script that keeps throwing an error is:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBAdmin -Q "EXECUTE [ola].[DatabaseIntegrityCheck] @Databases = ''SYSTEM_DATABASES, -tempdb'', @LogToTable = ''Y''" -b', 
        @flags=0

and the error is:

'SRVR' scripting variable not defined

This leaves me to believe it doesn't like this particular part:

@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBAdmin -Q "EXECUTE [ola].[DatabaseIntegrityCheck] @Databases = ''SYSTEM_DATABASES, -tempdb'', @LogToTable = ''Y''" -b', 

The interesting thing is if I copy and paste the sql script into SSMS and execute it, it works just fine.

Why am I getting this error only when executing from Powershell?


The powershell that runs the sql

try
        {
            Invoke-Sqlcmd  -ServerInstance $ServerName `
                -Database DBAdmin `
                -InputFile  $f.FullName `
                -QueryTimeout 30 `
                -ErrorAction Stop
        }
        catch
        {
            Write-Host "File:" $f.FullName 
            Write-Error $_.Exception.Message
        }

Solution

  • Add the DisableVariables switch to Invoke-Sqlcmd so that the SQL Agent scripting variables are not interpreted as SQLCMD variables:

    try
        {
            Invoke-Sqlcmd  -ServerInstance $ServerName `
                -Database DBAdmin `
                -InputFile  $f.FullName `
                -QueryTimeout 30 `
                -ErrorAction Stop `
                -DisableVariables
        }
        catch
        {
            Write-Host "File:" $f.FullName 
            Write-Error $_.Exception.Message
        }