从SQL Server执行的Powershell脚本 - 如果放到后台作业，则停止工作

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

我执行PowerShell脚本以在Office365中创建文件夹和收件箱规则。

The script works fine when called from SQL Server.

从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(?)

但是，如果我将代码放入函数并使用Start-Job调用，那么它将从SQL Server停止工作 - 但是如果从本地机器执行它会工作（？）

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

我真的希望代码在后台运行，因为它需要2-3分钟才能完成。

Any suggestions as to what I'm doing wrong?

关于我做错了什么的任何建议？

This is code that works from SQL Server:

这是从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):

这是我使用ScriptBlock的代码（不能从sql运行，但可以在本地机器/服务器上运行）：

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 

1 个解决方案

#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.

更常规的方法可能是将您要创建的O365文件夹名称添加到数据库中的表中，而不是使用PowerShell作业异步处理脚本，而数据库中的表又用于驱动执行PowerShell的SQL Server代理作业脚本。 SQL Server代理作业可以按计划轮询表以创建新文件夹。您可能仍需要从xp_cmdshell触发脚本，因为SQL Server代理作业步骤可以很复杂地进行参数化。

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:

如果您更喜欢使用您正在使用的方法，可能调试此方法的唯一方法是通过修改脚本来检索PowerShell作业的输出，以便它执行以下操作：

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