<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Using Windows PowerShell to Query SQL

Published on
11,945 Points
5,645 Views
3 Endorsements
Last Modified:
Approved

Why Use PowerShell?

Recently, I was asked to come up with a quick way to find out what versions all of our SQL servers were currently running. Well, we have several thousand so taking a manual approach simply wasn't an option. We also didn't have time to test and purchase a product to do that sort of discovery for us. That left us with one option which we could take advantage of; scripting.

It was time to dust off an old script I had written a few years ago to monitor a database for new items and then process those items in Active directory. That script was a little more complex than what we are going to do today, but sets up the framework nicely. It already had the functions I needed, so all I had to do was write a quick query, plug it into the script and voila, instant gratification. The functions originated from JBs PowerShell blog at the following URL: http://mspowershell.blogspot.com/2008/07/sql-through-adonet.html

The Functions

Let's take a look at these functions. We will break them up into 5 seperate functions. Today we will be using 4 of them. Since I am only needing to find out what versions the sql servers are on, the first function will be ignored, but I wanted to show it anyway, as it adds a key line for when you are wanting to query the actual content of a table. Let's start with that one:

1. New-SQLDBConnection


This function will take the server instance name and the database name to establish a connection to an existing database. From there, you can write a query to pull specific data from the tables in the database.
Function New-SQLDBConnection {
    Param ([string]$server,
        [string]$db,
        [string]$connectionName = $ScriptName)
    if (test-path variable:\conn) {
        $conn.close()
    } else {
        $conn = new-object ('System.Data.SqlClient.SqlConnection')
    }
    $connString = "Server=$server;Integrated Security=SSPI;Database=$db;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}

Open in new window

Since we are not needing to access any data from any specific databases, we will move on to the second function.

2. New-SQLInstanceConnection


function New-SQLInstanceConnection {
    Param ([string]$server,
	[string]$connectionName = $ScriptName)
    if (test-path variable:\conn) {
        $conn.close()
    } else {
        $conn = new-object ('System.Data.SqlClient.SqlConnection')
    }
    $connString = "Server=$server;Integrated Security=SSPI;Database=$db;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}

Open in new window

In this function, we are only providing the server instance name. The function will use that information and establish your connection.

3. Query-SQL


Once your connection is established, you will then need to provide your query to the connection. The next function sets the groundwork for that to be possible.
function Query-SQL {
    Param ($query, $conn, [int]$CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    if ($? -eq $false) {
        Close-SQLquery $rset
        [int]$rset = -1
    }
    if ($Rset.hasrows -eq $false) {
        Close-SQLquery $rset
        [string]$rset = ""
    }
    ,$Rset
}

Open in new window

You must pass the actual query and the connection that was established by the previous function to this function. This function will then return the results of the query in a format you can code against.

4. Close-SQLQuery


Being the conscientious scripter that you are, you know that you need to clean up after youself. You should always close your queries when you are done with them. You can do this with the following function:
function Close-SQLquery {
    Param ($query)
    $query.close()
    $query = $null
}

Open in new window

5. Remove-SQLconnection


This last step removed the connection from memory, freeing up the PowerShell environment to script against the connection again, as you can only have one connection open at a time.
function Remove-SQLconnection {
    Param ($connection)
    $connection.close()
    $connection = $null
}

Open in new window


Now that the pieces are in place...

Let's put those functions to use. First thing you need is a list of servers. In this example, we will be using a CSV file with a header row called SqlServer, then each row after that will have an instance name off of each sql server. If your server hosts multiple instances, you only need one since what we are looking for is the Version information of the server itself. Once you have the list, you can use the import-csv cmdlet to write it into an array. We will use an array called $sqlServers.

You can then use a for loop that iterates through each server in the array and processes it accordingly. On each element of the array you will pull out the SqlServer listed on that row, establish your connection using the New-SQLInstanceConnection function, Check to make sure the connection opened for you, establish your query, and then run the query-sql function against it and the connection.

Since what we want to know is the versions of all of our sql servers, the query we will be running is simply "SELECT @@VERSION" . The Query results are written to a variable called $Data. Now the contents of $data are not as cut and dry as you would think. If you simply stopped there and typed $data to see what the result was, it would look something like: FieldCount 1
This is not what we are looking for at all. So what do we do now? Well, I am glad you asked. Now we treat that $Data as an array and use the GetValue attribute to return what we are really looking for.

Once we have that, we can output our results. In this exercise, we will simply write it to the screen, but you can choose any PowerShell output medium you want, be it out-file, export-csv, write-host or whatever. Then, finally, we close our query and our connection.

This script (minus the functions) looks as follows:
$sqlServers = import-csv "c:\scripts\Get-SQLServerInfo\SQLServers.csv"

for ($i = 0; $i -le ($sqlServers.Length -1); $i +=1)
{
	$sqlServer = $sqlServers[$i].SqlServer
	
	$myConn = New-SQLInstanceConnection "$sqlServer"
	if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
	$query = "SELECT @@VERSION"
	$data = Query-SQL $query $myConn
	foreach ($item in $data)
	{
		$result = $item.GetValue(0)
		#you can write the results below out to any format you want. For the purpose of this example, we will just dump them to the screen.
		$sqlServer
		$result
	}


	# Close the SQL Query using the Close-SQLquery function
	Close-SQLquery $data

	# Remove the SQL connection using the Remove-SQLConnection function
	Remove-SQLconnection $myConn
}

Open in new window


The Result

The output of the script (minus the blotted out private info) should look something like this:
Results

The whole shebang

Here is the whole script from start to finish:
Function New-SQLDBConnection {
    Param ([string]$server,
        [string]$db,
        [string]$connectionName = $ScriptName)
    if (test-path variable:\conn) {
        $conn.close()
    } else {
        $conn = new-object ('System.Data.SqlClient.SqlConnection')
    }
    $connString = "Server=$server;Integrated Security=SSPI;Database=$db;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}
function New-SQLInstanceConnection {
    Param ([string]$server,
	[string]$connectionName = $ScriptName)
    if (test-path variable:\conn) {
        $conn.close()
    } else {
        $conn = new-object ('System.Data.SqlClient.SqlConnection')
    }
    $connString = "Server=$server;Integrated Security=SSPI;Database=$db;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}
function Query-SQL {
    Param ($query, $conn, [int]$CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    if ($? -eq $false) {
        Close-SQLquery $rset
        [int]$rset = -1
    }
    if ($Rset.hasrows -eq $false) {
        Close-SQLquery $rset
        [string]$rset = ""
    }
    ,$Rset
}
function Close-SQLquery {
    Param ($query)
    $query.close()
    $query = $null
}

function Remove-SQLconnection {
    Param ($connection)
    $connection.close()
    $connection = $null
}

$sqlServers = import-csv "c:\scripts\Get-SQLServerInfo\SQLServers.csv"

for ($i = 0; $i -le ($sqlServers.Length -1); $i +=1)
{
	$sqlServer = $sqlServers[$i].SqlServer
	
	$myConn = New-SQLInstanceConnection "$sqlServer"
	if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
	$query = "SELECT @@VERSION"
	$data = Query-SQL $query $myConn
	foreach ($item in $data)
	{
		$result = $item.GetValue(0)
		#you can write the results below out to any format you want. For the purpose of this example, we will just dump them to the screen.
		$sqlServer
		$result
	}


	# Close the SQL Query using the Close-SQLquery function
	Close-SQLquery $data

	# Remove the SQL connection using the Remove-SQLConnection function
	Remove-SQLconnection $myConn
}

Open in new window


If you guys want, I'll post my other script that pulls actual data from a table in SQL. Just let me know.
3
Author:GusGallows
0 Comments

Featured Post

Cloud as a Security Delivery Platform for MSSPs

Every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. View our on-demand webinar to learn more!

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month