I’m going to demonstrate how simple is using PowerShell with SQL Server 2008 Provider and PSSnapins to work with SQL Server Agent jobs. I will cover the following areas:
1. Listing Jobs.
2. Starting jobs.
3. Monitoring Jobs.
You will become familiar with SQL Server 2008 PowerShell cmdlet “Invoke-SQLcmd” because this is the main cmdlet to make this work. I didn’t use any SMO components with these samples.
First, what is needed to run the following samples? Here’s some additional information:
## – requirement:
## – 1. Must have SQL Server 2008 in order the use the SQL Server provider and SQL PSSnapins.
## – 2. Use SQLPS.exe to run this script, or add the following code from mwories blog into
## your “WindowsPowerShell” folder in your User profile “My Document” folder. Here’s how:
## – 3. Results will be display on screen or add the code to output to a file. (" | Out-File …")
Second, here’s a brief description of the SQL Server 2008 PowerShell Cmdlet “Invoke-SQLcmd” copied from the PowerShell V2 help documentation using “Help Invoke-SQLcmd”: (it is self-explanatory)
Runs a script containing the languages and commands supported by the SQL Server sqlcmd utility. The languages supported are Transact-SQL and the XQuery syntax supported by the Database Engine.
Invoke-Sqlcmd also accepts many of the commands supported by sqlcmd, such as GO and QUIT. Invoke-Sqlcmd accepts the sqlcmd scripting variables, such as SQLCMDUSER. Invoke-Sqlcmd does not set sqlcmd scripting variables by default. Invoke-Sqlcmd does not support the sqlcmd commands primarily related to interactive script editing.
The commands not supported include : !!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.
The first result set the script returns is displayed as a formatted table. Result sets after the first are not displayed if their column list is different from the column list of the first result set. If result sets after the first set have the same column list, their rows are appended to the formatted table that contains the rows that were returned by the first result set.
Invoke-Sqlcmd does not return message output, such as the output of PRINT statements, unless you use the PowerShell -Verbose parameter.“
Third, from SQL Server, we are going to use the following T-SQL Stored-Procedures:
1. “sp_help_jobactivity” – This one will list all active and non-active jobs on the SQL Server.
2. “sp_start_job” – This procedure will start a SQL job.
Now, we are ready to start using PowerShell with our SQL Server jobs.
1 – Listing SQL Agent Jobs
Here I’m showing two ways to list SQL jobs and each one of the two procedures will give us very interesting results:
1. From the PowerShell console, navigate using “CD” to where the jobs are stored.
2. Use the Cmdlet “Invoke-SQLcmd” to execute the SQL Stored-Procedure “sp_help_jobactivity”.
With option #1, you are using the PSdrive “SQLServer\…” to navigate to the job and then will need to loop through the results to display both the Job and their job steps information.
Now, with option #2, we use the SQL Stored-Procedure to get the information about the jobs using one line of code.
2 – Start SQL Agent Jobs
This is a quick section because will be using the SQL Stored-Procedure “sp_start_job” to build our collection of .NET objects with one line.
This cmdlet will not return any information back to you. It will only start the job. So the next step is to monitor the running job.
3 – SQL Job Monitoring
Although, Listing and monitoring use the same Stored-Procedure “sp_help_jobactivity”, the only difference is that we are looking for the “status” value. When the job is running, the Job_Status will have a value of NULL, if the job fails the value is 0, and if the job ended successfully then the value is 1.
Reminder: Any time you create a .NET PSobject using the “$variable = value or PowerShell cmdlet or …”, this object is static and not dynamic. So, you need to refresh that .NET PSobject by running again the same lines that execute the Stored-Procedure “sp_help_jobactivity”:
Example: (second time )
See the results in the following image below. The first time running the two liner script, the dates and job_status statistics are set to NULL values. The second time running, the job completed successfully with dates showing and the job_status = 1.
Now, as you can possibly see, you can build this procedure to include:
1. Use the “ForEach” statements and loop through your SQL jobs.
2. Save the results of the jobs to a text file or you own table.
3. Build your own script SQL Job monitoring solution.
4. Execute/Start SQL jobs remotely from your desk.
This is just to name a few things.
Here’s some resource information I use for this blog:
Update PowerShell V1 to V2 on non-Window 7 and Windows 2008 R2 – Windows Management Framework (Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0)
Happy PowerShell Scripting!!!