asked
Can powershell script in sql job step call the next step in the same job
I need to call Step 2 only if criteria is met and it is a certain time of day. Here is the PS script for Step 1 Step 1 - check for files $Checktime = Get-Date $jobName = "Check For Files" $stepName = "Send No Invoices Email" IF ( (Test-Path -Path "\\test\data\AP\*.csv") -eq $true ) { $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = 'server=test;integrated security=TRUE;database=msdb' $sqlConnection.Open() $sqlCommand = new-object System.Data.SqlClient.SqlCommand $sqlCommand.CommandTimeout = 120 $sqlCommand.Connection = $sqlConnection $sqlCommand.CommandText= "exec dbo.sp_start_job APProcess" $sqlCommand.ExecuteNonQuery() $sqlConnection.Close() } ElseIf ( ($Checktime -ge (Get-Date 8:00) -and $Checktime -lt (Get-Date 9:00) ) ) {$sqlCMD = "EXEC dbo.sp_start_job N'{0}', @step_name = N'{1}'" -f $jobName, $stepName $sqlCMD } Step 2 - Send Specfic email I tried to raise an error and on failure go to step 2, but the first step actually failed and sent incorrect emails. I also tried not assigning the jobname and it failed with syntax error. Can PA initiate a step in the same job?
I suspect you won't be able to do it that way because I don't believe that SQL Server won't allow you to have more than one instance of the job running at a time... Can you not put the email step into a separate job and call that? Or make it into a stored procedure and call that from your powershell script?
Personally, I wouldn't want PowerShell calling the next step because I would normally have the have set to 'successful move to the next step'. In step 2 I would have a SPROC (or you could put the SQL in the command text, but I prefer SPROCS) something like this: DECLARE @CurrentDate DATETIME2 = GETDATE(); IF DATEPART(HOUR,@CurrentDate) = 8 BEGIN -- Send our email using sp_send_dbmail. PRINT 'Sending email now' END ELSE BEGIN -- Don't send our email. PRINT 'Out of hours, email not sent.' END; I've tested before, during and after 8 and it works, you can of course code other checks in as well.
18 People are following this question.
