通过SQL运行Powershell脚本 [英] Running Powershell scripts through SQL

问题描述

我有一个脚本对名为Server1的SQLServer运行Invoke-SQLCmd.从中收集的数据将传递到另一个针对Server2触发的脚本，然后将结果插入回到Server 1上的表中.在每个Invoke-SQLCmd上，我都使用-user -password和一个具有sa的帐户两个系统上的权限.

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.

当我从命令外壳程序或Poershell ISE运行脚本时，我的数据将插入表中，并且一切正常.当我从SQL中运行它时，什么也没有发生.当我使用xp_cmdshell时，我没有输出(返回"null").

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.

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

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

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.

我在做什么错?如果可以在ISE上使用，肯定可以正常工作吗?

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

推荐答案

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

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

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

我正在做的另一件事是使用带有文件名而不是-file的-command参数，但这没有什么区别.我还将文件名括在双引号中，但是如果脚本文件的文件路径中没有空格，也不会造成任何影响.

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.

除此之外，我还需要查看您的脚本在做什么.例如，它是否连接到其他机器?您可以在Powershell中运行一个简单的命令，例如"powershell -command get-command"吗?

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'?

这篇关于通过SQL运行Powershell脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！