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
– Ryan Bemrose, Commented Jun 4, 2015 at 6: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
– Diskdrive, Commented Jun 4, 2015 at 6:12

Ryan Bemrose · Accepted Answer · 2015-06-05 01:55:10Z

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 5, 2015 at 1:55

answered Jun 4, 2015 at 6:25

Ryan Bemrose

9,3461 gold badge45 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Diskdrive Over a year ago

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 Over a year ago

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?

Ryan Bemrose Over a year ago

Which PowerShell version are you using? Streams >=3 were added in Powershell 3.0, which shipped with Windows 8 and Windows Server 2012.

Diskdrive Over a year ago

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?

Ryan Bemrose Over a year ago

A search for 'Invoke-Sqlcmd verbose stream' turned up this question from 2010. Might be the answer you need. stackoverflow.com/questions/4511498/…

|

mjolinor · Accepted Answer · 2015-06-04 10:16:12Z

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 4, 2015 at 10:16

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Collectives™ on Stack Overflow

Why is my powershell output file blank?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related