SQLPSX Release 2.3 is real tool for SQL-Server

Well it took some time, until Chad Miller published the current version of SQLPSX.
Concerning SQL-Server I dare say, it is a real tool to work with, with some unique features.

You get a lot of options for output format, e.g. list, which showes you the contents of the columns above each other. That helps, if you have varchar fields containing multiple lines. While the usual grid format only displays the first line and the text format looses column alignment, here you get nearly the complete content.
Nearly? Yes using ISE Output window, some wise man desided that truncating the output to current width of the Output window is what users have to want.

OK. With the limitations of the output window you have two options. First you can tweet about this last millenium decision. Second you can pray to Microsoft Connect and send a change Request for an option to programmatically set the witdth of the output pane).
Or you just ignore the two options and write a workaround.

The workaround I invented is the output format isetab. (Yes you find it in SQLPSX 2.3)
The trick is not using the Output pane for displaying the result, but to create a new editor and display it there in its whole witdh.

Working with the new mode, I soon end with lots of ofen editors and nearly as a joke to a coworker I designed the idea of an inline output format. I put the result into the calling editor, just below the calling selection or at the end, if there is none.

First trial was just 4 lines and it just felt good.

You can adjust the princip, if you have a function, which you invoke by ISE menu, which produces some wide output. Instead of sending the output to the output pane, you can use something similar to:

$text = "your code goes here>"            
         $editor = $psise.CurrentFile.Editor            
         $caretLine = $editor.CaretLine            
         $lineCount = $editor.LineCount            
         if (-not $selectedEditor.SelectedText)            
         {            
            $lineend = $editor.GetLineLength($lineCount) + 1            
            # Write-Host "Nothing Selected  Linecount: $lineCount LineEnd: $lineend"            
            if ($lineend -eq 0) {$lineend = 1}            
            # set selection to the end of the editor            
            $editor.Select($lineCount, $lineend, $lineCount, $lineend)            
         }            
         elseif ($caretLine -lt $lineCount)            
         {            
            # Write-Host "Usual case"            
            # set selection to the start of the next line            
            $editor.Select($caretLine + 1, 1, $caretLine + 1, 1)            
         }            
         else            
         {            
            $lineend = $editor.GetLineLength($lineCount) + 1            
            # Write-Host "At end  Linecount: $lineCount LineEnd: $lineend"            
            if ($lineend -eq 0) {$lineend = 1}            
            # set selection to the end of the editor            
            $editor.Select($lineCount, $lineend, $lineCount, $lineend)            
         }            
         $editor.InsertText($text)            

You can load this into an ISE editor and run it using F5. You see the line your code goes here added at the end.

Bernd

A Problem with Remove-Item $folder -recurse -force

I'm working with some testsenario, where I have repeatedly to remove a folder structure of about 3500 files of depth 8.

Remove-Item $folder -recurse -force

sometimes does not remove all the files. In some cases it yields the message, that a folder can't be deleted, because it is not empty, but when checking the folder is empty. In other not all files are removed.

To reach a clean state, I wrote the following script:

function Remove-ItemRecursiveBruteForce            
{            
    param(            
        [Parameter(Position = 0, Mandatory=$true, ValueFromPipeline=$false)]            
        [string]$folder,            
        [Parameter(Position = 1, Mandatory=$False, ValueFromPipeline=$false)]            
        $maxiterations = 20            
    )            
            
    $iteration = 0            
    while (  $iteration++ -lt   $maxiterations)            
    {            
        if (Test-Path $folder)            
        {            
            Remove-Item $folder -recurse -force -EA SilentlyContinue            
        }            
        else            
        {            
            "$folder deleted in $iteration iterations"            
            break            
        }            
    }            
    if (Test-Path $folder)            
    {            
        "$folder not empty after $iteration iterations"            
    }            
            
}            
            

I observe that it needs randomly between 2 and 11 iterations to remove my folder.

I'm using W7-32 bit and use PowerShell-ISE to run the script.

Is this problem just me, or is it OS specific. Any explanations?

1. Attemp to use .NET isolated storage with modeless WPK windows

 

The following attempt is not working with modeless windows (replace -show #-asjob    by -asjob). Using -show it works. Please run the code, do some selection within GUI, finally call  Get-value to check the result.

ipmo WPK                        
            
                        
$env:WPKResult = "auto"            
            
function action            
{            
    param (            
        $value            
    )            
    $env:WPKResult = $value            
                
    $changedProperty = $value            
    Write-Host "Value of $changedProperty changed to $value"                        
            
    try {            
    $useriStorage = [System.IO.IsolatedStorage.IsolatedStorageFile]::GetUserStoreForAssembly()            
    $file = New-Object System.IO.IsolatedStorage.IsolatedStorageFileStream("SQLSettigs.xml",[System.IO.FileMode]::Create,$useriStorage)            
    $xml = New-Object System.Xml.Serialization.XmlSerializer($changedProperty.GetType())            
    $xml.Serialize($file,$changedProperty)            
    $file.Close()            
    $useriStorage.Close()            
    }            
    catch {            
        $env:WPKResult = 'error'            
    }            
            
}            
                          
New-StackPanel {                        
    New-RadioButton -Content "auto"     -GroupName Results -IsChecked $True -On_Click { action "auto" }                        
    New-RadioButton -Content "list"     -GroupName Results -On_Click { action "list" }                        
    New-RadioButton -Content "table"    -GroupName Results -On_Click { action "table" }            
    New-RadioButton -Content "grid"     -GroupName Results -On_Click { action "grid" }            
    New-RadioButton -Content "variable" -GroupName Results -On_Click { action "variable" }            
    New-RadioButton -Content "csv"      -GroupName Results -On_Click { action "csv" }            
    New-RadioButton -Content "file"     -GroupName Results -On_Click { action "file" }            
} -show #-asjob                        
                        
            
function Get-value            
{            
    $useriStorage = [System.IO.IsolatedStorage.IsolatedStorageFile]::GetUserStoreForAssembly()            
    try {            
        $file = New-Object System.IO.IsolatedStorage.IsolatedStorageFileStream("SQLSettigs.xml",[System.IO.FileMode]::Open,$useriStorage)            
    }            
    catch {            
        Write-Host "UISettings.xml is not found"            
        return            
    }                        
    $xmlReader = New-Object System.Xml.XmlTextReader($file)            
    $xml = New-Object System.Xml.Serialization.XmlSerializer('abc'.GetType())            
    $newISEOptions = $xml.Deserialize($xmlReader)            
    $newISEOptions            
}

Please help - I want to send back a keystroke to the ISE window

Hello WPF friends, I need a little help. I want to add a button which sends F7 keystroke to the ISE-window from which I run this little WPK script.

ipmo WPK                        
                        
$env:WPKResult = "auto"                          
New-StackPanel {                        
    New-RadioButton -Content "auto"     -GroupName Results -IsChecked $True -On_Click { $env:WPKResult = "auto" }                        
    New-RadioButton -Content "list"     -GroupName Results -On_Click { $env:WPKResult = "list" }                        
    New-RadioButton -Content "table"    -GroupName Results -On_Click { $env:WPKResult = "table" }            
    New-RadioButton -Content "grid"     -GroupName Results -On_Click { $env:WPKResult = "grid" }            
    New-RadioButton -Content "variable" -GroupName Results -On_Click { $env:WPKResult = "variable" }            
    New-RadioButton -Content "csv"      -GroupName Results -On_Click { $env:WPKResult = "csv" }            
    New-RadioButton -Content "file"     -GroupName Results -On_Click { $env:WPKResult = "file" }            
    New-Button -Content "execute Sql"  -On_click { New-Button "I want to send F7 key-stroke to calling ISE" -show }                        
} -asjob                        
                        

This script is intended to be run in ISE and to add some cool GUI to SQLPSX.

Thanks

Bernd

A try in WPK (one way and only once)

The best I found in WPK seems to be the the following:

ipmo WPK            
            
$option = "To File" # this value might be prompted by the user            
             
             
New-StackPanel {            
    New-RadioButton -Content "To Text" -GroupName Results -IsChecked $True -On_Click {             
        $Resource.Result = "To Text"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
        }            
    New-RadioButton -Content "To Grid" -GroupName Results -On_Click {              
        $Resource.Result = "To Grid"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
        }            
    New-RadioButton -Content "To File" -GroupName Results -On_Click {              
        $Resource.Result = "To File"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
    }            
    New-RadioButton -Content "To CSV" -GroupName Results -On_Click {              
        $Resource.Result = "To CSV"             
        $info = $window | Get-ChildControl info            
        $info.text = $Resource.Result            
        $env:WPKResult = $Resource.Result            
    }            
    New-textBox -Name info             
    } -On_Loaded {            
            $info = $window | Get-ChildControl info            
            $info.text = $Resource.Result            
    }-asjob -resource @{Result = $option}            
            

I can modify the value within GUI and query it outside

$env:WPKResult

I misuse the processes environment, which is shared between the runspaces. Hmm better than pidgeon carrying USB sticks, but it is restricted to strings.

I guess I have to really dive into PowerBoots  (and that is the polite version ;-) of my opinion about WPK)

Have some fun trying to find further work arounds.

Bernd

A small Async PowerBootsWindow

Hello,
here I'll show a little async PowerBoots Gui, which is able to modify variables in the main thread.

ipmo PowerBoots            
            
$options = @{ "Results" = "To Text" }            
             
Boots {             
    StackPanel {            
        RadioButton -Content "To Text" -GroupName Results -IsChecked $True -On_Click { $options.Results = "To Text" }            
        RadioButton -Content "To Grid" -GroupName Results -On_Click {  $options.Results = "To Grid" }            
        RadioButton -Content "To File" -GroupName Results -On_Click {  $options.Results = "To File" }            
        RadioButton -Content "To CSV" -GroupName Results -On_Click {  $options.Results = "To CSV" }            
                }            
      } -async            
            
            

Is it possible to do the same thing with WPK?

A viewer for Crystal Reports with PowerShell (partially working)

Hello,
this time I tried to build a small Report Viewer using the components from Visual Studio 2008 with PowerShell. It shows that using PowerShell here makes some things rather easy.

I'm using this to display old Reports made with Crystal Reports 8.5 using ODBC Datasources to SQL-Server.

Reports based on views works fine, I can set database and formulars.

But I have still a problem with reports based on stored procedures. Setting $table.location doesn't work anymore as in the past. I go the error table not found. Any hints welcome.

And here is the code if you want to try something like this:

Function Show-CrystalReport            
{            
    param(            
        $reportPath,             
        $servername,             
        $databasename,             
        $userId,             
        $password,            
        $RecordSelectionFormula,            
        $formulas,            
        $parameters            
        )            
                    
            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.Shared')            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.CrystalReports.Engine')            
    [reflection.assembly]::LoadWithPartialName('CrystalDecisions.Windows.Forms')            
            
    $report = New-Object CrystalDecisions.CrystalReports.Engine.ReportDocument            
    $report.load($reportPath)            
            
    $report.ParameterFields | % {             
        if ($parameters.keys -contains $_.Name)            
        {            
            $report.SetParameterValue($_.Name, $parameters[$_.Name])            
        }            
     }               
            
    $report.DataDefinition.FormulaFields | % {            
        if ($formulas.keys -contains $_.Name)            
        {            
            $_.Text = $formulas[$_.Name]            
        }            
    }            
            
    if ($RecordSelectionFormula) {            
        $report.RecordSelectionFormula = $RecordSelectionFormula            
    }            
            
    foreach ($Table in $report.Database.Tables)            
    {            
        $table            
        $tli = $Table.LogonInfo            
        $li = $tli.ConnectionInfo            
        Write-host "location : $($table.location)"            
                    
        $li.ServerName = $servername            
        $li.DatabaseName = $databasename             
        $li.UserID = $userId            
        $li.Password = $password            
        $Table.ApplyLogOnInfo($tli)            
        # the following doesn't work as in the past            
        if ( $table.location -contains '.')            
        {            
            $table.location -match  '(.*)\.(.*)'            
            $table.location = "$($databasename).dbo.$($matches[2])"            
        }            
        $table.location             
                   
    }            
            
    $rv = New-Object CrystalDecisions.Windows.Forms.CrystalReportViewer            
    $rv.ReportSource = $report            
    $rv.Dock = [System.Windows.Forms.DockStyle]::Fill            
            
            
    $form = New-Object Windows.Forms.Form            
                 
    $form.Height = 810            
    $form.Width= 1210            
    $form.Controls.Add($rv)            
    $rv.Show()            
    $form.ShowDialog()            
            
    $rv.Dispose()            
    $report.Dispose()            
    $form.Dispose()            
            
}