Using PowerShell with SQL Agent 2005

Today I needed to get a PowerShell script scheduled as a task in SQL Agent for SQL Server 2005. SQL 2008 natively supports running PowerShell, but all 2005 can do is run either T-SQL or CmdExec.

My first approach was to copy the path of the PowerShell executable from the start menu shortcut  - “%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe”

That refused to work giving a really helpful “The step failed” error message.

Trying a few variations I eventually figured out that CmdExec does not expand environment variables. So either rely on the fact that PowerShell.exe is probably in the path, or specify the full path. My working version is:

c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe -file "c:\tmp\test1.ps1" -ExecutionPolicy Unrestricted

If you need PowerShell to talk to Analysis Services, then Darren Gosbell’s powerSSAS (with versions for SSAS 2005 and 2008) might be useful too.

More WHS Backup options

Some updates on options for online backup of Windows Home Server:

• For a brief period it looked like Mozy would be releasing a WHS client but that turns out not to be the case. I’d let my free trial with KeepVault lapse in anticipation of the Mozy product being released. Oh well :-(
• Found Backblaze - ($US5/month unlimited) but doubt they work with WHS. Casey from the Backblaze team responded to an email query saying “..don’t have any plans for supporting Server OS like Windows Home Server”
• Others have recommended CrashPlan, and certainly their prices are competitive with Mozy and Carbonite. Whilst it isn’t officially supported with WHS, at least it gets a mention on their support site. Poster “Scott” goes into more detail on how he got it working with WHS.

Rob Farley on Talking Shop Down Under

LobsterPot Solutions’ very own Rob Farley is this week’s guest on the Talking Shop Down Under podcast!

Since my last post about podcasts I’m listening to, I’ve now added the following to my collection:

• Talking Shop Down Under – Interviews with Australian developers
• The Home Server Show – Latest on Windows Home Server
• FranklySpeaking – Microsoft Australia’s Andrew Coates and Michael Kordahi
• Elegant Code – Quiet since November 2009. One contributor now appears to be involved in PluralCast
• Daily Audio Bible – Brian Hardin reads through the entire Bible in one calendar year.