CODEWITHSUNDEEP
sqlsql-serverpowershell
methosmen
methosmen

Reputation: 23

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

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

Upvotes: 2

Views: 765

Answers (1)

Ed Harper
Ed Harper

Reputation: 21495

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

Upvotes: 1

Related Questions