Thursday, July 24, 2008

SQL queries

There aren't that much written about using PowerShell as a channel for T-SQL, available on the web, so I thought I might give it a shot.

The scripts below allows you to query a SQL Server (SELECT statements) and use the returned data, as well as perform queries that doesn't return data (INSERT, UPDATE, DBCC *, etc.).
The way these functions are written are in no way ideal, but rather the minimum amount of scripting required to solve needs I've had in certain scripting scenarios. The scripts below include a limited ability to return errors.


The way to use the functions can be described this way:

1. Use New-SQLconnection to establish a connection to a SQL server.
2. Execute Query-SQL or NonQuery-SQL to perform a query against the server.
3. Use Close-SQLquery to close the query.
4. repeat 2. and 3. as many times as needed.
5. Use Remove-SQLconnection to close the connection.

It is only possible to have one open query (ie. not Close-SQLquery'ed) at a
time, but you can have as many SQLconnections as you need.


First we need the ability to create a connection to a SQL server:

Usage:
PS> $conn = New-SQLconnection "Server\Instance" "Database"
Check the state property on the returned conenction to determine if the connection was successfully established:
PS> if ($conn.state -eq "closed") {# Connection failed - handle error}

function New-SQLconnection {
    Param ([string]$server,
        [string]$database = "master",
        [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=$database;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}

Then we should be able to perform a query:

Usage:
PS> Query-SQL "SELECT @@version" $conn
If the query returns zero rows "$data -eq $null" will evaluate to true.
If the query cannot be completed the returned object will be of type [int]

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 ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

Execute a SQL statement, where the information returned in on a "x rows affected" level:
If the query returns zero rows "$data -eq $null" will evaluate to true.
If the query cannot be completed the returned object will be of type [int]
Use "if ($data -is [int]) {# handle error}"
The value -1 is returned if a statement that doesnt' return a "x rows affected" is executed
The value -2 is returned if an error occurs.

Usage:
PS> NonQuery-SQL "SELECT @@version" $conn

function NonQuery-SQL {
    Param ($query, $conn, $CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $RowsAffected = $sqlCmd.ExecuteNonQuery()
    if ($? -eq $false) {
        $RowsAffected = -2
    }
    $RowsAffected
}

Close down an existing query (should be performed after every query):

Usage:
PS> Close-SQL $data

function Close-SQLquery {
    Param ($query)
    $query.close()
    $query = $null
}

And finally close an established connection to a SQL Server:

Usage:
PS> Remove-SQLconnection $conn

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


Here is a complete example of how to use the functions above to do a simple query and create a new database:

$ScriptName = $myInvocation.MyCommand.Name
$Server = "MyServer\MyInstance"

$myConn = New-SQLconnection $Server "master"
if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
Write-Host "First Query" -back magenta
$query = "SELECT fileid, filename, size FROM sysfiles"
$data = Query-SQL $query $myConn
if ($data.gettype() -eq [int]) {"Failed to query SQL server";EXIT}
if ($data.gettype() -eq [string]) {"Zero rows returned";EXIT}
while($data.Read()){
    Write-Host $data.GetValue(0) -back yellow
    Write-Host $data.GetString($data.GetOrdinal("filename")) -back yellow
    Write-Host $data.GetValue(2) -back yellow
}

Close-SQLquery $data

Write-Host "Second Query" -backgroundcolor magenta
$query = "SELECT TOP 5 name, description, backup_start_date FROM msdb.dbo.backupset"
$data = Query-SQL $query $myConn
if ($data.gettype() -eq [int]) {"Failed to query SQL server";EXIT}
if ($data.gettype() -eq [string]) {"Zero rows returned";EXIT}
while($data.Read()){
    Write-Host $data.GetValue(2) -back red
    Write-Host $data.GetValue(1) -back red
    Write-Host $data.GetValue(0) -back red
}

Close-SQLquery $data

Write-Host "First NonQuery" -back magenta
$query = "CREATE DATABASE [MyFirstTestDB] "
$data = NonQuery-SQL $query $myConn
if ($data -eq -2) {
    Write-Host "Run away! Something has gone wrong!!!"
} else {
    Write-Host "$data Rows Affected"
}

Remove-SQLconnection $myConn

6 comments:

Anonymous said...

You might want to combine all the scripts into one larger script. By reusing the connection object, you could easily start a transaction in a query, have it exit the query without committing it (or have an error that leaves it open), and then go to lunch. Since the connection is still open and the transaction is also still open, you might be somewhat annoyed with yourself for blocking other connections.

Thanks,

Stephen

Jakob Bindslet said...

Thanks for the suggestion Stephen - however, the purpose of the scripts was simply to provide the building blocks for larger scripts.

Niels Grove-Rasmussen said...

SQL Server 2008 PowerShell (sqlps.exe) gives us Invoke-SqlCmd. But one have to install sqlps.
Still the subject is relevant - several non-PS colleagues has asked about access to data from SCOM command prompt.

Kibi78704 said...

Jakob,
Thank you so very much for this blog. I have adapted several of your functions for use in my QA libraries.
I have reason to connect to a remote SQL Server, and used your code as a springboard to do so. I am using PowerShell 1.0, .Net 2.0, SQL Server 2005, Terminal Services, and Microsoft(R) Windows(R) Server 2003, Standard Edition (on the client).
I thought you might be interested in the connection string, because it took me several hours to discover the correct one.

$DbServerName = "SomeDnsServerAlias"
$Database = "SomeDatabaseName"

Function Global:New-SqlConnection(
[string] $Server = $DbServerName,
[string] $Database = $(throw $SPiTestMsgs.GetDatabaseName)
) {
##########################################
## Prepare the authentication information. By default, we pick Windows authentication
##########################################
$Authentication = "Integrated Security=SSPI"

##########################################
## Prepare the connection string out of the information they provide for the expected database
##########################################
$ConnectionString = "Server=$Server\" + `
"$Authentication;" + `
"Database=$Database;"

If (Test-Path Variable:\SqlConnection) { $SqlConnection.close() }
Else { $SqlConnection = New-Object ('System.Data.SqlClient.SqlConnection') }

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.StatisticsEnabled = $True
$SqlConnection.Open()
Return $SqlConnection
} ## End New-SqlConnection

Cheers,
Kibi78704

Quae singulae non valent, junctae vigent. -- Those which are not powerful separately, thrive together.

Kibi78704 said...

Sorry, I left out a semicolon in the connection string. Try this instead:

$ConnectionString = "Server=$Server\;$Authentication;Database=$Database;"

Anyway, thanks again.

Anonymous said...

Thanks so much. This was a huge help for me!