Powershell Invoke-Sqlcmd capture verbose output

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

i.e.

Invoke-Sqlcmd  -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose  > 开发者_运维问答D:\SqlLog.txt

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

According to Capture Warning, Verbose, Debug and Host Output via alternate streams:

...if I wanted to capture verbose output in a script:

stop-process -n vd* -verbose 4>&1 > C:\Logs\StoppedProcesses.log

So, you would do something like

(Invoke-Sqlcmd -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose) 4> c:\temp\myoutput.txt

Where 4 is the "verbose" stream.

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 PowerShell object. You can then gain access to the five different streams of information:

> $ps = [PowerShell]::Create()
> [ref]$e = New-Object System.Management.Automation.Runspaces.PSSnapInException
> $ps.Runspace.RunspaceConfiguration.AddPSSnapIn( "SqlServerCmdletSnapin100", $e ) | 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 | % { $_.Message | Out-File -Append D:\SqlLog.txt }
> cat D:\SqlLog.txt

hello world

Please try:

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

I found it at

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: $Messages = %{ $Rows = Invoke-Sqlcmd -Query "PRINT 'Hello World!';" -ServerInstance $Server -verbose} 4>1

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.

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"

http://technet.microsoft.com/en-us/library/cc281720.aspx

For me the below command worked:

invoke-sqlcmd -inputfile "C:\cfn\scripts\set-tempdb.sql" -Verbose *> "C:\cfn\log\set-tempdb.log"

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

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

'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 | Out-Host

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.

Invoke-Sqlcmd -Query $Query ` -Database $Database ` -ServerInstance $ServerInstance ` -Verbose 4>&1 | Tee-Object -Variable output

4>&1 redirects the verbose stream to the main output steam Tee-Object 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.

Capturing verbose output is tricky. The only post I've seen on this topic is here: http://www.nivot.org/2009/08/19/PowerShell20AConfigurableAndFlexibleScriptLoggerModule.aspx

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 http://poshcode.org/2279

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

invoke-sqlcmd "select 13.0/7.0" |out-string -outvariable abc
$abc

produces:

 Column1
 -------
1.857142

I use both combination of -IncludeSqlUserErrors and -ErrorVariable truc

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

Write-Host $truc