I’m not a big Powershell user, but sometimes it is useful. Recently I decided to setup a SQL Agent Job to execute all .SQL script files found in a particular folder. I used a Powershell step and it seemed pretty easy. However, I noticed that when I used the out-File parameter that I only got the output from SELECT statements in the file; no PRINT statements, no errors. Not really very helpful (where did my errors go?). So I started digging…and found that this is by design. Ugh.
More digging…I found this post:
which led to this post:
And there was my answer; I have to redirect the output and specify a redirect operator in the command (there are 7 in the above post to choose from). I removed the out-File parameter and added a redirector. I decided to use the “*” to redirect everything. The invoke command now looks like this and I get errors, etc. in the output. This makes it much easier to run scripts during off-hours while capturing all success and failure messages:
# I first set my input file name and output file name as variables invoke-sqlcmd -QueryTimeout 0 –ServerInstance "MyInstanceName" -Database "MyDatabaseName" -InputFile $f.fullname -Verbose *> $out