从 2005 SQL Server 中的 SQL 代理作业运行脚本时，Powershell 成绩单为空
我有一个复杂的 Powershell 脚本，它作为 SQL 2005 Server 代理作业的一部分运行。该脚本工作正常，但它使用“Start-Transcript $strLogfile -Append”命令将其所有操作记录到脚本文件中。问题是成绩单总是空的。它添加页眉和页脚以指示记录正在开始和停止，但它实际上不记录任何内容。示例：
**********************
Windows PowerShell Transcript Start
Start time: 20100304173001
Username : xxxxxxxxxxxx\SYSTEM
Machine : xxxxx-xxx (Microsoft Windows NT 5.2.3790 Service Pack 2)
**********************
**********************
Windows PowerShell Transcript End
End time: 20100304173118
**********************
当我从命令提示符或启动 -> 执行脚本时运行一切正常。这是用于运行脚本的命令（与 SQL 代理作业的操作系统 CmdExec 步骤中使用的命令相同）
powershell.exe -File "c:\temp\Backup\backup script.ps1"
我首先认为它一定与在系统帐户（默认 SQL 代理帐户）下运行的脚本有关，但是即使当我尝试更改 SQL 代理以在我自己的个人帐户下运行时，它仍然会创建一个空白的记录。
在将 PowerShell 脚本作为 2005 SQL Server 代理作业的一部分执行时，有什么方法可以让 PowerShell 脚本正常工作吗？
I have a complex Powershell script that gets run as part of a SQL 2005 Server Agent Job. The script works fine, but it uses the "Start-Transcript $strLogfile -Append" command to log all of it's actions to a transcript file. The problem is that the transcript is always empty. It adds the header and footer to indicate that the transcript is starting and stopping, but it doesn't actually log anything. Example:
**********************
Windows PowerShell Transcript Start
Start time: 20100304173001
Username : xxxxxxxxxxxx\SYSTEM
Machine : xxxxx-xxx (Microsoft Windows NT 5.2.3790 Service Pack 2)
**********************
**********************
Windows PowerShell Transcript End
End time: 20100304173118
**********************
When I execute the script from a command prompt or start -> run everything works just fine. Here is the command used to run the script (same command used in the Operating system CmdExec step of the SQL Agent Job)
powershell.exe -File "c:\temp\Backup\backup script.ps1"
I first thought it must have something to do with the script running under the System account (default SQL Agent account), but even when I tried changing the SQL Agent to run under my own personal account it still created a blank transcript.
Is there any way to get PowerShell Transcripts to work when executing them as part of a 2005 SQL Server Agent Job?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我仍然不确定为什么 Powershell 成绩单是空的，但我们找到了解决方法。在 SQL 作业的 CmdExec 步骤下，有一个高级选项可将输出捕获到文件，该选项与“将输出附加到现有文件”选项相结合并使用 Logfile.rtf 扩展名，与 Powershell 转录内容大致相同。这样，从 Powershell 脚本打印到主机的任何内容（包括通过管道传输到“| out-host”的本机控制台可执行文件）都将被捕获在日志文件中。
I am still not sure why the Powershell Transcript is empty, but we found a workaround. Under the CmdExec step of the SQL Job there is an advance option to capture the output to a file, which combined with the "Append output to existing file" option and using a Logfile.rtf extension is about the same as the Powershell transcript. This way anything that gets printed to the host from the Powershell script (including native console executables piped to "| out-host") will be captured in the log file.
如果您的脚本使用本机命令（控制台 exe），Start-Transript 不会记录任何该输出。此问题已登录Connect，您可以对其进行投票。捕获所有输入的一种方法是使用 cmd.exe：
If your script uses native commands (console exes), Start-Transript does not log any of that output. This issue has been logged on Connect, you can vote on it. One way to capture all input is to use cmd.exe:
sqlps.exe 不实现某些方法，包括支持 write-host 的方法。这可以解释为什么在从 SQL Agent Powershell 作业步骤运行 sqlps.exe 时看不到使用 Start-Transcript 的输出。请参阅http://blogs.msdn.com/mwories/archive/2009/09/30/the-use-of-write-host-and-sql-server-agent-powershell-job -steps.aspx 了解更多信息。
sqlps.exe does not implement certain methods including the method that supports write-host. This may explain why you are not seeing output using Start-Transcript when running sqlps.exe from a SQL Agent Powershell jobstep. See http://blogs.msdn.com/mwories/archive/2009/09/30/the-use-of-write-host-and-sql-server-agent-powershell-job-steps.aspx for more information.