CODEWITHSUNDEEP
sql-serverpowershellsql-server-2008-r2sqlcmd
Diskdrive
Diskdrive

Reputation: 18825

Why is my powershell output file blank?

Sorry, I'm new to Powershell so I'm obviously missing something fairly fundamental but I can't seem to figure this out.

I've got a powershell script calling a SQL script. I can get the script to run but I would also like to store the output in a log file.

This is my powershell script

Add-PSSnapin SqlServerCmdletSnapin100 
Add-PSSnapin SqlServerProviderSnapin100

Invoke-Sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' | Out-file -filepath "testlog.txt"

And this is my testscript.sql

PRINT 'HELLO WORLD'

At the moment, it creates the testlog.txt file but it is blank.

What am I doing wrong?

Upvotes: 0

Views: 4193

Answers (2)

Ryan Bemrose
Ryan Bemrose

Reputation: 9266

It seems the data you want is in the verbose data stream. In PowerShell 3.0 and above, you can redirect the verbose stream (4) to the stdout stream (1) before it can be sent to Out-File.

Invoke-Sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' -verbose 4>&1 | Out-file -filepath "testlog.txt"

See Get-Help about_redirection for the explanation of different streams.

See also Powershell: Out-File for discussion about redirecting error and verbose streams to Out-File.

Unfortunately, PowerShell 2.0 only supports redirection of stream 0,1,2. You can use a [System.Management.Automation.PowerShell] object to get at the other streams. See the accepted answer to Powershell Invoke-Sqlcmd capture verbose output

Upvotes: 3

mjolinor
mjolinor

Reputation: 68273

You can save the Verbose (and all the other console output) to a file without polluting your output stream with Start-Transcript

Upvotes: 1

Related Questions