通过 SQL 运行 Powershell 脚本

Running Powershell scripts through SQL

提问by furnace

I have a script that runs Invoke-SQLCmd against a SQLServer called Server1. Data that is collected from that is passed along to another script that is fired off against Server2 and the results are inserted back into a table on Server 1. On every Invoke-SQLCmd I have used the -user -password with an account that has sa permissions on both systems.

我有一个针对名为 Server1 的 SQLServer 运行 Invoke-SQLCmd 的脚本。从中收集的数据传递给另一个脚本，该脚本针对 Server2 触发，并将结果插入回服务器 1 上的表中。在每个 Invoke-SQLCmd 上，我都使用了 -user -password两个系统的权限。

When i run the script from the command shell or from the Poershell ISE my data is inserted into the table and every thing works fine; When i run it from within SQL nothing happens. I get no outputs ("null" is returned) when i use xp_cmdshell as below.

当我从命令 shell 或 Poershell ISE 运行脚本时，我的数据被插入到表中，并且一切正常；当我从 SQL 中运行它时，什么也没有发生。当我使用 xp_cmdshell 时，我没有得到任何输出（返回“null”），如下所示。

xp_cmdshell 'powershell.exe -file c:\script.ps1 -ExecutionPolicy Unrestricted'

I have put it into a SQLjob and used a proxy account that links to my domain account that has admin rights on both boxes yet still no results recorded in the job history and no data in my table on Server1.

我已将其放入 SQLjob 并使用代理帐户链接到我的域帐户，该帐户在两个框上都具有管理员权限，但作业历史记录中仍未记录结果，Server1 上的表中也没有数据。

What am i doing wrong? surely this should work if it works from the ISE?

我究竟做错了什么？如果它从 ISE 工作，这肯定应该工作吗？

回答by Chad Miller

I haven't had any problem doing this and even created a couple of blog posts:

我这样做没有任何问题，甚至创建了几篇博客文章：

http://sev17.com/2009/04/05/executing-powershell-in-sql-server (mirror)

http://sev17.com/2009/04/05/executing-powershell-in-sql-server（镜像）

http://sev17.com/2010/11/29/executing-powershell-in-sql-server-redux (mirror)

http://sev17.com/2010/11/29/executing-powershell-in-sql-server-redux（镜像）

The one thing that I'm doing differently is using the -command parameter with a file name instead of -file, but that shouldn't make a difference. I'm also enclosing the file name in double quotes but this shouldn't make a difference either if the script file has not spaces in the file path.

我正在做的一件事是使用带有文件名的 -command 参数而不是 -file，但这应该没有区别。我还将文件名用双引号括起来，但是如果脚本文件在文件路径中没有空格，这也不应该有什么不同。

Outside of that I would need to see what your script is doing. For instance is it connecting to other machines? Can you run a simple command in powershell like 'powershell -command get-command'?

除此之外，我需要看看你的脚本在做什么。例如它是否连接到其他机器？你能在 powershell 中运行一个简单的命令，比如“powershell -command get-command”？

回答by furnace

It would seem that i was not loading the snapins correctly. Although my SQLsnapins were loaded for the first session it wasnt passing it to the second PS script that was running invoke-SQLcmd. My second script did add the cmdlet snapin but that may not have been enough.

我似乎没有正确加载管理单元。虽然我的 SQLsnapin 是为第一个会话加载的，但它并没有将它传递给运行 invoke-SQLcmd 的第二个 PS 脚本。我的第二个脚本确实添加了 cmdlet 管理单元，但这可能还不够。

It should have worked but for whatever reason, adding the script block from here fixed it. :/

它应该有效，但无论出于何种原因，从此处添加脚本块修复了它。：/

http://msdn.microsoft.com/en-us/library/cc281962.aspx

http://msdn.microsoft.com/en-us/library/cc281962.aspx

Thanks to those that responded.

感谢那些做出回应的人。

回答by JasonS

What am i doing wrong?

我究竟做错了什么？

I think there is an error in the example provided. I would have expected:

我认为提供的示例中存在错误。我会期望：

xp_cmdshell 'powershell.exe -ExecutionPolicy Unrestricted -file c:\script.ps1'

Because: "Filemust be the last parameter in the command, because all characters typed after the Fileparameter name are interpreted as the script file path followed by the script parameters and their values."

因为：“ File必须是命令中的最后一个参数，因为在File参数名称之后键入的所有字符都被解释为脚本文件路径，后跟脚本参数及其值。

Source: PowerShell.exe Command-Line Help

来源：PowerShell.exe 命令行帮助