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
more ▼

asked Oct 04, 2010 at 03:26 AM in Default

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
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.
more ▼

answered Oct 04, 2010 at 05:16 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Create the powershell job as an external script and call that instead?
more ▼

answered Oct 04, 2010 at 03:37 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

external script as in batch file?
Oct 04, 2010 at 03:39 AM Kev Riley ♦♦

That's what I was thinking, yes...

Perhaps we could get @jimmyboo in to discuss the pros & cons!
Oct 04, 2010 at 03:54 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 04, 2010 at 03:32 AM

WilliamD gravatar image

25.9k 17 19 41

@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
Oct 04, 2010 at 03:38 AM Kev Riley ♦♦
I only know it via powershell exe call, like cmd but will run the .ps1 file: powershell.exe .\myscript.ps1
Oct 04, 2010 at 03:42 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 04, 2010 at 03:26 AM

Seen: 2543 times

Last Updated: Oct 04, 2010 at 03:26 AM