Thursday, November 30, 2006

PowerShell, SQL & ADO

The following function connects to a SQL server through ADO, and queries the master database for a list of the database id (dbid) and name of all databases on the server:

function ShowAllDBs {
$adOpenStatic = 3
$adLockOptimistic = 3
     $objConnection = New-Object -comobject ADODB.Connection
     $objRecordset = New-Object -comobject ADODB.Recordset
     $query = "SELECT dbid, name FROM master.dbo.sysdatabases ORDER BY dbid"
     $objConnection.Open("Provider=SQLOLEDB; Data Source=" + $args[0] + "; Initial Catalog=master; Integrated Security=SSPI")
     $objRecordset.Open($query, $objConnection, $adOpenStatic, $adLockOptimistic)
     $objRecordset.MoveFirst()
     do {
          $temp0 = $objRecordset.Fields.Item("dbid").Value
          $temp1 = $objRecordset.Fields.Item("name").Value
          Write-Output "$temp0 : $temp1"
          $objRecordset.MoveNext()
     } until     ($objRecordset.EOF -eq $True)
     $objRecordset.Close()
     $objConnection.Close()
}

To use the function:

ShowAllDBs [server or server\instance]

Wednesday, November 29, 2006

Functions in PowerShell

Here is how to create a simple function in PowerShell. The function takes a single argument, and returns the twice the given amount. Note that the function works on both integers and strings (PowerShell handles multiplication of strings by repeating the string the given number of times).:

function MultiplyByTwo {
$temp = $args[0] * 2
Write-Output $temp
}

MultiplyByTwo 42

MultiplyByTwo "Bluebeard"

This is another example. The RetrieveData function calls two independent functions in order to retrieve the number of CPUs and the amount of memory of a given host:

function GetMem {
$temp = Get-WmiObject "Win32_LogicalMemoryConfiguration" -computername $args[0]
$MemInBytes = $temp.TotalPhysicalMemory * 1kb
Write-Output $MemInBytes
}

function GetNumCPU {
$temp = Get-WmiObject "Win32_ComputerSystem" -computername $args[0]
$nocpu = $temp.NumberOfProcessors
Write-Output $nocpu
}

function RetrieveData {
$tmpNoCPU = GetNumCPU $args[0]
$tmpMemInBytes = GetMem $args[0]
$computername = $args[0]
Write-Output "The Computer $computername has $tmpNoCPU CPU(s) and $tmpMemInBytes bytes of RAM installed."
}

RetrieveData "localhost"

Variables and types

A variable in PowerShell must begin with the dollarsign ($). The name of the variable stops at the first word breaking character (such as space, comma or period). If special characters are needed in a variable, name curly braces can be used to surround the variable name:

$myvar = "normal variable name"
$myvar
${ugh!b££!!¤&(} = "special variable name"
${ugh!b££!!¤&(}

In order to determine the type of a variable, the GetType method can be used:

$a = 1234
$a.GetType() ## Displays the type of $a

$b = "1234"
$b.GetType()

[int]$c = $b ## casts $c as an integer (int32)
$c.GetType()

To get information of properties and methods of a given object - in this case a variable - use Get-Member -InputObject.
Notice the difference between the string variable $b and the integer cariable $c

Get-Member -InputObject $b
Get-Member -InputObject $c

Examples - using various methods on a variable:

$d = "Hello world! I'm back again!"
$d.Split()
$d.ToLower()
$d.ToUpper()

Tuesday, November 28, 2006

SQLDMO

Here is a small script using SQLDMO against a MS SQL Server 2000 or 2005 to obtain the TCP port number currently used. I know the Books Online for SS05 indicates that DMO is no longer supported - well I guess that they're wrong:

$s = New-Object -comobject "SQLDMO.SQLServer"
$s.loginsecure = $true
$s.connect("SERVERNAME01")
$s.registry.tcpport
$s.close()

Two ways of writing foreach

There are two ways of creating foreach statements in PowerShell

$b = 'hans','soren','ib','poul','hanne','william','ann-marie'
$b | Foreach-Object {Write-Host $_}

Or

$b = 'hans','soren','ib','poul','hanne','william','ann-marie'
Foreach ($a in $b) {Write-Host $a}

Basic Stuff

Literal Strings
'text' indicate a literal string in PowerShell, while "text" is used when variables and escape chars are needed.
$c = "Julemanden"
Write-Host 'Santa Claus is called $c in Denmark'
Write-Host "Santa Claus is called $c in Denmark"

Multidimensional Arrays
$a = @( (1,2,3,4,5) , (6,7,8,9,0) )
$a[0][0]
$a[1][1]