CODEWITHSUNDEEP
powershellsql-server-2016sql-agent-job
DForck42
DForck42

Reputation: 20367

'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
        }

Upvotes: 1

Views: 3945

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

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
    }

Upvotes: 5

Related Questions