CODEWITHSUNDEEP
sql-serverpowershellsql-agent
Andrey
Andrey

Reputation: 11

Create agent job from power shell script with including of power shell command

I try to automate SQL Server 2016 installation process by using power shell scripting. When I execute Invoke-SqlCmd with parameter of file:

Invoke-SqlCmd -InputFile "..\res\test.sql" -ServerInstance "(local)" -Database "master" -ErrorAction Stop

which include agent job create script with power shell command:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Erase Phantom System Health Records.', 
    @step_id=3, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @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'PowerShell', 
    @command=N'if (''$(ESCAPE_SQUOTE(INST))'' -eq ''MSSQLSERVER'') {$a = ''\DEFAULT''} ELSE {$a = ''''};
       (Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()', 
        @database_name=N'master', 
        @flags=0

it`s stops with error:

Invoke-SqlCmd : 'INST' scripting variable not defined

Obviously this is $ character which I need to escape before creation. Have anybody idea about how to do this? I searched, but can`t find anything that work.

Upvotes: 1

Views: 610

Answers (2)

CrommCruach
CrommCruach

Reputation: 91

Disable variables for InvokeSQLcmd

Invoke-SqlCmd -InputFile "..\res\test.sql" -ServerInstance "(local)" -Database "master" -ErrorAction Stop -DisableVariables

Upvotes: 1

anthonypants
anthonypants

Reputation: 1

PowerShell is trying to parse your $(ESCAPE_xxxx(yyyy)) blocks, so you'll need to escape those before you send it to Invoke-SqlCmd. The easiest way to do that is going to be the replace operator:

$script = Get-Content "..\res\test.sql"
$script -replace '\(ESCAPE_(\w+)\((.+)\)\)','`(ESCAPE_$1`($2`)`)'
Invoke-SqlCmd -InputFile $script -ServerInstance "(local)" -Database "master" -ErrorAction Stop

Obviously, you won't be able to just escape every parenthetical in a block of SQL commands, but you can do some experimentation to find out how narrow or broad this replacement needs to be.

Upvotes: 0

Related Questions