Why is my powershell output file blank?

Question

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?

If you remove the `Out-file` command and run it, does the desired text show up in the console? — Ryan Bemrose, Jun 04 '15 at 06:06
@RyanBemrose : if i do this "Invoke-Sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' -verbose" i can get it to show on screen but not sure how to save it — Diskdrive, Jun 04 '15 at 06:12

Ryan Bemrose · Answer 1 · 2015-06-05T01:55:10.443

3

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

edited Jun 05 '15 at 01:55

answered Jun 04 '15 at 06:25

Ryan Bemrose

9,018
1
41
54

Yeah I tried that before, but all I get when I run it is this error "Ampersand not allowed. The & operator is reserved for future use; use "&" to pass ampersand as a string." Any ideas what this is? – Diskdrive Jun 05 '15 at 00:46
The weird thing is if I replace 4>&1 to 2>&1 then it works (although it's still blank). It looks like the data stream 4 is missing or something? – Diskdrive Jun 05 '15 at 01:31
Which PowerShell version are you using? Streams >=3 were added in Powershell 3.0, which shipped with Windows 8 and Windows Server 2012. – Ryan Bemrose Jun 05 '15 at 01:40
oh crap, I think I've only got version 2.0 and I won't be able to upgrade it as I need this script run by other people in the company that can't upgrade their machine. Is that a pre-3.0 way of doing this? – Diskdrive Jun 05 '15 at 01:44
A search for 'Invoke-Sqlcmd verbose stream' turned up this question from 2010. Might be the answer you need. https://stackoverflow.com/questions/4511498/powershell-invoke-sqlcmd-capture-verbose-output – Ryan Bemrose Jun 05 '15 at 01:47
thanks Ryan that was very helpful link. I'm able to get it to output the print statements now, but I'm still struggling it to return things like (1 row(s) affected) and whenever the script crashes, it just crashes without logging the error message but I'll investigate further. – Diskdrive Jun 09 '15 at 00:50

score 1 · Answer 2 · answered Jun 04 '15 at 10:16

1

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

answered Jun 04 '15 at 10:16

mjolinor

66,130
7
114
135

Why is my powershell output file blank?

2 Answers2