Scheduling a PowerShell Script Using SQL Server Agent

Question

Scheduling a PowerShell Script Using SQL Server Agent

VishnuGupthanSQLPowershellDBA

Ten Centuries

Points: 1210
More actions
May 21, 2018 at 10:06 pm

#341024

Comments posted to this topic are about the item Scheduling a PowerShell Script Using SQL Server Agent

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dave.rogers 954 SSC Enthusiast Points: 190 More actions · Answer 1

And even better, you can use PowerShell to create the scheduled task. This would also serve as documentation for the changes that you have just made. And better still, you could use the open source dbatools PowerShell module to make the task of creating a scheduled task even easier.

logitestus SSCrazy Points: 2928 More actions · Answer 2

Curious Question: Why use the PowerShell task over the CmdExec task? I know in your article you are calling the PowerShell executable, hence the use of the CmdExec task. I know from my own experiments the positives/negatives of using the CmdExec task over the PowerShell one; does anyone have a compelling reason for not using the PowerShell task?

Thanks!

VishnuGupthanSQLPowershellDBA Ten Centuries Points: 1210 More actions · Answer 3

dave.rogers 954 - Tuesday, May 22, 2018 2:03 AM
And even better, you can use PowerShell to create the scheduled task. This would also serve as documentation for the changes that you have just made. And better still, you could use the open source dbatools PowerShell module to make the task of creating a scheduled task even easier.

I know we can create scheduled tasks using powershell but I don't think it is there in PS 2.0 and below. (It started from 3.0 to my knowledge.)
Still many of the projects in the industry still have windows 2008\R2 OS which dont have PS version 3 installed.

Of course, u can use dbatools ps module , but just treat this as a simple and straight forward way for even a DBA with basic posh skills without importing modules and all.

VishnuGupthanSQLPowershellDBA Ten Centuries Points: 1210 More actions · Answer 4

logitestus - Tuesday, May 22, 2018 8:55 AM
Curious Question: Why use the PowerShell task over the CmdExec task? I know in your article you are calling the PowerShell executable, hence the use of the CmdExec task. I know from my own experiments the positives/negatives of using the CmdExec task over the PowerShell one; does anyone have a compelling reason for not using the PowerShell task?
Thanks!

Thanks for the question, Logitestus.
I have not experienced any issues so far implementing it using cmdexec. Am planning to write another post with the Powershell subsystem in near future in SQL Agent job.

Thanks
Vishnu

thomasstricker2001 SSC Veteran Points: 204 More actions · Answer 5

I think you could have spent a little more space on explaining changing the Admin privileges for running Powershell, since it is not possible to save the script and run the same in a Powershell format (*.ps1), without opening Powershell as Admin. Changing the security privileges for the Powershell user takes some prep work. Perhaps this is a topic for another Powershell article? I am looking at this topic from an advanced beginner level.