Saturday, 21 March 2015

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 hold many information which arecollected from local machines as well as the server details.

I know getting the data from a SQL server is not that easy if you have a sql management studio, but in few scenario we need to collect the data very frequently from sql server to provide an input for something else, or getting a small updated information.

If we can use powershell to get those information automatically, that will be great as it saves lot of our time, also we no need to have a sql admin studio installed on our machine.or we no need to open the console.

Below are 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.

1 - Opening SQL Connection using System.Data.SQLClient.SQLConnection
We should have the proper permission to the database to access the data using powershell

$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 

2 - Crating stored procedure and adding SQL query using System.Data.SqlClient.SqlCommand.
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 in your query.

$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 - Now your query result is available in $SQLDataset 

$SQLDataset.tables[0













As per the above result, we got two column 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 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 ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close() 

Putting all together : You have to modify the server details.

$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 ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close()





Download Script