Powershell Invoke-Sqlcmd capture verbose output

Question

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!"

score 19 · Answer 1 · edited Jun 20 '20 at 09:12

19

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 28 '14 at 16:59

Jana Sattainathan

807
8
12

score 12 · Accepted Answer · answered Dec 24 '10 at 00:34

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

score 10 · Answer 3 · answered Dec 22 '10 at 17:06

10

Please try:

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

I found it at

answered Dec 22 '10 at 17:06

bernd_k

11,558
7
45
64

score 4 · Answer 4 · answered Dec 19 '14 at 20:09

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.

Ugh! sorry so long. Looks like my ampersands did not make it during the paste. The `4>1` should have been `&4>&1` — jrobiii, Apr 21 '16 at 13:48

score 3 · Answer 5 · answered Oct 19 '12 at 01:46

3

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

answered Oct 19 '12 at 01:46

Steve

31
1

How does that help the OP pipe verbose output to a file? – Jon Lin Oct 19 '12 at 01:58
1

does not work with backup: ``-Query "BACKUP DATABASE [$p2] TO DISK=`'$p5\$p6`' WITH NOFORMAT, NOINIT, NAME = `'$p2-Full Database Backup`', SKIP, NOREWIND, NOUNLOAD, STATS = 10" -verbose | Out-File -filePath "$p5\info.txt" but works with *> – Sasha Bond Nov 09 '21 at 19:49

score 3 · Answer 6 · answered Nov 16 '17 at 09:06

3

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

answered Nov 16 '17 at 09:06

ExploringApple

1,348
2
17
30

score 3 · Answer 7 · answered Mar 07 '19 at 20:48

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.

Ries Vriend · Answer 8 · 2016-03-04T12:28:12.993

2

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

edited Mar 04 '16 at 12:28

answered Mar 04 '16 at 12:08

Ries Vriend

977
7
9

score 1 · Answer 9 · answered Dec 23 '10 at 03:32

1

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

answered Dec 23 '10 at 03:32

Chad Miller

40,127
3
30
34

1

Simmo: I've done it by using start-transcript and stop-transcript... it seems to capture everything that would have gone to the terminal – Robert S Ciaccio Dec 23 '10 at 04:57
Good idea. I hadn't thought of start-transcript. – Chad Miller Dec 23 '10 at 13:05

score 1 · Answer 10 · answered Mar 15 '20 at 01:09

1

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

answered Mar 15 '20 at 01:09

Albert

356
4
7

score 0 · Answer 11 · answered Jul 24 '19 at 12:52

0

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

answered Jul 24 '19 at 12:52

Xavius Pupuss

160
1
6

Powershell Invoke-Sqlcmd capture verbose output

11 Answers11

Linked

Related