sql server - Why is my powershell output file blank? -

sorry, i'm new powershell i'm missing fundamental can't seem figure out.

i've got powershell script calling sql script. can script run store output in log file.

this powershell script

add-pssnapin sqlservercmdletsnapin100  add-pssnapin sqlserverprovidersnapin100  invoke-sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' | out-file -filepath "testlog.txt" 

and testscript.sql

print 'hello world' 

at moment, creates testlog.txt file blank.

what doing wrong?

it seems data want in verbose data stream. in powershell 3.0 , above, can redirect verbose stream (4) stdout stream (1) before can sent out-file.

invoke-sqlcmd -inputfile "testscript.sql" -serverinstance '.\sql2008' -verbose 4>&1 | out-file -filepath "testlog.txt" 

see get-help about_redirection explanation of different streams.

see powershell: out-file discussion redirecting error , verbose streams out-file.

unfortunately, powershell 2.0 supports redirection of stream 0,1,2. can use [system.management.automation.powershell] object @ other streams. see accepted answer powershell invoke-sqlcmd capture verbose output