Enums in Windows PowerShell Less Than Version 5.0

Maybe you’ve noticed that the upcoming version of Windows PowerShell, 5.0, will make Enumerators (Enums) very easy to create with the new enum keyword. With this post I share an approach to create enums in PowerShell 4.0 and lower as well.

(If you know what an Enumerator is you can skip this section.) Enums help you to deal with rather small ranges of integer values (each value gets a name) and, even more importantly, they simplify programming robust solutions. Put the case that you have to deal with different environments, for example Dev, Test, Acceptance, and Prod. And let’s say that each environment is represented by an int value (thus, 0 to 3 represents Dev to Prod). What happens if you assign the value 4 by mistake? For PowerShell it’s ok because 4 is a valid int value. Therefore, this error will remain undetected at the scene and – according Murphy – reveal its dark energy in the worst possible moment. You get the idea, I hope. It’s no fun to narrow down such problems. How to prevent such failure? You could mess around with if statements and -lt, -gt, -eq for example. Or you make use of, guess what, an Enum. If you have an Enum type for the afore-mentioned environments, PowerShell will refuse a variable of this type to be assigned any value outside of the scope 0..3 and throws an error at the root cause. Therefore, I like to use Enums ever since PowerShell 1.0.

In Windows PowerShell 4.0 and below, Enums are created as follows:

# Create EnvType enum with Add-Type
PS C:\> $EnvType = @'
>> public enum EnvType
>> {
>>     Dev,
>>     Test,
>>     Acceptance,
>>     Production
>> }
>> '@
>> Add-Type -TypeDefinition $EnvType
>>

Now, play with it (that’s how I like to learn stuff, btw):

PS C:\> [EnvType]$myEnv = 0
PS C:\> $myEnv
Dev
PS C:\> [EnvType]$myEnv = 'Production'
PS C:\> $myEnv
Production

Now, let’s get dirty…

PS C:\> # Try invalid values...
PS C:\> [EnvType]$myEnv = 'UAT'
Der Wert "UAT" kann nicht in den Typ "EnvType" konvertiert werden. Fehler: "Der Bezeichner "UAT" kann keinem gültigen
Enumeratornamen zugeordnet werden. Geben Sie einen der folgenden Enumeratornamen an, und wiederholen Sie den Vorgang:
Dev, Test, Acceptance, Production."
In Zeile:1 Zeichen:1
+ [EnvType]$myEnv = 'UAT'
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException
PS C:\>
PS C:\> [EnvType]$myEnv = 4
Der Wert "4" kann aufgrund von ungültigen Enumerationswerten nicht in den Typ "EnvType" konvertiert werden. Geben Sie
einen der folgenden Enumerationswerte an, und versuchen Sie es erneut. Mögliche Enumerationswerte sind "Dev, Test,
Acceptance, Production".
In Zeile:1 Zeichen:1
+ [EnvType]$myEnv = 4
+ ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException

Btw, did you notice the hint within the error message? PowerShell lists the possible values for you.

Hope this helps

Citrix PVS Image Preparation Script for XenApp 7.x Workloads

With this post I share a Powershell script that prepares the master installation of a XenApp 7.x Worker for imaging with Citix Provisioning Services, Prepare-XenApp7.ps1.

Due to fact that Citrix has ported its flagship XenApp to the architecture that was introduced with XenDesktop 5, there’s strictly speaking no need to generalize the PVS vDisk that provides the workload of a XenApp Worker because it doesn’t contain IMA-related stuff anymore. On the other hand there’s still room for some optimization steps before putting a XenApp vDisk into production/standard mode. The script automates the following steps:

  • Investigate the PVS’ Personality.ini in the root of the system drive in order to determine the disk mode that is read-write, read-only, or started from local HD
  • Clear Citrix User Profile Manager’s cache
  • Resync time
  • Update GPO settings
  • Clear network related caches (DNS and ARP)
  • Clear WSUS Client related settings
  • Clear event logs
  • Based on the findings in Step 1, suggest a convenient main action, that is either “Exit” (if we’re in maintenance/private w/ read-write vdisk access), or “Invoke ImagingWizard” (if we started from local HD), or “Invoke XenConvert” (reverse imaging scenario w/ read-only vdisk access)

BTW, the script should work for desktop workloads as well but I haven’t tested it so far.

<#
    .NOTES
    ===========================================================================
     Filename              : Prepare-XenApp7.ps1
     Created on            : 2014-12-18
     Created by            : Frank Peter Schultze
     Organization          : out-web.net
    ===========================================================================

    This script needs to be run on a Windows Server where Citrix XenDesktop 7.x
    VDA and Citrix Provisiong 7.x Service's Target Device Software is installed.

    DISCLAIMER: This PowerShell script is provided "as is", without any warranty,
    whether express or implied, of its accuracy, completeness, fitness for a
    particular purpose, title or non-infringement, and none of the third-party
    products or information mentioned in the work are authored, recommended,
    supported or guaranteed by me. Further, I shall not be liable for any damages
    you may sustain by using this module, whether direct, indirect, special,
    incidental or consequential, even if it has been advised of the possibility
    of such damages.

    .SYNOPSIS
    Citrix PVS Image Preparation Script for XenApp 7.x

    .DESCRIPTION
    Citrix Provisioning Services Image Preparation Script for Citrix XenApp 7.x
    Workloads.
#>

Set-Variable -Name C_DISK_MODE_READ_WRITE -Value 0 -Option ReadOnly
Set-Variable -Name C_DISK_MODE_LOCAL_HD   -Value 1 -Option ReadOnly
Set-Variable -Name C_DISK_MODE_READ_ONLY  -Value 2 -Option ReadOnly

Set-Variable -Name C_ANSWER_YES -Value 0 -Option ReadOnly
Set-Variable -Name C_ANSWER_NO  -Value 1 -Option ReadOnly

Set-Variable -Name C_ANSWER_OPTION1 -Value 0 -Option ReadOnly
Set-Variable -Name C_ANSWER_OPTION2 -Value 1 -Option ReadOnly
Set-Variable -Name C_ANSWER_OPTION3 -Value 2 -Option ReadOnly

Set-Variable -Name C_SERVICE_STATUS_RUNNING -Value 'Running' -Option ReadOnly
Set-Variable -Name C_SERVICE_STATUS_STOPPED -Value 'Stopped' -Option ReadOnly

[string]$DiskName = $null
$DiskMode = $null

function Read-Choice {
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory=$true)]
        [String]
        $Caption
    ,
        [Parameter(Mandatory=$true)]
        [String]
        $Message
    ,
        [Parameter(Mandatory=$true)]
        [String[]]
        $Option
    ,
        [Parameter(Mandatory=$true)]
        [String[]]
        $HelpText
    ,
        [Parameter(Mandatory=$true)]
        [int]
        $Default = 0
	)
    [Management.Automation.Host.ChoiceDescription[]] $choices = @()
    for ($i = 0; $i -lt $Option.Length; $i++) {
        $optionText = $Option[$i]
        $choice = New-Object Management.Automation.Host.ChoiceDescription $optionText
        if($HelpText -and $HelpText[$i]) {
            $choice.HelpMessage = $HelpText[$i]
        }
        $choices += $choice
    }
    $Host.UI.PromptForChoice($Caption, $Message, $choices, $Default)
}


# ------------------------------------------------------------------------------

Write-Output 'Citix PVS Image Preparation Script for XenApp 7.x '

$ChoiceParam = @{
    Caption  = 'PVS XenApp 7 Image Prep'
    Message  = 'Are you ready to start?'
    Option   = '&Yes','&No'
    HelpText = 'Yes, execute this script','No, exit this script'
    Default  = $C_ANSWER_NO
}
if ((Read-Choice @ChoiceParam) -eq $C_ANSWER_NO) {
    return
}


# ------------------------------------------------------------------------------

if (Test-Path -Path ${env:SystemDrive}\Personality.ini) {
    [string] $DiskName = & "${env:ProgramFiles}\Citrix\Provisioning Services\GetPersonality.exe" `$DiskName /o
    Write-Output "Disk Name: ${DiskName}"

    [int] $WriteCacheType = & "${env:ProgramFiles}\Citrix\Provisioning Services\GetPersonality.exe" `$WriteCacheType /o
    switch ($WriteCacheType) {
        $C_DISK_MODE_READ_WRITE {
            $DiskMode = $C_DISK_MODE_READ_WRITE
            Write-Output "Disk Mode: Private or Maintenance (Read-Write)"
		}
        default {
            $DiskMode = $C_DISK_MODE_READ_ONLY
            Write-Output "Disk Mode: Standard (Read-Only)"
        }
    }
}
else {
    $DiskMode = $C_DISK_MODE_LOCAL_HD
    Write-Output "Disk Mode: Local Hard Disk"
}


# ------------------------------------------------------------------------------

Write-Host '1. Clear Citrix related caches (UPM)'

$UPMCacheFile = "${env:ProgramFiles}\Citrix\User Profile Manager\UserProfileManager_?.cache"
if (Test-Path -Path $UPMCacheFile) {
    $Service = Get-Service -Name ctxProfile -ErrorAction SilentlyContinue
    if ($Service.Status -eq $C_SERVICE_STATUS_RUNNING) {
        $Service.Stop()
        $Service.WaitForStatus($C_SERVICE_STATUS_STOPPED)
    }
    Remove-Item $UPMCacheFile -Force
}


# ------------------------------------------------------------------------------

Write-Output '2. Resync time'

$Service = Get-Service -Name W32Time -ErrorAction SilentlyContinue
if ($Service.Status -eq $C_SERVICE_STATUS_STOPPED) {
    Write-Output 'Start W32Time service'
    $Service.Start()
    $Service.WaitForStatus($C_SERVICE_STATUS_RUNNING)
}

w32tm.exe /config /update
w32tm.exe /resync


# ------------------------------------------------------------------------------

Write-Output  '3. Update GPO settings'

gpupdate.exe /force


# ------------------------------------------------------------------------------

Write-Output '4. Clear networking related caches (DNS and ARP)'

ipconfig.exe /flushdns
arp.exe -d


# ------------------------------------------------------------------------------

Write-Output '5. Clear WSUS Client related regisrty keys'

$Service = Get-Service -Name wuauserv -ErrorAction SilentlyContinue
if ($Service.Status -eq $C_SERVICE_STATUS_RUNNING) {
    Write-Output 'Stop Windows Update service'
    $Service.Stop()
    $Service.WaitForStatus($C_SERVICE_STATUS_STOPPED)
}
$WURegKey= 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate'
Remove-ItemProperty -Path $WURegKey -Name AccountDomainSid -ErrorAction SilentlyContinue
Remove-ItemProperty -Path $WURegKey -Name SusClientId -ErrorAction SilentlyContinue
Remove-ItemProperty -Path $WURegKey -Name PingID -ErrorAction SilentlyContinue
Remove-ItemProperty -Path $WURegKey -Name SusClientIdValidation -ErrorAction SilentlyContinue


# ------------------------------------------------------------------------------

Write-Output '6. Clear event logs'

Get-EventLog -List | ForEach-Object {Clear-EventLog -LogName $_.Log} -ErrorAction Inquire


# ------------------------------------------------------------------------------

$ChoiceParam = @{
    Caption  = '6. PVS XenApp Image Prep'
    Message  = 'Choose between the following options'
    Option   = '&Exit', '&ImagingWizard', '&XenConvert'
    HelpText = 'Exit the Script to shutdown this machine manually (Private/Maintenance vDisk mode)',
               'Invoke PVS ImagingWizard to create a new VHD from this machine (vP2PVS)',
               'Invoke XenConvert to image this machine to existing and already mapped vDisk or make a re-image (PVS2vP, vP2PVS)'
    Default  = $DiskMode
}
switch (Read-Choice @ChoiceParam) {
    $C_ANSWER_OPTION1 {
        Write-Output 'You chose "Exit". Finishing script with TargetDeviceOptimizer . . .'
        Start-Process "${env:ProgramFiles}\Citrix\Provisioning Services\TargetOSOptimizer.exe"
    }
    $C_ANSWER_OPTION2 {
        Write-Output 'You chose "ImagingWizard". Invoking PVS ImagingWizard . . .'
        Start-Process "${env:ProgramFiles}\Citrix\Provisioning Services\ImagingWizard.exe" -Wait
    }
    $C_ANSWER_OPTION3 {
        Write-Output 'You chose "XenConvert". Invoking Citrix XenConvert . . .'
        Start-Process "${env:ProgramFiles}\Citrix\XenConvert\XenConvert.exe" -Wait
    }
}


# ------------------------------------------------------------------------------

Write-Output 'Script finished. Press any key to exit.'

$dummy = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

Hope this helps

Latest version on GitHub: Prepare-XenApp7.ps1

How To Backup MS SQL Express Databases? #PowerShell

Happy Twenty Fifteen! The first post of the new year deals with a common question I am confronted with from time to time: Do you have a script to backup MS SQL Express? Yes, I have. The script requires the SQLPS PowerShell Module that will be installed automatically with newer versions of MS SQL Express. Basically, it simplifies the usage of its Backup-SqlDatabase Cmdlet:

<#
    .NOTES
    ===========================================================================
     Filename              : Backup-SqlDatabases.ps1
     Created on            : 2014-12-17
     Created by            : Frank Peter Schultze
    ===========================================================================

    .SYNOPSIS
        Backups SQL databases.

    .DESCRIPTION
        Backups all databases within a given MS SQL Express server instance.
#>
[CmdletBinding()]
Param (
    # The MS SQL Express server computer name
    [Parameter()]
    $ComputerName = $env:COMPUTERNAME
,
    # The name of the instance
    [Parameter()]
    $InstanceName = 'DEFAULT'
,
    # The backup directory
    [Parameter()]
    $BackupPath = 'C:\Backup'
)

$ErrorActionPreference = 'Stop'

$DatabasePath = "SQLSERVER:\SQL\${ComputerName}\${InstanceName}\Databases"

$DateTime = Get-Date -UFormat '%Y%m%d-%H%M'

try {
    Import-Module -Name SQLPS
}
catch {
    Write-Error 'Error loading the SQLPS PowerShell module'; return
}

try
{
    Push-Location -Path $DatabasePath
}
catch
{
    Write-Error "Error setting location - ${DatabasePath}"; return
}

Get-ChildItem | ForEach-Object {

    $DatabaseName = $_.Name

    Write-Output "About to backup database - ${DatabaseName}"

    try {
        Backup-SqlDatabase -Database $DatabaseName -BackupFile "${BackupPath}\${DatabaseName}_${DateTime}.bak"
    }
    catch {
        Write-Error 'Error backing up database'
    }
}

Pop-Location

Hope this helps

[Updated] How To Get The Clientname Within A Logon Script? #PowerShell #RemoteDesktopServices #CitrixXenApp

This morning, a workmate seeked my support regarding an issue that I wasn’t aware of: on a Windows Server 2008 R2 Remote Desktop Session Host you can’t leverage the CLIENTNAME environment variable within a logon script. I stumbled upon a post regarding the same issue and decided to port their VBScript based solution to Windows PowerShell and here’s the result:

<#
.SYNOPSIS
    Return the RDS session ID of a given user.
.DESCRIPTION
    Leverages query.exe session in order to get the given user's session ID.
.EXAMPLE
    Get-RDSSessionId
.EXAMPLE
    Get-RDSSessionId -UserName johndoe
.OUTPUTS
    System.String
#>
function Get-RDSSessionId
{
    [CmdletBinding()]
    Param
    (
        # Identifies a user name (default: current user)
        [Parameter(ValueFromPipeline = $true)]
        [System.String] 
        $UserName = $env:USERNAME
    )
    $returnValue = $null
    try
    {
        $ErrorActionPreference = 'Stop'
        $output = query.exe session $UserName |
            ForEach-Object {$_.Trim() -replace '\s+', ','} |
            ConvertFrom-Csv
        $returnValue = $output.ID
    }
    catch
    {
        $_.Exception | Write-Error
    }
    New-Object psobject $returnValue
}

<#
.SYNOPSIS
    Return the RDS client name
.DESCRIPTION
    Returns the value of HKCU:\Volatile Environment\<SessionID>\CLIENTNAME
.EXAMPLE
    Get-RDSClientName -SessionId 4
.EXAMPLE
    Get-RDSClientName -SessionId Get-RDSSessionId
.OUTPUTS
    System.String
#>
function Get-RDSClientName
{
    [CmdletBinding()]
    Param
    (
        # Identifies a RDS session ID
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [System.String] 
        $SessionId
    )
    $returnValue = $null
    $regKey = 'HKCU:\Volatile Environment\{0}' -f $SessionId
    try
    {
        $ErrorActionPreference = 'Stop'
        $regKeyValues = Get-ItemProperty $regKey
        $sessionName = $regKeyValues | ForEach-Object {$_.SESSIONNAME}
        if ($sessionName -ne 'Console')
        {
            $returnValue = $regKeyValues | ForEach-Object {$_.CLIENTNAME}
        }
        else
        {
            Write-Warning 'Console session'
#            $returnValue = $env:COMPUTERNAME
        }
    }
    catch
    {
        $_.Exception | Write-Error
    }
    New-Object psobject $returnValue
}

Get-RDSSessionId | Get-RDSClientName

Hope this helps

Citrix PVS: Sync Local vDisk Store #PowerShell

Among the files in my ‘powershell-zone’ repository on Github you may stumble upon a script called Sync-PvsLocalStore.ps1. This script helps you to keep a given vDisk within the local vDisk store of two or more Citrix Provisioning Services (PVS) servers in sync. Actually, the script uses robocopy.exe /MIR to mirror the VHD, AVHD, and according PVS files; meaning that …

  • it copies modified and new files from a designated master PVS server to one or more member PVS servers, and
  • it removes EXTRA files from the member servers

The script is designed to exclude maintenance versions of a vDisk from this process. It just keeps production and test versions of a vDisk in sync.
Therefore Sync-PvsLocalStore.ps1 makes only sense if you consequently use ever the same PVS server (what I call the “master” PVS server) for vDisk maintenance purposes.

Design PowerShell Scripts For Feedback

What is the “secret” ingredient of building rather sophisticated solutions with Windows PowerShell?

If you’d ask me, it’s about applying the principle of Feedback Loops. Maybe you’ve heard or read of Feedback Loops in the context of Agile Development Practices or DevOps. To put it in a nutshell, from a DevOps-perspective Feedback Loops stand for communicating early, often, and openly with a view to identify the need or the room for improvements as quickly as possible. It’s like frequent tasting while preparing a dish: experienced chefs do it because they know that a late feedback is worth nothing, or in the case of a real first-class dinner, it would be nothing more than the result of a fortune series of events.

How can a PowerShell script be designed for Feedback Loops? I’ll show you my personal approach. Please note that it makes no claims of being the one and only.

First of all, starting from a scripting challenge that includes multiple actions you need to apply the divide and conquer strategy; meaning that for one thing you have to identify each individual action that is necessary to succeed and for the other thing you have to think of each individual action as an independent “battle”. Solve each task detached from the overall context, i.e. create fully functional partial solutions that receive input values through parameters and that at least return $true in case of success (and otherwise $false). If you’re done with isolating the partial solutions, you’re ready to “conquer” that is to put the “lego bricks” together. Now, the band begins to play…

What you need now is a sort of meta-script that controls the work in process if you like. My approach, in its very basic form, looks like this:

$result = $null
$subTask = (
    '1-FirstStep',
    '2-SecondStep',
    '3-ThirdStep',
    '4-FinalStep'
)
# Each sub script returns a PSObject with properties [boolean]Success and [string]Reason
switch ($subTask)
{
    '1-FirstStep'
    {
        $result = & .\FirstStep.ps1
        if (!$result.Success)
        {
            break
        }
    }
    '2-SecondStep'
    {
        $result = & .\SecondStep.ps1
        if (!$result.Success)
        {
            break
        }
    }
    '3-ThirdStep'
    {
        $result = & .\ThirdStep.ps1
        if (!$result.Success)
        {
            break
        }
    }
    '4-FinalStep'
    {
        $result = & .\FinalStep.ps1
        if (!$result.Success)
        {
            break
        }
    }
}
if ($result.Success)
{
    'All script actions succeeded.' | Write-Output
}
else
{
    'Failed, because: {0}' -f $result.Reason | Write-Output
}

The code sample shows the basic concept how to “misuse” switch as a simple workflow engine that terminates processing as soon as something went wrong. Initially, I give each step a friendly name and put these names in an array ($subTask). Then I commit that collection of names as “test value” to switch. Switch evaluates each item (name) in the order in which it was defined, i.e. for each name switch invokes the appropriate action.

The advantages of this approach really come to light for example when it comes to …
… repeat the entire workflow before raising an error,
… repeat single steps individually before raising an error, or
… go into reverse in case of a fatal condition (rollback)

For now, I leave you alone with your thoughts.

Why Merge Hash Tables #PowerShell

As part of building an automation framework, typically you’re facing the challenge to separate the data from logic as this is the key to an agile and re-usable solution. The automation logic by itself should “only” know how to process data within a workflow, but the logic by itself shouldn’t know any (hard-coded) value. Instead, the logic should get data values from separate resources like configuration files, registry, databases, whatever. With separation of data and logic it’s almost a no-brainer to set up the solution for another environment, not to mention maintenance that is as easy as pushing DEV into the git repo and pulling the changes into TEST and PROD for example. I personally like to maintain data in hashtables, to be more precise I usually have for example config-global.ps1 and a config-abbreviation.ps1 file per environment that 1. adds specific environment settings and 2. overrides baseline settings from the global config. These ps1 files only contain a (usually nested) hash table. In order to import the data I dot-source both the global config and the environment config. The next step is the creation of a single data resource from both hash tables; meaning that I’m able to access the keys and their values through a single variable like $ConfigData afterwards.

Merging hash tables is straightforward as long as each hash table contains different keys: it’s simply $hashTable1 + $hashTable2. In case of duplicate keys this approach will fail because PowerShell isn’t able to resolve that conflict by deciding which key takes precedence. That’s why I wrote the function Merge-Hashtables (get your copy from the TechNet Gallery). Merge-Hashtable creates a single hash table from two given hash tables. The function considers the first hash table as baseline and its key values are allowed to be overridden by the second hash table. Internally, Merge-Hashtables uses two sub functions. The first sub function detects duplicates and adds for each conflict the key of the second hash table to the resulting hash table. The second sub function adds additional keys from the second hash table to the resulting hash table. Both sub functions are designed to support nested hash tables through recursive calls.

Hope this helps.

Simple SQL Scripting Framework

With this post I share my approach to facilitate the re-usage of SQL commands within PowerShell scripts. You should continue reading if you often deal with SQL. And even if you’re not about to use PowerShell scripts against SQL databases you can take some inspiration on how to build a smart automation solution.

My solution uses a PowerShell function Invoke-SQL and a hash table that contains generalized SQL commands. First things first…

In order to issue SQL command text against an ODBC database connection I prefer my function Invoke-SQL. The function either accepts an existing OdbcConnection object or a connection string in order to create a connection on the fly. By default the function returns $true if the execution of the given SQL statement succeeded. With the -PassThru switch the function loads the results into a DataTable object and returns it for further processing. I uploaded Invoke-SQL to the Microsoft TechNet Gallery. Get your copy from there.

Before I proceed to the SqlCommand hash table let me explain why you need it. The Invoke-SQL example below shows how to pass a simple SELECT statement to an existing ODBC connection ($DBConnection) and save the query’s result into the DBResult variable:

PS:\> $DBResult = Invoke-SQL -CommandText 'SELECT * FROM MyTable' -PassThru
-Connection $DBConnection
PS:\>

So much for the simple scenario. As you know SQL command texts can be far more complex than SELECT-foo-FROM-bar and often span multiple lines. With PowerShell it is good practice to use here-strings to deal with multi-line SQL commands. Take a look at the next example that shows the concept (meaning that you shouldn’t care about the content of the SQL query):

PS:\> $SelectVMM = @'
>> SELECT vmm.hostname, vmm.id 
>> FROM myVmmServers as vmm
>> JOIN myDataCenters as DC ON (DC.id = vmm.dcid)
>> JOIN myDCLocations as Location ON (Location.id = DC.lcid)
>> WHERE Location.id = '49'
>> '@
>>
PS:\> $DBResult = Invoke-SQL -CommandText $SelectVMM -PassThru
-Connection $DBConnection
PS:\>

Ok. And now put the case that you have 15, 20 and more of such rather sophisticated SQL commands and you have to re-use them over and over but with different values. Take a look at the value for Location.ID in the previous example. It is hard coded. Therefore, in order to re-use the $SelectVMM here-string you need to leverage the copy-paste-align method (which is error-prone and bloats scripts with redundant code). Or is there another, better, smarter way? Yes, there is. Take a look at the slightly altered example below:

PS:\> $SelectVMM = @'
>> SELECT vmm.hostname, vmm.id
>> FROM myVmmServers as vmm
>> JOIN myDataCenters as DC ON (DC.id = vmm.dcid)
>> JOIN myDCLocations as Location ON (Location.id = DC.lcid)
>> WHERE Location.id = '{0}'
>> '@
>>
PS:\> $SelectVMM49 = $SelectVMM -f '49'
PS:\> $DBResult = Invoke-SQL -CommandText $SelectVMM49 -PassThru
-Connection $DBConnection
PS:\>

As you can see I replaced the hard coded value from the here-string with the placeholder {0}. And afterwards, in order to re-use the here-string, I used PowerShell’s format operator to replace that placeholder with a specific value, and saved the resulting here-string into a new variable. That’s nice. There’s still room for improvement though. Finally, I bring that SqlCommand hash table into play…

Basically, the hash table is a collection of named here-strings each containing a generalized SQL command text like above. It could look like this for example:

$SqlCommand = @{
GetValue = @'
    SELECT {0}
    FROM {1}
'@
GetLatestRecord = @'
    SELECT TOP 1 *
    FROM {0}
'@
SetIdValue = @'
    UPDATE {0}
    SET {1}='{2}',timestamp=GETUTCDATE()
    WHERE Id={3}
'@
SelectVMM = @'
    SELECT vmm.hostname, vmm.id 
    FROM myVmmServers as vmm
    JOIN myDataCenters as DC ON (DC.id = vmm.dcid)
    JOIN myDCLocations as Location ON (Location.id = DC.lcid)
    WHERE Location.id = '{0}'
'@
}

With that SqlCommand hash table in memory SQL scripting is as easy as:

PS:\> $SelectVmm = $SqlCommand.SelectVMM -f '49'
PS:\> $DBResult = Invoke-SQL -CommandText $SelectVmm -PassThru
-Connection $DBConnection
PS:\>

Key take-away:

In order to re-use SQL commands, create for each a generalized here-string and provide them through a hash table.

Hope this helps.

Yet Another Invoke SQL PowerShell Script

A few weeks ago, I uploaded my PowerShell function Invoke-SQL to the Microsoft TechNet Gallery and I forgot to mention it here.

Invoke-SQL is designed to issue any valid SQL command text against an ODBC database connection. The function either accepts an existing OdbcConnection object or a connection string in order to create a connection on the fly. By default the function returns $true if the execution of the given SQL statement didn’t fail. With the PassThru switch the function loads the results into a DataTable object and returns it for further processing. Invoke-SQL returns nothing on error opening the ODBC connection or executing the SQL command text.