SQL Agent Reporting

Mike Fal shows us how to use Powershell and T-SQL to get SQL Agent job status:

This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.

Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.

SMO’s a powerful thing.

Related Posts

Database Migration With dbatools

Jess Pomfret shows how easy it is to migrate databases from one SQL Server instance to another using dbatools: Now that there are no connections we can move the database.  Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the […]

Read More

Reading Giant Text Files With Powershell

Mark Broadbent has a quick Powershell script to read a selection from a giant text file: I recently ran into this very same problem in which the bcp error message stated: An error occurred while processing the file “D:\MyBigFeedFile.txt” on data row 123798766555678. Given that the text file was far in excess of notepad++ limits […]

Read More


November 2015
« Jan Dec »