Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Powershell and SQL Agent Job token Expand / Collapse
Author
Message
Posted Monday, March 17, 2014 4:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 PM
Points: 39, Visits: 221
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?
Post #1551956
Posted Tuesday, March 18, 2014 5:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 5,397, Visits: 3,121
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!!!
Post #1552105
Posted Wednesday, March 19, 2014 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 PM
Points: 39, Visits: 221
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?
Post #1552731
Posted Wednesday, March 19, 2014 3:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 PM
Points: 39, Visits: 221
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
Post #1552810
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse