Using Windows PowerShell to Query SQL: PowerShell, Windows, SQL, Query

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
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all 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
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select all 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
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

Select all 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
}

1:
2:
3:
4:
5:

Select all 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
}

1:
2:
3:
4:
5:

Select all 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
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Select all Open in new window

The Result

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

screenshot-sqlquery.jpg

167 KB
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
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:

Select all 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.

Using Windows PowerShell to Query SQL

Why Use PowerShell?

The Functions

New-SQLDBConnection

New-SQLInstanceConnection

Query-SQL

Close-SQLQuery

Remove-SQLconnection

Now that the pieces are in place...

The Result

The whole shebang

PowerShell

Windows

SQL

Query

Powershell

About The Author

More Articles From GusGallows

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Powershell Experts