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
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.
4>1 should have been &4>&1If 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"
'$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 *>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
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.
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
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
Invoke-Sqlcmd. I believe it is merely a wrapper around theSQLCMD.exeutility. I sometimes prefer SQLCMD's simplistic output over the PowerShell object. Just depends on what you are trying to do.