How to connect SQL database using PowerShell

Importing Machine details:  (Connecting to an SCCM database)

I am using an SCCM (System Center Configuration Manager) database to pull some information using PowerShell, the database contains a table called CM_VRR and the table holds the information that are collected by SCCM agents from local machines.

Getting the data from a SQL server is not that easy if you have a SQL management studio, but in a few scenarios, we need to collect the data very frequently from the SQL server to provide input for something, or getting small updated information for a reference.

Below we are trying to get the SQL table information using Powershell

Below is my SQL server information.

SQL Server 2012 :
Server Name : ADDC-2012
Database : CM_VRR

SCCM 2012 Console (Front End)

SQL Query – Used to get the Computer Information (Device Collection Details)

Now let’s start this using Powershell !!!

Note the below classes:

Reference: https://msdn.microsoft.com/en-us/library/System.Data.SqlClient

System.Data.SQLClient.SQLConnection : This Represents an open connection to a SQL Server database
System.Data.SqlClient.SqlCommand : Represent a Transact-SQL statement or stored procedure to execute against a SQL Server database
System.Data.SqlClient.SqlDataAdapter : Represents a set of data commands and a database connection that are used to fill the data-set and update a SQL Server database
System.Data.DataSet : Represents an in-memory cache of data.

Make sure we have enough permission to access the database using PowerShell

1 – Create SQL Connection

$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
[System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}
$SQLCommand.Connection = $SQLConnection

In the below SQL Query, I have hard-coded the collection Name. If any portion of your SQL query is dynamic, you just create a variable in the initial stage and include it in your query, also create parameters to avoid hardcoding the values in the script.

Add SQL queries using System.Data.SqlClient.SqlCommand.

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"

3 – Creating SQL Dataadapter and Dataset –  Using System.Data.SqlClient.SqlDataAdapter and System.Data.DataSet

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset) |out-null

4 – Query result is available in $SQLDataset 

$SQLDataset.tables[0]

As per the above result, we got two columns with System Name and Collection Name based on our query. You may get many columns based on what query you are going to use. So we have to filter the data based on our required column.

Filtering the table data based on a column. Below for-each loop will filter the first column ( $data[0] ) of our dataset.
Increase the array value from [0] to get the next column values $data[1]

$tablevalue = @()
foreach ($datain $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}

$SQLConnection.close()

Putting all together: Please replace the server and database name

$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
[System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}


$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"
$SQLCommand.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset) |out-null

$tablevalue = @()
foreach ($datain $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}

$SQLConnection.close()