1

I haven't been on here for a while so apologies if I have made any faux pas in my question...

I am writing a small PS script that will update some tables in an SQL database. I would like to return the current data in the table and then ask the user if this needs changing.

Heres my code so far:

Get-SqlDatabase -serverinstance localhost | Out-GridView -PassThru -Title "Select Database to work on." | New-Variable DB -Force | FT -AutoSize

$DB.ExecuteWithResults("SELECT FileServerPath AS 'Current DMS Path' FROM DMSAdministration").tables

Switch(Read-Host "Would you like to change this?")
{
    Y {Write-Host -f Green "Begin Update DMS Path..."
        $DMSPath = Read-Host "Please enter DMS Path for $DB" 
        [string]$SQLCMD = "UPDATE DMSadministration SET FileServerPath = '$DMSPath' WHERE ConfigurationID = 'CONFIG1'"
        $DB.ExecuteNonQuery($SQLCMD)
        $DB.ExecutewithResults("SELECT FileServerPath FROM DMSAdministration").tables
      }
    N {Write-Host -f Yellow "Did not alter path!"}
}

The problem i'm having is that when I run this I select the database from Out-GridView. I then expect it to show me the result from $DB.ExecuteWithResults("SELECT FileServerPath AS 'Current DMS Path' FROM DMSAdministration").tables and then ask the user whether this should be changed. Currently, I select the DB and the Switch condition runs before the result is given.

TIA

Paul George
  • 33
  • 4
  • 1
    you have run into the _direct versus indirect output_ glitch. [*grin*] direct stuff - the various `*-Host` cmdlets, for instance - go almost directly to the screen. the _indirect stuff_ - what your `$DB.ExecutewithResults(` line produces - will be delayed approximately 300 ms to see if there is more coming to group with it. ///// the solution is one of the following ... **_[1]_** don't mix direct & indirect output to the screen. **_[2]_** force the indirect output to be direct. the easiest way is to pipe the indirect stuff to `Out-Host`. – Lee_Dailey Feb 01 '20 at 21:27
  • I hope the linked post explains the problem, which Lee's comment summarizes. Indeed, @Lee_Dailey, but note that the delay isn't about _grouping_, it is about _determining useful column widths_. Another - cumbersome, but ultimately most flexible - solution is to define formatting data for the type of objects output by the `$DB.ExecuteWithResults()` call. – mklement0 Feb 01 '20 at 22:07
  • @mklement0 - interesting! thank you for the added info. [*grin*] however, from my experience, the most visible result of the delay is the way things are grouped into batches - which certainly requires column choice & size calcs. the result seems rather similar to what happens when exporting objects with different properties to CSV . – Lee_Dailey Feb 01 '20 at 23:15
  • @Lee_Dailey: Not that I've delved into this too deeply, but the presence of _grouped_ output (from a _single_ command) suggests that formatting data is involved, in which case the 300 ms. delay does _not_ apply. – mklement0 Feb 01 '20 at 23:18
  • @Lee_Dailey: As for your CSV example: here's what happens analogously with display output: https://stackoverflow.com/a/55504393/45375 – mklement0 Feb 01 '20 at 23:21

0 Answers0