question

Kev Riley avatar image
Kev Riley 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
sql-server-2005sql-agentpowershell
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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
0 Likes 0 ·
I only know it via powershell exe call, like cmd but will run the .ps1 file: powershell.exe .\myscript.ps1
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Create the powershell job as an external script and call that instead?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

external script as in batch file?
0 Likes 0 ·
That's what I was thinking, yes... Perhaps we could get @jimmyboo in to discuss the pros & cons!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.