PowerShell Custom Class for Querying a SQL Server

Here is a handy custom class I created for PowerShell 5+ that can query an SQL database. The class creates a SQL connection using the System.Data.SqlClient.SqlConnection class in .Net, and gives you full access to this object for information or to manipulate parameters. The class can also query the list of tables and views available in the database to help you prepare your SQL query.

Here’s a quick how-to:

Instantiate the Class

Create an instance of the object. You can create an empty instance like this:


$SQLQuery = [SQLQuery]::new()

You can then specify the SQL Server name and Database name:


$SQLQuery.SQLServer = "SQL-01\Inst_SCCM"
$SQLQuery.Database = "CM_ABC"

And then add a query:


$SQLQuery.Query = "Select top 5 * from v_R_System"

Or you can pass these parameters when you create the object, like:


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC")

Or..


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC","Select top 5 * from v_R_System")

Execute the Query

Now execute the query to return the results:


$SQLQuery.Execute()

The result will be displayed in the console window, or you can pipe to GridView for easier viewing:


$SQLQuery.Execute() | Out-GridView

Grid1

The query results will be stored to the object in the Result parameter so you can retrieve them later.


$SQLQuery.Result

The results are stored in a DataTable format with rows and columns which is an ideal format for SQL query results.

Load a Query from a File

You can load in a SQL query from a “.sql” file like so, passing the location of the file to the method:


$SQLQuery.LoadQueryFromFile("C:\Scripts\SQLScripts\OSD_info.sql")

Then execute the query as before.

Change the Connection String

By default, the connection string will be created for you, but you can add your own custom connection string using the ConnectionString parameter:


$SQLQuery.ConnectionString = "Server=SQL-01\inst_sccm;Database=CM_ABC;Integrated Security=SSPI;Connection Timeout=5"

Timeout Values

There are two timeout parameters which can be set:


$SQLQuery.ConnectionTimeout
$SQLQuery.CommandTimeout

The ConnectionTimeout parameter is the maximum time in seconds PowerShell will try to open a connection to the SQL server.

The CommandTimeout parameter is the maximum time in seconds PowerShell will wait for the SQL query to execute.

Get a List of Views or Tables in the Database

The following two methods can be used to retrieve a list of views or tables in the database:


$SQLQuery.ListViews()
$SQLQuery.ListTables()

The views or tables will be stored to the parameter of the same name in the object, so you can retrieve them again later.

You can filter the list to search for a particular view or table, or group of. On the command line you could do:


$SQLQuery.ListViews()
$SQLQuery.Views.Rows | where {$_.Name -match "v_Client"}

Or as a one-liner:


$SQLQuery.ListViews().Where({$_.Name -match "v_Client"})

You could also output to GridView for filtering:


$SQLQuery.ListViews() | Out-GridView

Grid2

Hide the Results

By default, the Execute(), ListViews() and ListTables() methods will return the results to the console after execution. You can turn this off by setting the DisplayResults parameter to $False. This scenario may be useful for scripting where you may not wish to display the results right away but simply have them available in a variable.


$SQLQuery.DisplayResults = $False

Access the SQL Objects

You can access the SQLConnection object, or the SQLCommand object to view or set parameters, or execute any of the methods contained in those objects. These objects are only available once the SQL query has been executed.

SQLConnection

SQLCommand

You can re-use the same SQLQuery object to run other queries; only the results for the most recent query will be stored in the object itself.

SQLQuery Custom Class

New Free Tool: ConfigMgr Remote Compliance

Remote Compliance

Today I released a new free tool for ConfigMgr administrators and support staff.

ConfigMgr Remote Compliance can be used to view, evaluate and report on System Center Configuration Manager Compliance Baselines on a remote computer. It provides similar functionality to the Configurations tab of the Configuration Manager Control Panel, but for remote computers. It is a useful troubleshooting tool for remotely viewing client compliance, evaluating baselines, viewing the evaluation report or opening DCM log files from the client, without needing to access the client computer directly.

ConfigMgr Remote Compliance can be downloaded from here.

Source code for this application is available on GitHub and code contributions are welcome.

Deploying Custom Microsoft Office Templates with System Center Configuration Manager

Some time ago a wrote a blog describing a way to deploy custom templates for Microsoft Office applications using SCCM Compliance Settings. Since then, I have re-written the solution into something much more manageable as the previous incarnation was not very clearly defined in how to update templates, and involved some considerable admin overhead. This updated solution is much improved and better manages the lifecycle of your custom templates, including updating, adding and retiring templates. Much of this process is now automated using PowerShell, and I have removed the need to manually specify all the template file names in the scripts so it is also much easier to set up and deploy.

It is relatively detailed, so instead of writing a blog I put this into a free PDF guide which you can download from here:

Deploying Custom Microsoft Office Templates with System Center Configuration Manager

pdfimg