Administration

Invoke-SQLCMD lesson learned

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:

https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2015/10/23/powershell-invoke-sqlcmd-verbrose-output-to-a-file

which led to this post:

https://technet.microsoft.com/en-us/library/hh847746.aspx

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s