asked
PowerShell job steps in SQL 2005
From SQL 2008 up, we've had the ability to specify a job step as a PowerShell step ([MSDN link][1]), but how can I do that for SQL 2005? [1]:
http://msdn.microsoft.com/en-us/library/cc280490(v=SQL.100).aspx
You can't make the call directly from the Agent in 2005. So you have to have the Agent call something else to run it, or, connect to the 2005 server from a 2008 machine, or, set up Powershell on the 2005 machine and then use remoting from a 2008 machine to do the work.
Have you tried making a template job for powershell and scripting that out in SSMS so you can then create a job of that type on SQL 2005? The system proc msdb.dbo.sp_add_jobstep is called with the `@subsystem` variable set to PowerShell. I don't know if that would be a valid value on 2005 - maybe that is an extra-special subsystem that started with 2008.
@WilliamD - the valid list of subsystems is given by `sp_enum_sqlagent_subsystems`, which on 2005 doesn't include PowerShell :( - I guess you're right that that subsystem was added in 2008 - but there still must be a way of executing PS scripts
Create the powershell job as an external script and call that instead?
That's what I was thinking, yes... Perhaps we could get @jimmyboo in to discuss the pros & cons!
