Small Framework to load sql scripts simultaniously to several Servers

I use the following framework to run scripts calling sqlcmd simultaneously as PowerShell jobs during some MsBuild Task. My Buildagents are running Server 2003 and there are some linitations cf. StackOverflow.

But just calling sqlcmd works fine. In fact I'm dropping and recreating my test databases and running a lot of sql scripts completly within such background jobs. I didn't try it with the Oracle part using sqlplus which I run in the main script and after whose termination I wait for jobs to terminate in time or terminate them. 

$workdir = Split-Path $MyInvocation.MyCommand.Path            
$logdir = 'C:\temp\log'            
$server1 = 'my_sql2008'            
$server2 = 'my_sql2005'            
$datbase = 'PowerShellTest'            
            
$cmd1 = [scriptblock]::Create(". $workdir\script_containing_function_for_background_job.ps1
Some_function -server $server1 -database $datbase $logdir -asJob" )            
            
                
$cmd1 = [scriptblock]::Create(". $workdir\script_containing_function_for_background_job.ps1
Some_function -server $server2 -database $datbase $logdir -asJob" )            
            
            
"...Starting the background jobs"            
$SQLJobStartTime = Get-date            
$jnr1 = (Start-Job -ScriptBlock $cmd1).Id            
$jnr2 = (Start-Job -ScriptBlock $cmd2).Id            
# *****************************************            
            
' Doing something slow here (it happens to be with an Oracle Server'            
<#
....
#>            
            
function Get-WaitTime             
{            
    param(            
        [DateTime]$start,            
        $dauer            
    )            
    $rest = $dauer - ((Get-Date) - $start).Totalseconds             
    if ($rest -lt 0) {$rest = 0}            
    $rest            
}            
            
'...Waiting for job on  SQL Server 2008'            
Wait-job -id $jnr1 -timeout ( Get-WaitTime $SQLJobStartTime 960 )   # max 16 min            
if ((Get-job -id $jnr1).state -eq 'Running')            
{            
    Stop-job -id $jnr1            
}             
Receive-Job -id $jnr1            
$state1 = (Get-job -id $jnr1).State            
$dauer1 = ((get-date) - $SQLJobStartTime).Totalseconds            
            
"$(Get-date -f d)  $(Get-date -f t) Release $release($build) Einspielen auf SQl-Server 2008 $state1 $dauer1 secs"            
            
'...Waiting for job  SQL Server 2005'            
Wait-job -id $jnr2 -timeout ( Get-WaitTime $SQLJobStartTime 960 )   # max 16 min            
if ((Get-job -id $jnr2).state -eq 'Running')            
{            
    Stop-job -id $jnr2            
}             
Receive-Job -id $jnr2            
$state2 = (Get-job -id $jnr2).State            
$dauer2 = ((get-date) - $SQLJobStartTime).Totalseconds            
            
"$(Get-date -f d)  $(Get-date -f t) Release $release($build) Einspielen auf SQl-Server 2005 $state2 $dauer2 secs"            

Displaying the builds of a given build-definition since the latest succesfull one

When it comes to fixing broken builds, the first step is to determine when the last successful build run and which builds failed afterwards.

In practice I'm looking for the state of 20 build definitions each morning, but here I reduce the script to a single one.

# adapt the following 4 lines to your environment            
Add-Type -Path "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.TeamFoundation.Build.Client.dll"            
            
$tfsuri = 'your tfs server'            
$project = "your prpject"            
$buildDefinition = 'Main'            
            
function Get-BuildInfo            
{            
    param(            
        $buildDefinition,             
        $MaxBuildsPerDefinition = 5,            
        [switch]$DontBreakOnSuccess,            
        [switch]$Starttime            
        )            
            
    #Write-Host "Using MaxBuildsPerDefinition $MaxBuildsPerDefinition"            
    $tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($tfsuri)            
    $buildServerType = [Microsoft.TeamFoundation.Build.Client.IBuildServer]            
    $buildServer = $tfs.GetService($buildServerType);            
            
    $buildDetailSpec = $buildServer.CreateBuildDetailSpec($project, $buildDefinition);            
    if ($Starttime)            
    {            
        $buildDetailSpec.QueryOrder = [Microsoft.TeamFoundation.Build.Client.BuildQueryOrder]::StartTimeDescending            
    } else {            
        $buildDetailSpec.QueryOrder = [Microsoft.TeamFoundation.Build.Client.BuildQueryOrder]::FinishTimeDescending            
    }            
    $buildDetailSpec.MaxBuildsPerDefinition = $MaxBuildsPerDefinition            
            
            
    try            
    {            
     $buildQueryResult = $buildServer.QueryBuilds($buildDetailSpec)            
            
        foreach ($build in $buildQueryResult.builds)            
        {                
            # [Microsoft.TeamFoundation.Build.Client.IBuildDetail]$buildDetail = $buildQueryResult.builds[0]             
            $tfsBuildNumber = $build.BuildNumber            
            $shortBuildNumber = $tfsBuildNumber.Substring($tfsBuildNumber.LastIndexOf('_')+1)            
            
    #         #$shortBuildNumber            
    #         $buildQueryResult.failures.length            
    #         $buildQueryResult.builds.length            
    #         $buildQueryResult.builds[0].BuildNumber            
    #         #$buildQueryResult.builds[0].Status            
    #         #$buildQueryResult.builds[0].BuildDefinitionUri.AbsolutePath            
    #         $buildQueryResult.builds[0].StartTime            
    #         $buildQueryResult.builds[0].FinishTime            
              
              $buildAgent = '??'            
              $build.Information | % {            
                $_.nodes | % {            
                    try             
                    {            
                    $buildAgent = $_.children.nodes[2].fields['ReservedAgentName']            
                    }            
                    catch            
                    {            
                    }            
                }             
            }            
                
            New-Object PSObject -Property @{             
                Buildnumber = $build.BuildNumber;             
                Status = $build.Status;            
                CompilationStatus = $build.CompilationStatus;            
                Start = $build.StartTime;            
                End = $build.FinishTime;            
                DropLocation = $build.DropLocation;            
                SourceGetVersion = $build.SourceGetVersion;            
                BuildAgent = $buildAgent            
                }            
            if ($build.Status -eq 'Succeeded' -and ! $DontBreakOnSuccess) { break  }                
                            
        }            
    }            
    catch [Exception]            
    {            
     throw "TFS nicht erreichbar."            
    }            
            
}            
            
            
$a = @(Get-BuildInfo $buildDefinition)            
$a | % {            
    if ($_.Status -eq 'InProgress') { $end = Get-Date } else { $end = $_.End }            
    "{0,-32} {1,18}  {2:ddd} {3} - {4:T} {5,5:0} {6} {7,6} {8}" -f $_.Buildnumber, $_.Status, $_.Start, $_.Start, $End, ($End -$_.Start).Totalminutes, $_.SourceGetVersion, $_.buildAgent, $Droplocation            
}

The result tells me start time, end time, duration, included change set  and drop location.

In the case of failed builds I some further checks on the log files not included here to dispatch the problem as quick as possible to the developer  who caused the problem.

Search for Firefox Bookmarks with given combination of 1 to 3 keywords

When I found that Firefox stores its bookmarks in a sqlite database places.sqlite I wrote the following PowerShell script to search for bookmark by keyword combination.

Here is the code:

ipmo WPK            
            
if (! $sqlitedll)            
{            
    $sqlitedll = [System.Reflection.Assembly]::LoadFrom("C:\Program Files\System.Data.SQLite\bin\System.Data.SQLite.dll")             
}            
            
$ConnectionString = "Data Source=C:\Var\sqlite_ff4\places.sqlite"            
            
$conn = new-object System.Data.SQLite.SQLiteConnection             
$conn.ConnectionString = $ConnectionString             
$conn.Open()             
            
function Invoke-sqlite            
{            
    param( [string]$sql,            
           [System.Data.SQLite.SQLiteConnection]$connection            
           )            
    $cmd = new-object System.Data.SQLite.SQLiteCommand($sql,$connection)            
    $ds = New-Object system.Data.DataSet            
    $da = New-Object System.Data.SQLite.SQLiteDataAdapter($cmd)            
    $da.fill($ds) | Out-Null            
    return $ds.tables[0]            
}            
            
function Show-Bockmarks ($resource) {            
    New-Grid -Rows 2 -Columns 1 -width 1400 -hight 1000  {            
            
        New-StackPanel -Orientation horizontal -column 0 -row 0 -Children {            
             New-Label    '1. Keyword'            
             New-TextBox  -Name tag1 -width 200            
             New-Label    '2. Keyword'            
             New-TextBox  -Name tag2 -width 200            
             New-Label    '3. Keyword'            
             New-TextBox  -Name tag3 -width 200            
             New-Button -Name Search "search" -On_Click {            
            $text1 = $window | Get-ChildControl Tag1            
            $tag1 = $text1.Text            
            $text2 = $window | Get-ChildControl Tag2            
            $tag2 = $text2.Text            
            $text3 = $window | Get-ChildControl Tag3            
            $tag3 = $text3.Text            
            if ( $tag2 -ne '') {            
$clause2 = @"            
    join moz_bookmarks l2 on b.fk = l2.fk and b.id <> l2.id
    join moz_bookmarks t2 on l2.parent = t2.id and  t2.parent = 4 and upper(t2.title) = upper('$tag2')
"@                                    
            } else { $clause2 = '' }                    
            
            if ( $tag3 -ne '') {            
$clause3 = @"            
    join moz_bookmarks l3 on b.fk = l3.fk and b.id <> l3.id
    join moz_bookmarks t3 on l3.parent = t3.id and  t3.parent = 4 and upper(t3.title) = upper('$tag3')
"@                                    
            } else { $clause3 = '' }                    
            
$ff_sql = @"
SELECT b.title, datetime (b.dateAdded / 1000000, 'unixepoch', 'localtime') dateAdded , p.url
    from moz_bookmarks b
    join moz_bookmarks l1 on b.fk = l1.fk and b.id <> l1.id
    join moz_bookmarks t1 on l1.parent = t1.id and  t1.parent = 4 and upper(t1.title) = upper('$tag1')
    join moz_places p  on b.fk = p.id $clause2 $clause3
where b.title is not null and b.type = 1
"@            
            $conn = $resource.conn            
            $window.Title = "$($conn.database) Database Browser"            
            $TableView = $window | Get-ChildControl TableView            
            $TableView.ItemsSource = @(Invoke-sqlite -sql $ff_sql -connection $conn)            
             }             
             New-Button -Name Cancel "Close" -On_Click {$window.Close()}             
        }            
        New-ListView -Column 0 -Row 1 -Name TableView -View {            
           New-GridView -AllowsColumnReorder -Columns {            
               New-GridViewColumn "title"             
               New-GridViewColumn "dateAdded"             
               New-GridViewColumn "url"             
           }            
        }   -On_SelectionChanged {            
             start $this.selecteditem.url            
        }            
        #}            
            
    } -asjob -Resource $resource            
}            
            
Show-Bockmarks -resource @{conn = $conn}            

You have to install System.Data.SQLite
from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

I had problems when I checked the install in GAC option. Therefore I installed it without that option and supply the absolute path in my script.

Note: You can't open places.sqlite while Firefox is running. I copied it and coded the path of the copy into my script.

Now I can enter a keyword, press search and the grid fills. Double clicking starts the bookmark in the default browser.

Eval-Selection in ISE editor, result in editor pane

Here is a little demo showing, who to use ISEs editor pane to display untruncated, unwrapped results.

Run the script in ISE.
Open a new editor.
Tpye dir and press F7:

            
            
function Eval-Selection            
{            
    # Bernd Kriszio 2010-11-21            
    # http://pauerschell.blogspot.com/            
    # twitter @bernd_k            
                
                    
    $editor = $psise.CurrentFile.Editor            
            
    if ($editor.SelectedText)            
    {            
        $inputScript = $editor.SelectedText            
        $editor.InsertText('')            
        $editor.InsertText($inputScript)            
        $result = Invoke-expression $inputScript | out-String            
        $editor.insertText("`r`n")            
        $editor.InsertText($result)            
            
    }            
    else            
    {            
        $inputScript = $editor.Text            
        $EndLine =  $editor.LineCount             
        $EndColumn = $editor.GetLineLength($EndLine) + 1            
        $editor.SetCaretPosition($EndLine, $EndColumn)            
        $result = Invoke-expression $inputScript | out-String            
        $editor.insertText("`r`n")            
        $editor.InsertText($result)            
    }            
}                    
                    
             
$psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.Add("Eval-Selection", {Eval-Selection} ,  'f7')

Edited
After playing a while, I modified the rules for the case, when nothing is selected. Now I use just the single line, the caret is in. Further I added the -width 1000 parameter to make it realy an improvement about usual output-pane results.

And I have a lot of crazy ideas, what to do if the caret is in an empty line. I don't implement them. Next month I wouldn't remember them myself.

Keep things simple

Bernd

This is the version, I added to my profile:

function Eval-Selection            
{            
    # Bernd Kriszio 2010-11-21            
    # http://pauerschell.blogspot.com/            
    # twitter @bernd_k            
                
                    
    $editor = $psise.CurrentFile.Editor            
                
    # if nothing is selected just use the line the caret is in            
    if (!$editor.SelectedText)            
    {            
        $caretLine = $editor.CaretLine            
        $caretLineEnd = $editor.GetLineLength($caretLine) + 1                
                    
        $editor.Select($caretLine, 1, $caretLine, $caretLineEnd)            
    }            
            
    # if something is selected use it.            
    if ($editor.SelectedText)            
    {            
        $inputScript = $editor.SelectedText            
        $editor.InsertText('')            
        $editor.InsertText($inputScript)            
        $result = Invoke-expression $inputScript | out-String -width 1000            
        if ($editor.CaretColumn -ne 1)            
        {            
            $editor.insertText("`r`n")            
        }            
        $editor.InsertText($result)            
            
    }            
}            

Determine Start and End of a Selection in PowerShell Ise

Hello, I' m just working on a new inline outpout mode for SQLPSX a very ambious project to access SQL databases based on PowerShell.

There ISEs Output pane doesn't always satisfy my needs, because it truncates and wraps output but you can put your Output into the current or new Editor panes and that works fine. Today I focus on using the current editor pane. 

My goal is to insert the output exactly at the starting of the line below the selection if there is one or at the end of the editor.

First I found it difficult to get the start and end of a selection, but than I found a clever work around. I can even determine, if a selection was done from left to right or from right to left.

But don't use this possibility. It would be really confusing.

Here is my test code, with which I designed (honestly redesigned) the logic to evaluate the output location in SQLPSX (next release).

Have fun studying it. Just put the code below it into an ISE editor, select some text (or nothing) and press F5.

Bernd

function Get-InfoAboutCurentFilesEditor            
{            
    $editor          = $psIse.CurrentFile.Editor            
    $LineCount       = $editor.LineCount            
    $CaretLine       = $editor.CaretLine            
    $caretColumn     = $editor.CaretColumn            
    $CaretLineLength = $editor.GetLineLength($CaretLine)            
    $CaretAtLineEnd  = $caretColumn -eq $CaretLineLength + 1            
    $CaretInLastLine = $CaretLine -eq $LineCount            
    $SelectedText    = $editor.SelectedText            
    $hasSelection    = $SelectedText -ne ''            
                
    if ($hasSelection)            
    {            
        # delete selected text            
        $editor.InsertText('')            
        $StartLine =  $editor.CaretLine            
        $StartColumn = $editor.CaretColumn            
        # reinsert the text            
        $editor.InsertText($SelectedText)            
        $EndLine =  $editor.CaretLine            
        $EndColumn = $editor.CaretColumn            
        # restore the selection            
        $editor.select($StartLine, $StartColumn, $EndLine, $EndColumn)             
        $reverse =  $CaretLine -eq  $StartLine -and $caretColumn -eq $StartColumn            
    }            
    else            
    {            
        $StartLine =  $EndLine = $LineCount            
        $StartColumn = $EndColumn = $editor.GetLineLength($EndLine) + 1            
        $reverse = $False            
    }            
                
    if (  $EndLine -lt  $LineCount)            
    {            
        $outputLine = $EndLine + 1            
        $outputColumn = 1            
        $NeedsNewLine = $False            
    }            
    else            
    {            
        $outputLine = $EndLine            
        $outputColumn = $EndColumn            
        if ($EndColumn -ne 1)            
        {            
            $NeedsNewLine = $True            
            $outputColumn = $editor.GetLineLength($EndLine) + 1            
        }            
        else            
        {            
            $NeedsNewLine = $False            
        }            
    }            
@"
DisplayName:     $($psIse.CurrentFile.DisplayName) 
LineCount:       $LineCount

CaretLine:       $CaretLine
CaretColumn:     $caretColumn
CaretLineLength: $CaretLineLength
CaretAtLineEnd:  $CaretAtLineEnd
CaretInLastLine: $CaretInLastLine

hasSelection:    $hasSelection
reverse:         $reverse

outputLine:      $outputLine
outputColumn:    $outputColumn
NeedsNewLine:    $NeedsNewLine
"@            
}            
            
Get-InfoAboutCurentFilesEditor

New ISE work arounds -- determine start and end of textselection

I just guess that ISE is made by some Trappist monks. One never hears anything about future versions.
At most some closed Change Request at Microsoft Commit.
I get a little tired hoping, they will release anything soon.
If I need something, I'm going to write some dirty workaround.
Current problem: you can select text within the editor from left to right or from right to left ending with the caret at different positions.
Only the holy men didn't publish the API to get the start and the end of the selection. You can query only the caret position.
What I want acchieve, is to be able to insert in the line just below the current selection.
Well the following work around sets the caret to the beginning of the selection.
Put the code into an ise editor select testa and press f5, than repeat it by selecting the tesxt in the oposite direction and press again f5:

$editor = $psise.CurrentFile.Editor            
 $currentselection = $editor.SelectedText            
 if ($currentselection)            
 {            
    $initialCaretLine = $editor.CaretLine            
    $initialCaretColumn = $editor.CaretColumn            
    $editor.InsertText('')            
    $selstartline  = $editor.CaretLine            
    $selstartColumn = $editor.CaretColumn             
    $editor.InsertText($currentselection)            
    $selEndline  = $editor.CaretLine            
    $selEndColumn = $editor.CaretColumn             
    $editor.Select($selstartline, $selstartColumn, $selEndline, $selEndColumn)            
                
    "Selection is ($selstartline, $selstartColumn, $selEndline, $selEndColumn)"            
    "Initial Caret Position ($initialCaretLine, $initialCaretColumn)"            
 }            
            
            
# testa            
# testb            

Hope this really helps

Bernd

I can code Bad Influenced style too

I hope there is a better way to do this, but if PowerShell enforces the use of work arounds, I'll use them.
Please, if you find something more elegant, please drop a note.

Well to make SQLPSX one of the most powerfull tools to extract data from SQL-Server or Oracle databases I just want to transform a datarow into a tabular representation.


$datarow | Format-Table -auto

seems a good start, but as described in http://poshoholic.com/2010/11/11/powershell-quick-tip-creating-wide-tables-with-powershell/

you need to pass the result to Out-String -width 10000 or as many characters you want.
Sorry can't say -1 for unlimited. (Perhaps in V3, if we cry load enough?)

Oops not all columns included, lets add -property *

$datarow | Format-Table -auto -property * | Out-String -width 10000

Oops what happens now?  At the end you get some unwanted addition columns:

RowError, RowState, Table, ItemArray,  HasErrors

A problem well described in https://connect.microsoft.com. With no work around supplied.

Go there and vote for it.

And here is the code, that gives me the wanted result. I agree, it is ugly, but its result is fine:

$columns = ''            
            
                            foreach ($i in 0.. ($res[0].Table.columns.count -1))            
                            {            
                                if ($columns) { $columns +=  ', '+ $res[0].Table.Columns[$i].ColumnName }            
                                else { $columns = $res[0].Table.Columns[$i].ColumnName}            
                            }            
            
                            $c = '($res | ft -Property ' + $columns + ' -auto | Out-string -width 10000 -stream ) -replace " *$", ""-replace "\.\.\.$", "" -join "`r`n" '            
                            $text = Invoke-expression $c            

I hope this helps

Bernd