By: Tim Smith | Last Updated: 2016-03-03 | Comments (1) | PowerShell
One question I get asked a lot is where I run PowerShell scripts. The answer varies by the specific situation, environment, and purpose. In this tip I look at three convenient ways to call PowerShell scripts, as well as a fourth way that includes other applications which may be the appropriate place to call them for your environment.
Run PowerShell Scripts with a SQL Server Agent Job
The SQL Server Job Agent allows you to run PowerShell scripts when you select this option. One thing to watch for is the version of SQL Server and some functionality of PowerShell scripts, which may be incompatible. I've run into errors with PowerShell scripts running from SQL Server agent because of incompatibility.
Consider that the user running this job will need permissions to what the script does. Relative to how you configure security (or the environment's security overall), you may not want a user to run PowerShell scripts.
Run PowerShell Scripts with PowerShell ISE
PowerShell ISE is a slightly more interactive scripting tool than a command window because in versions 3.0 and above we have access to intellisense and the coloring of a script allows a coder to quickly determine what's wrong or what needs to be added.
Since I repeat many scripts and calls to scripts, I can reference a full list of functions within a script (code block one below) or I can reference related functions in multiple script sources (code block two below).
### Referencing one script with many functions: . D:\PS\Functions.ps1
### Referencing multiple scripts with related functions: . D:\PS\SMOFunctions.ps1 . D:\PS\RMOFunctions.ps1
A couple of points:
- Any daily and repetitive task is useful here. Consider getting the last date of a statistics update for a table, or shrinking a log. Once the function is coded, if automation is out of the question (if), then calling the function with the appropriate parameters quickly solves the issue.
- This approach is for live function calls, not set function calls. In the latter, I prefer related code together.
- Consider the use of configuration tables and (or) files, as these can allow for some of the live scripts to be saved and called without parameters. I can update the configuration files or tables and still call the same functions with the changed parameters.
Run PowerShell Scripts with Windows Task Scheduler
The below screenshot is from a home edition of Windows 7, but Task Scheduler comes with Windows Server as well. Task Scheduler allows scheduling tasks, such as exe programs, PS scripts, etc.
To run a PowerShell script, set the appropriate name and time, and under the Actions tab, put "PowerShell.exe" in the Program/script: block and in the Add arguments (optional): block, type -file "D:\PS\MyPSFile.ps1" with the location of the PowerShell file inside the quotations. Like other approaches, consider what user is running this along with what permissions that user has.
Run PowerShell Scripts with an Application
TeamCity and many other applications can call PowerShell scripts, in addition to many environments using C# to call PowerShell scripts. PowerShell scripts can also be built in modules, and the modules can be referenced and accessed. In these environments, the preference is to use these tools to call them and we'll seldom run into a situation where we must fully re-write the ones we use.
- First, know the security of your environment and the server you want to run scripts.
- If doing daily tasks with scripts, consider function lists - whether related or combined - to allow for easy completion when these tasks arise.
- For automation, consider running scripts in a scheduled program, such as TeamCity or Task Scheduler.
- Check out these other PowerShell tips
Last Updated: 2016-03-03
About the author
View all my tips