Hopefully karma will repay me for this post.  I always visit so many blogs for general perusal but mostly for fixing issues I’m running into at the time.  Maybe I can save some poor soul out there some time and energy looking through Googled-Results-Land.  First of all…I’m really digging Powershell.  Since reading details of what was to come in SQL Server 2008 I was intrigued.  Then when I started wading through it and creating scripts, I dig it even more.  As with everything though I ran into some bumps in the road.

I was setting up some Powershell scripts to run through the SQL Server Agent for the first time ever.  I tested them, and tweaked them, and tested some more.  Hooray I’m done, let’s really quickly schedule this bad boy to run through the Agent….what tha?  I first tried to use the Powershell subsystem since it was the cool new toy in SQL Server 2008.  Well I get the error:  This host does not support transcription.  Ok…appears to be a bug as far as I can tell so I move on and try issuing the command to run the script via cmdexe.  I got to thinking that would be better anyway so it would pull in changes to the script and I only have to update the script file and not remember to update the syntax in the job step (I’ve been bitten by that one before).  Ok so moving on, still can’t get it to work right that way either.  Ok so what next, hmm, what is this tee-object thing?  That’ll work and even better so as it will pull in files to be deleted without the whatif…fantastic!  Doing my testing and it’s working great after I figure out the syntax of the tee-object -variable does not use the $ like you’d think it would.  Ok fabulous, lets add multiple instances into the mix…wait…oh no don’t tell me.  It seems tee-object at this time does not have -append ability.  Dangit!  Ok after going to vote to add this into next version of Powershell (I’m using 1.0 by the way)  I have to think again.  How am I going to do the logging for this script looping through multiple instances through the Agent *banging head on desk*?  Ok out-file does have ability to append.  Then the skies part, the sun shines through, and the angels sing 🙂  Below is what I had to do to get logging to work with a script that is scheduled with the SQL Server agent:

$now = Get-Date
$logfile = “c:\logdirectory\LogName-” + $now.ToString(“MMddyyy”) + “.log”

Stuff doing | tee-object -var temp | other stuff doing (ie. remove-item)
$temp | out-file -filepath $logfile

Then on the next iteration and any subsequent ones you’ll have to add the -append to your out-file operation.  I also included my way to add a timestamp to the log as well as that took me some digging into as well.  Double karma brownie points perhaps?