Powershell script executed from SQL Server - stops working if put to background job

Question

I execute a PowerShell script to create folders and inbox-rules in Office365.

The script works fine when called from SQL Server.

But if I put the code in function and calls with Start-Job then it stops working from SQL Server - but it will work if executed from local machine(?)

I really want code to run in background because it takes 2-3 minutes to complete.

Any suggestions as to what I'm doing wrong?

This is code that works from SQL Server:

param( 
     [Parameter(Position=0, Mandatory=$true)] [string]$FolderName 
    )

-- CODE HERE --

-- End of file

This is my code with ScriptBlock (does not work from sql, but works from local machine/server):

param( 
     [Parameter(Position=0, Mandatory=$true)] [string]$FolderName 
    )

$func = {function createFolderAndRule {
    param([string]$FolderName)
    #-- CODE HERE --

}
}


Start-Job -ScriptBlock {param($tm) createFolderAndRule $tm } -InitializationScript $func -ArgumentList($FolderName)
# End of file

How are you calling the PowerShell script from SQL Server? Is there an error message when the script doesn't work? — Ed Harper
– Ed Harper, Commented Sep 29, 2016 at 19:06
set _at_cmd = 'powershell "D:\PS\NewProject.ps1"'+' ' + _at_folder exec master.dbo.xp_cmdshell _at_cmd The sql output: Id Name PSJobTypeName State HasMoreData Location -- ---- ------------- ----- ----------- -------- 1 Job1 BackgroundJob Running True localhost Maybe it is permission error? — methosmen
– methosmen, Commented Sep 30, 2016 at 3:38
Using Sql Server to create Office365 rules is a bit... personal approach. Maybe you could do the same with, say, Task Scheduler instead? — vonPryz
– vonPryz, Commented Sep 30, 2016 at 5:49
It is personal yes :) Every new project created in the database should trigger a new inbox-rule in Exchange. Any other approach to solve this is much appreciated — methosmen
– methosmen, Commented Sep 30, 2016 at 6:02

Ed Harper · Accepted Answer · 2016-09-30 08:02:37Z

1

Rather than using PowerShell jobs to process your script asynchronously, a more conventional approach might be to add the O365 folder name you want to create to a table in the database, which is in turn used to drive a SQL Server Agent job which executes the PowerShell script. The SQL Server Agent job could poll the table on a schedule for new folders to create. You might still need to trigger the script from xp_cmdshell, because SQL Server Agent job steps can be fiddly to parameterise.

If you prefer to stick with the method you're using, probably the only way to debug this is to retrieve the output of the PowerShell job by modifying your script so that it does something like:

param( 
     [Parameter(Position=0, Mandatory=$true)] [string]$FolderName 
    )

$func = {function createFolderAndRule {
    param([string]$FolderName)
    #-- CODE HERE --
}
}


Start-Job -ScriptBlock {param($tm) createFolderAndRule $tm } -InitializationScript $func -ArgumentList($FolderName)

Start-Sleep -s 300 #sleep 5 mins

Receive-Job -Id 1 #retrieve the output of the PowerShell job

Remove-Job -Id 1

answered Sep 30, 2016 at 8:02

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

methosmen Over a year ago

Thank you! One step closer it seems. The script actually works when using the Start-Sleep -s 300 #sleep 5 mins But it does not work if the sleep period is i.e. 2 seconds ...

Ed Harper Over a year ago

@methosmen - the implication is that the running PS job is terminated when the parent xp_cmdshell command finishes, regardless of the state of the job; adding the 5 minute wait gives the job time to complete. On that basis, you will not be able to run the command asynchronously - you will have to run it synchronously and either accept that it takes several minutes, or use another process to trigger the script by processing a queue (as I suggested with SQL Server agent).

Collectives™ on Stack Overflow

Powershell script executed from SQL Server - stops working if put to background job

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related