Problem with Powershell and SQL Agent Job token

Question

Post reply

Problem with Powershell and SQL Agent Job token

whenriksen

SSCarpal Tunnel

Points: 4752
More actions
March 17, 2014 at 4:31 pm

#305420

I'm working on a script that will transfer SQL Agent Jobs to another job server. One of the jobs uses the SQL Agent Token in a step:
WHERE job_id = $(ESCAPE_SQUOTE(JOBID))
Once the job is scripted out to a variable, I need to do -replace a string value with another string value to prepare the job script for the new environment. The values I want to replace are not even in this part of the script, but it causes Invoke-SQLCmd to fail because Powershell attempts to interpret the $() as an expression.
Any suggestions?
Wes
(A solid design is always preferable to a creative workaround)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Any chance for some more information? I understand that you won't want to post too much.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 2

I'm scripting the jobs out using SMO into a string variable using SMO and the .Script() method. One of the jobs uses the SQL Token I mentioned above. I do some replace commands to update the job script for the target server, but when I attempt to execute the command using Invoke-SQLCMD, it fails on the job token. Attempts to escape the JobToken fail. A snippet from my Powershell script is below.

****************************************************

$SourceServer = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SourceSSISServerName

$TargetServer = New-Object "Microsoft.SqlServer.Management.Smo.Server" $TargetSSISServerName

[string]$Script = ""

foreach($SJob in $SourceServer.JobServer.Jobs | where{$_.Name -like 'EDW*'})

{

#Dynamic SQL to script Drop Job statement

#Each SQL Statement must be terminated with GO;

$Script= $Script+"--Drop Job $($SJob.Name)`r`nDeclare @SQL NVARCHAR(500);

SELECT @SQL = 'EXEC msdb.dbo.sp_delete_job @job_id=N''' + CONVERT(NCHAR(36),JOB_id) + ''', @delete_unused_schedule=1;'

FROM MSDB.DBO.sysjobs AS S

WHERE NAME = '$($SJob.Name)';

EXECUTE (@SQL);

GO;"

#Combine SMO script with dynamic SQL to comment script and disable job immediately after created

$Script = $Script+"`r`n--Create Job $($SJob.Name)`r`n$($SJob.Script()) GO;`r`n--Disable Job $($SJob.Name)`r`nExecute msdb.dbo.sp_update_job @job_name = '$($SJob.Name)',@enabled=0`r`nGO;`r`n`r`n"

}

#Must escape the "\" character only in the match portion of the replace statement and replace it with "\\"

$SourceSSISPath = $SourceSSISPath -replace "\\","\\"

$SourceSSISServerName = $SourceSSISServerName -replace "\\","\\"

$SourceDBServer = $SourceDBServer -replace "\\","\\"

$script = $Script -replace "$SourceSSISPath" ,"$TargetSSISPath"

$Script = $Script -replace "$SourceSSISServerName","$TargetSSISServerName"

$Script = $Script -replace "$SourceDBServer","$TargetDBServer"

$Script = $Script -replace "

$Script | Out-File "$SSISScriptPath\sqljobs.sql"

[String[]]$queries = $Script -split "GO;"

foreach ($query in $queries)

{

Invoke-SQLCmd -Query:$query -Database:'MSDB' -ServerInstance:$TargetSSISServerName | Out-File $logfile -Append

}

***********************

When powershell executes the jobstep containing the SQL Token, it throws an error, even though the SQL is valid.

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CHECK STEPS',

@step_id=4,

@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'TSQL',

@command=N'SELECT step_id FROM msdb.dbo.sysjobhistory WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0) AND job_id = $(ESCAPE_SQUOTE(JOBID)) AND run_status <> 1 -- success IF @@ROWCOUNT <> 0 RAISERROR(''A preceding step failed'', 16, 1)', @database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

*******************

I assume that putting a backtick in front of the $ character would tell powershell not to consider it as a variable expression, however I can't get -replace to properly recognize that point and replace it with something else.

cls

[string]$SQL = 'WHERE job_id = $(ESCAPE_SQUOTE(JOBID))'

#Display original string

write-output $SQL

#Verify syntax of Replace is correct and working

$NewSQL = $SQL -replace '_','!'

write-output $NewSQL

$NewSQL = $SQL -replace '$','`$'

write-output $NewSQL

$NewSQL = $SQL -replace '`$','`$'

write-output $NewSQL

$NewSQL = $SQL -replace '``$','``$'

write-output $NewSQL

$NewSQL = $SQL -replace 'WHERE job_id = ','WHERE job_id = `'

write-output $NewSQL

******

Any suggestions?

Wes
(A solid design is always preferable to a creative workaround)

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 3

Found it!!!

I needed to include -DisableVariables in the Invoke-SQLCmd statement. Now it runs without erroring on the SQL Token.

Invoke-SQLCmd -Query:$query -Database:'MSDB' -ServerInstance:$TargetSSISServerName -DisableVariables| Out-File $logfile -Append

Wes

Wes
(A solid design is always preferable to a creative workaround)