Scheduling a PowerShell Script Using SQL Server Agent

  • VishnuGupthanSQLPowershellDBA

    Ten Centuries

    Points: 1022

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

  • dave.rogers 954

    SSC Enthusiast

    Points: 184

    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: 2878

    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: 1022

    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: 1022

    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

    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.

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

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