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