Thursday, August 09, 2007

Reading directly from SQL Server

A small example of reading a value from an SQL Server. In this case the maximum memory allowed for the SQL Server Instance.

$adOpenStatic = 3

$adLockOptimistic = 3
$timeout = 3

function GetMaxMemory {
    ## returns single string
    ## returns -1 if memory is unlimited
    ## returns -2 if no connection to the desired DB can be obtained
    ## arg0 = datasource (eg. server1\instance01)
    $objConnection = New-Object -comobject ADODB.Connection
    $objRecordset = New-Object -comobject ADODB.Recordset
    $query = "SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544"
    $objConnection.Set_ConnectionString("Provider=SQLOLEDB; Data Source=" + $args[0] + "; Initial Catalog=master; Integrated Security=SSPI")
    $objConnection.Set_ConnectionTimeout($timeout)
    $objConnection.Open()
    if ($objConnection.State -eq "1") {
        $objRecordset.Open($query, $objConnection, $adOpenStatic, $adLockOptimistic)
            $objRecordset.MoveFirst()
            $temp0 = ($objRecordset.Fields.Item("value").Value)
            if ($temp0 -eq 2147483647) {
                $temp0 = -1
            } else {
                $temp0 = $temp0 * 1mb
            }
        [long]$temp1 = $temp0
        Write-Output $temp1
        $objRecordset.Close()
        $objConnection.Close()
    } else {
        Write-output -2
    }
}

Usage:

GetMaxMemory servername\isntancename

1 comment:

Anonymous said...

Any reason you don't use ado.net?