Using Windows PowerShell to Query SQL
Published:
Browse All Articles > Using Windows PowerShell to Query SQL
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.blogsp
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
}
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
}
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
}
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
}
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
}
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
}
The Result
The output of the script (minus the blotted out private info) should look something like this:
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
}
If you guys want, I'll post my other script that pulls actual data from a table in SQL. Just let me know.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)