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

Wednesday, July 23, 2008

A small bug in SMO

I recently encountered what appears to be a small bug in SMO:

When accessing the SqlServer.Management.Smo.Wmi.ManagedComputer namespace using a 32bit application (ie. Powershell.exe) from a 64-bit platform (HW & OS), SMO won't return anything for the "ServerInstances" property. If a 64bit application is used everything works just fine.

Try the following script from the 32, and 64 bit versions of PowerShell (repalce MyServer with the name of your SQL server):

32bit: %SystemRoot%\sysWOW64\WindowsPowerShell\v1.0\powershell.exe
- or -
64bit: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

[reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
$mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" MyServer
$mc

Result when working the correct way:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services : {MSFTESQL$SQL01, MSFTESQL$SQL02, MSFTESQL$SQL03, MSFTESQL$SQL04...}
ClientProtocols : {np, sm, tcp, via}
ServerInstances : {SQL01, SQL02, SQL03, SQL04...}
ServerAliases : {}
Urn : ManagedComputer[@Name='MyServer']
Name : MyServer
Properties : {}
UserData :
State : Existing

Result when NOT working working the correct way:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services : {MSFTESQL$SQL01, MSFTESQL$SQL02, MSFTESQL$SQL03, MSFTESQL$SQL04...}
ClientProtocols : {np, sm, tcp, via}
ServerInstances : {}
ServerAliases : {}
Urn : ManagedComputer[@Name='MyServer']
Name : MyServer
Properties : {}
UserData :
State : Existing

Sunday, July 20, 2008

NMEA GPS & 22 lines of PowerShell

Inspired by the python article "GPS + Google Maps Mash-up in 42 lines of code", I decided to create something similar in PowerShell:

function Nmea2dec {
    Param ([double]$degrees, $o)
    $deg = [math]::floor($degrees/100.0)
    $frac = (($degrees/100.0) - $deg)/0.6
    $ret = $deg + $frac
    if ($o -eq "S" -or $o -eq "W") {$ret = $ret * (-1)}
    return $ret
}

$port = new-Object System.IO.Ports.SerialPort COM1,4800,None,8,one
$port.open()
$line = ""
while (-not ($line -match ".GPRMC.*")) {
    $line = $port.readline()
}
$splitline = $line.split(",")
$latitude = Nmea2dec $splitline[3] $splitline[4]
$longtitude = Nmea2dec $splitline[5] $splitline[6]
$url = "http://maps.google.com/maps?ie=UTF8&ll=$latitude,$longtitude&q=$latitude,$longtitude&z=16&t=h"
$ie = New-Object -ComObject InternetExplorer.Application
$ie.visible=$true
$ie.navigate2("$url")

If you want to play around with the script above, you'll of course need a serial GPS reciever, or you could just use the following line of data:
$GPRMC,182700.000,A,5541.8761,N,01232.0247,E,0.12,52.50,190708,,,A*55

I think I'll continue to play around with GPS data and Google Maps for a while - look out for PS scripts!

Wednesday, July 16, 2008

Locked file detection

As previously stated here the Blog, I spend quite some time doing automation of SQL Server maintenance.
One challenge has been to detect if a given SQL Server was currently locking a specific device file (backup file). To solve this problem I came up with this small function:

function TestFileLock {
    ## Attempts to open a file and trap the resulting error if the file is already open/locked
    param ([string]$filePath )
    $filelocked = $false
    $fileInfo = New-Object System.IO.FileInfo $filePath
    trap {
        Set-Variable -name locked -value $true -scope 1
        continue
    }
    $fileStream = $fileInfo.Open( [System.IO.FileMode]::OpenOrCreate, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None )
    if ($fileStream) {
        $fileStream.Close()
    }
    $obj = New-Object Object
    $obj | Add-Member Noteproperty FilePath -value $filePath
    $obj | Add-Member Noteproperty IsLocked -value $filelocked
    $obj
}

Usage:
PS> TestFileLock "c:\pagefile.sys"
PS> TestFileLock "\\Server01\backup\MyInstance05\devDATABASE_00A1.bak"

Saturday, July 12, 2008

Obtain external IP

Recently I had to obtain the external IP of a number of PC - ie. the address the PCs appear to use on the internet (in my case, the external IP address of their internet gateway/router).

Here is the small function I ended up using:

## Function to retrieve external IP address.
## the external address is retrieved from the
## title header of the webpage "www.myip.dk"

function Get-ExternalIP {
    $source = "http://www.myip.dk"
    $client = new-object System.Net.WebClient
    $webpage = $client.downloadString($source)
    $lines = $webpage.split("`n")
    foreach ($line in $lines) {
        if ($line.contains("</title>")) {
            $ip = $line.replace("    <title>Your IP address is: ", "").replace("</title>","")
        }
    }
    $obj = New-Object Object
    $obj | Add-Member Noteproperty externalIP -value $ip
    $obj
}

Invoke the function in the following way:

$myip = Get-ExternalIP
$myip.externalip

Make PowerShell start much faster

Jeffrey Snover recently wrote the following on the PowerShell Blog:

Speeding Up PowerShell Startup

I talked about this before but a number of people have missed it so here it is under a better title.

In V1, we had a problem which caused our assemblies to not get ngen'ed during installation. If you don't know what "ngen" is, don't worry - you don't need to. All you need to know is that we didn't do the step that makes things go fast on your machine. The instructions for how to fix this are HERE.

Source: http://blogs.msdn.com/powershell/archive/2008/07/11/speeding-up-powershell-startup.aspx

Script:
Set-Alias ngen @(
dir (join-path ${env:\windir} "Microsoft.NET\Framework") ngen.exe -recurse |
sort -descending lastwritetime
)[0].fullName
[appdomain]::currentdomain.getassemblies() | %{ngen $_.location}

My personal advice would be for everyone to try out the "trick" - on some of my systems running the script reduced PowerShell startup time by 75% or more!