blocks|key|4053740|text|由于通过PowerShell主机的本机构造很难捕获详细的输出，因此您可以始终使用对PowerShell+object的编程访问。然后，您可以访问这五种不同的信息流：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4053741|>+$ps+=+[PowerShell]::Create()
>+[ref]$e+=+New-Object+System.Management.Automation.Runspaces.PSSnapInException
>+$ps.Runspace.RunspaceConfiguration.AddPSSnapIn(+"SqlServerCmdletSnapin100",+$e+)+%7C+Out-Null
>+$ps.AddCommand(+"Invoke-Sqlcmd"+).AddParameter(+"Query",+"Print+'hello+world'"+).AddParameter(+"Verbose"+)
>+$ps.Invoke()
>+$ps.Streams

Error++++:+{}
Progress+:+{}
Verbose++:+{hello+world}
Debug++++:+{}
Warning++:+{}

>+$ps.Streams.Verbose+%7C+%25+{+$_.Message+%7C+Out-File+-Append+D:\SqlLog.txt+}
>+cat+D:\SqlLog.txt

hello+world|code-block|syntax|javascript|4053742|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/dd144526(v=VS.85).aspx)^0|15|H|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Since capturing verbose output is not something one can do easily through the native constructs of the PowerShell host, you can always use the programatic access to the <a href="http://msdn.microsoft.com/en-us/library/dd144526(v=VS.85).aspx" rel="noreferrer">PowerShell object</a>. You can then gain access to the five different streams of information:

<pre><code>&gt; $ps = [PowerShell]::Create()
&gt; [ref]$e = New-Object System.Management.Automation.Runspaces.PSSnapInException
&gt; $ps.Runspace.RunspaceConfiguration.AddPSSnapIn( "SqlServerCmdletSnapin100", $e ) | Out-Null
&gt; $ps.AddCommand( "Invoke-Sqlcmd" ).AddParameter( "Query", "Print 'hello world'" ).AddParameter( "Verbose" )
&gt; $ps.Invoke()
&gt; $ps.Streams

Error : {}
Progress : {}
Verbose : {hello world}
Debug : {}
Warning : {}

&gt; $ps.Streams.Verbose | % { $_.Message | Out-File -Append D:\SqlLog.txt }
&gt; cat D:\SqlLog.txt

hello world
</code></pre>

blocks|key|4053810|text|根据Capture+Warning,+Verbose,+Debug+and+Host+Output+via+alternate+streams的说法|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4053811|4053812|+...if我想在脚本中捕获详细的输出：|blockquote|4053813|4053814|stop-process+-n+vd*+-verbose+4>&1+>+C:\Logs\StoppedProcesses.log|4053815|4053816|所以，你会做类似这样的事情|4053817|(Invoke-Sqlcmd+-Query+"PRINT+'Hello+World!';"+-ServerInstance+$Server+-verbose)+4>+c:\temp\myoutput.txt|code-block|syntax|javascript|4053818|其中4是“详细”流。|4053819|entityMap|0|LINK|mutability|MUTABLE|url|https://connect.microsoft.com/PowerShell/feedback/details/297055/capture-warning-verbose-debug-and-host-output-via-alternate-streams^0|2|1X|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|D|3|-4|5|6|7|16|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|17|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|18|8|@]|9|@]|C|$]]|$1|I|3|J|5|6|7|19|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1B|8|@]|9|@]|C|$]]|$1|N|3|O|5|P|7|1C|8|@]|9|@]|C|$Q|R]]|$1|S|3|T|5|6|7|1D|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]]]

According to <a href="https://connect.microsoft.com/PowerShell/feedback/details/297055/capture-warning-verbose-debug-and-host-output-via-alternate-streams" rel="noreferrer">Capture Warning, Verbose, Debug and Host Output via alternate streams</a>:
<blockquote>
...if I wanted to capture verbose output in a script:
stop-process -n vd* -verbose 4&gt;&amp;1 &gt; C:\Logs\StoppedProcesses.log
</blockquote>
So, you would do something like
<pre><code>(Invoke-Sqlcmd -Query &quot;PRINT 'Hello World!';&quot; -ServerInstance $Server -verbose) 4&gt; c:\temp\myoutput.txt
</code></pre>
Where 4 is the &quot;verbose&quot; stream.

blocks|key|4053661|text|请尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4053662|Invoke-Sqlcmd++-Query+"PRINT+'Hello+World!';"+-ServerInstance+$Server+-verbose++>+D:\SqlLog.txt++2>&1|code-block|syntax|javascript|4053663|I+found+it+at+|offset|length|4053664|entityMap|0|LINK|mutability|MUTABLE|url|http://www.techotopia.com/index.php/Windows_PowerShell_1.0_Pipes_and_Redirection^0|0|0|0|E|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Please try:

<pre><code>Invoke-Sqlcmd -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose &gt; D:\SqlLog.txt 2&gt;&amp;1
</code></pre>

<a href="http://www.techotopia.com/index.php/Windows_PowerShell_1.0_Pipes_and_Redirection" rel="noreferrer">I found it at </a>

blocks|key|4053858|text|因此，我知道这有点不相关，但我需要在一个单独的变量中捕获"RAISERROR“和"PRINT”语句，而不是行数据。下面是我是如何做到的：$Messages+=+%25{+$Rows+=+Invoke-Sqlcmd++-Query+"PRINT+'Hello+World!';"+-ServerInstance+$Server+-verbose}+4>1|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4053859|来自Invoke-SqlCmd的行数据在$Rows使用的STDOUT上。冗余输出在管道上继续，并被重定向到STDOUT+(由于$Rows是空的)。STDOUT中唯一需要交给$Messages的就是详细的输出。呼！|4053860|要获取现在在$Messages.Message上的数据，这就有点复杂了。|4053861|entityMap^0|1W|2Y|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|P|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|K|$]]

So I know this is a little unrelated, but I needed to capture "RAISERROR" and "PRINT" statements in a separate variable then the row data. Here is how I did it:<code>
$Messages = %{ $Rows = Invoke-Sqlcmd -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose} 4&gt;1</code>

It row data from Invoke-SqlCmd is on STDOUT which is consumed by $Rows the verbose output continues on the pipe and is redirected into STDOUT (which, thanks to $Rows is empty). The only thing in STDOUT to hand to $Messages is the Verbose output. Whew!

It's a little further complicated in that to get to the data it's now on $Messages.Message.

blocks|key|389823|text|如果只需要打印语句，只需在命令末尾添加-Verbose即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|389824|例如：Invoke-Sqlcmd+-Query+$Query+-ServerInstance+$Server+-Database+"master“-Verbose|389825|可以与Out-File结合使用：-|389826|Invoke-Sqlcmd+-InputFile+"C:\MyFolder\TestSQLCmd.sql“%7C输出文件-filePath+"C:\MyFolder\TestSQLCmd.rpt”|389827|http://technet.microsoft.com/en-us/library/cc281720.aspx|offset|length|389828|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|1K|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@$J|X|K|Y|1|Z]]|A|$]]|$1|L|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|I]]]]

If you just want print statements, just add -Verbose at the end of the command.

Eg.
Invoke-Sqlcmd -Query $Query -ServerInstance $Server -Database "master" -Verbose

May be able to use that in combination with Out-File:-

Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"

<a href="http://technet.microsoft.com/en-us/library/cc281720.aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/cc281720.aspx</a>

blocks|key|389948|text|对我来说，以下命令起作用了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|389949|invoke-sqlcmd+-inputfile+"C:\cfn\scripts\set-tempdb.sql"+-Verbose+*>+"C:\cfn\log\set-tempdb.log"|code-block|syntax|javascript|389950|我在Windows+2016和SQL+Enterprise+2016上使用Powershell+5.1|389951|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For me the below command worked:

<pre><code>invoke-sqlcmd -inputfile "C:\cfn\scripts\set-tempdb.sql" -Verbose *&gt; "C:\cfn\log\set-tempdb.log"
</code></pre>

I am using Powershell 5.1 on Windows 2016 With SQL Enterprise 2016

blocks|key|389935|text|在Powershell3中，*>>将所有输出(包括详细)附加到一个文件中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|389936|'Running+sql+query:'
Invoke-Sqlcmd+-ServerInstance+.\sql+-Verbose+-Query+"Print+'hello+world'"+*>>+c:\temp\sql.log
'Display+file+content:'
Get-Content+c:\temp\sql.log+%7C+Out-Host|code-block|syntax|javascript|389937|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In Powershell 3, *>> appends all output (including verbose) to a file

<pre><code>'Running sql query:'
Invoke-Sqlcmd -ServerInstance .\sql -Verbose -Query "Print 'hello world'" *&gt;&gt; c:\temp\sql.log
'Display file content:'
Get-Content c:\temp\sql.log | Out-Host
</code></pre>

blocks|key|389995|text|我认为有一个更简单的解决方案。我正在努力将详细的流实时输出到控制台，并将其保存为变量，以便在脚本中进一步下游使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|389996|Invoke-Sqlcmd+-Query+$Query+`+++++++++++++++-Database+$Database+`+++++++++++++++-ServerInstance+$ServerInstance+`+++++++++++++++-Verbose+4>&1+%7C+Tee-Object+-Variable+output|offset|length|style|CODE|389997|4>&1将详细流重定向到主输出流Tee-Object允许管道输出到两个不同的位置，在本例中，invoke-sqlcmd输出到控制台，但也保存到一个变量。|389998|entityMap^0|0|0|4R|0|0|4|G|A|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

I think there is an easier solution. I was struggling to output both the verbose stream to the console real-time and also save it as a variable to use further downstream in my script.

<code>Invoke-Sqlcmd -Query $Query `
 -Database $Database `
 -ServerInstance $ServerInstance `
 -Verbose 4&gt;&amp;1 | Tee-Object -Variable output</code>

<code>4&gt;&amp;1</code> redirects the verbose stream to the main output steam
<code>Tee-Object</code> allows the pipeline to output to two different places, in this case the invoke-sqlcmd is outputting to console but is saving to a variable as well.

blocks|key|1546339|text|捕获详细输出是一件棘手的事情。我在这里看到过关于这个话题的唯一帖子：http://www.nivot.org/2009/08/19/PowerShell20AConfigurableAndFlexibleScriptLoggerModule.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1546340|一个更简单的选择是不使用verbose并转换为write-output。您可以修改invoke-sqlcmd2函数以使用写输出而不是http://poshcode.org/2279|1546341|entityMap|0|LINK|mutability|MUTABLE|url|http://www.nivot.org/2009/08/19/PowerShell20AConfigurableAndFlexibleScriptLoggerModule.aspx|1|http://poshcode.org/2279^0|Y|2J|0|0|1U|O|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

Capturing verbose output is tricky. The only post I've seen on this topic is here:
<a href="http://www.nivot.org/2009/08/19/PowerShell20AConfigurableAndFlexibleScriptLoggerModule.aspx" rel="nofollow">http://www.nivot.org/2009/08/19/PowerShell20AConfigurableAndFlexibleScriptLoggerModule.aspx</a>

An easier option would be to no use verbose and convert to write-output. You could modify invoke-sqlcmd2 function to use write-output instead <a href="http://poshcode.org/2279" rel="nofollow">http://poshcode.org/2279</a>

blocks|key|390050|text|我使用-IncludeSqlUserErrors和-ErrorVariable+truc的组合|type|unstyled|depth|inlineStyleRanges|entityRanges|data|390051|$result+=+Invoke-Sqlcmd+-ServerInstance+$instance+-Database+'master'+-Query+"select+@@version"+-IncludeSqlUserErrors+-ErrorVariable+truc

Write-Host+$truc|code-block|syntax|javascript|390052|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I use both combination of -IncludeSqlUserErrors and -ErrorVariable truc

<pre><code>$result = Invoke-Sqlcmd -ServerInstance $instance -Database 'master' -Query "select @@version" -IncludeSqlUserErrors -ErrorVariable truc

Write-Host $truc
</code></pre>

blocks|key|1546492|text|您可以将输出捕获到变量，而不是将其重定向到文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1546493|invoke-sqlcmd+"select+13.0/7.0"+%7Cout-string+-outvariable+abc
$abc|code-block|syntax|javascript|1546494|产生：|1546495|+Column1
+-------
1.857142|1546496|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can capture output to a variable instead of redirecting it to a file.

<pre><code>invoke-sqlcmd "select 13.0/7.0" |out-string -outvariable abc
$abc
</code></pre>

produces:

<pre><code> Column1
 -------
1.857142
</code></pre>

I'm trying to capture the verbose output from the Invoke-Sqlcmd in Powershell. Anyone got any ideas to do this:

i.e. 

<pre><code>Invoke-Sqlcmd -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose &gt; D:\SqlLog.txt
</code></pre>

The SqlLog.txt file should contain the text "Hello World!"

Powershell Invoke-Sqlcmd capture verbose output

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试捕获Powershell中的Invoke-Sqlcmd的详细输出。任何人有任何想法来做这件事：即Invoke-Sqlcmd  -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose  > D:\SqlLog.txtSqlLog.txt文件应该包含文本"Hello World!“

问Powershell Invoke-Sqlcmd捕获详细输出
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Powershell Invoke-Sqlcmd捕获详细输出EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Powershell Invoke-Sqlcmd捕获详细输出
EN