Author: admin

I blogged about a nice Visual Studio addon “StudioShell” that fits very well with SSMS “SQL Server Management Studio 2012“, and I found this tool very useful for presenting.  But, when I try running the (long time) famous “Start-Demo” script, it gave me some errors.  Well, Thanks (again) to StudioShell developer Jim Christoper, he gave me a hint to why this was happenning.  Basically, this old “Start-Demo” uses some .NET calls which StudioShell can’t interpret because of the way it was develop, so it need a makeover.

So, I proceed to dive into the code and make some basic changes so it can work inside StudioShell.  Here’s how it looks running it from SSMS executing a demo script, and asking for (?) Help:

I change the color scheme, and added a message pause after displaying the result of the onliner.

Also, the new function will work on a normal PowerShell console, and at the end of the start-demo it will put back your default console colors.

Here’s the updated “Start-DemoSS.ps1“code:

[sourcecode language=”powershell”]
###########################################################################
# Original Version: 1.1
# Updated to Version 2.0, Maximo Trinidad, 02/09/2012
#————————————————————————–
# Comments:
# 1. Customized the foreground color to Cyan and backgroundColor to Black.
# 2. Created a Dump color to default to White.
# 3. Added to put back the default foreground and background colors.
# 4. Commented out the ‘(!) Suspense’ option because Studio Shell can’t
# handle "$host.NestedPrompt".
# 5. Modify the Help menu to acomodate changes.
# 6. Commented out all "$Host.UI.RawUI.WindowTitle".
# 7. Replaced all "[System.Console]::ReadLine()" with "Read-Host".
# 8. Added an end of results ‘write-host"– Press Enter to continue –"’
# follow with a read-host similate a pause.
###########################################################################

function Start-Demo
{
param($file=".\demo.txt", [int]$command=0)

## – Saved previous default Host Colors:
$defaultForegroundColor = $host.UI.RawUI.ForegroundColor;
$defaultBackgroundColor = $host.UI.RawUI.BackgroundColor;

## – Customizing Host Colors:
$host.UI.RawUI.ForegroundColor = "Cyan";
$host.UI.RawUI.BackgroundColor = "Black";
$CommentColor = "Green"
$MetaCommandColor = "Cyan"
$DumpColor = "White"
Clear-Host

## – setting demo variables:
$_Random = New-Object System.Random
$_lines = @(Get-Content $file)
$_starttime = [DateTime]::now
$_PretendTyping = $true
$_InterkeyPause = 100
Write-Host -for $CommentColor @"
NOTE: Start-Demo replaces the typing but runs the actual commands.
.
<Demo [$file] Started. Type `"?`" for help>
"@

# We use a FOR and an INDEX ($_i) instead of a FOREACH because
# it is possible to start at a different location and/or jump
# around in the order.
for ($_i = $Command; $_i -lt $_lines.count; $_i++)
{
if ($_lines[$_i].StartsWith("#"))
{
Write-Host -NoNewLine $("`n[$_i]PS> ")
Write-Host -NoNewLine -Foreground $CommentColor $($($_Lines[$_i]) + " ")
continue
}else
{
# Put the current command in the Window Title along with the demo duration
$_Duration = [DateTime]::Now – $_StartTime
#X – $Host.UI.RawUI.WindowTitle = "[{0}m, {1}s] {2}" -f [int]$_Duration.TotalMinutes, `
# [int]$_Duration.Seconds, $($_Lines[$_i])
Write-Host -NoNewLine $("`n[$_i]PS> ")
$_SimulatedLine = $($_Lines[$_i]) + " "
for ($_j = 0; $_j -lt $_SimulatedLine.Length; $_j++)
{
Write-Host -NoNewLine $_SimulatedLine[$_j]
if ($_PretendTyping)
{
if ([System.Console]::KeyAvailable)
{
$_PretendTyping = $False
}
else
{
Start-Sleep -milliseconds $(10 + $_Random.Next($_InterkeyPause))
}
}
} # For $_j
$_PretendTyping = $true
} # else

#X – $_OldColor = $host.UI.RawUI.ForeGroundColor
$host.UI.RawUI.ForeGroundColor = $MetaCommandColor
#X – $_input=[System.Console]::ReadLine().TrimStart()
$_input= Read-Host
#X – $host.UI.RawUI.ForeGroundColor = $_OldColor

switch ($_input)
{
################ HELP with DEMO
"?"
{
Write-Host -ForeGroundColor Yellow @"
——————————————————————————–
Help Running Demo: $file
.
(#x) Goto Command #x (b) Backup (?) Help
(fx) Find cmds using X (q) Quit (s) Skip
(t) Timecheck (d) Dump demo (px) Typing Pause Interval
.
NOTE 1: Any key cancels "Pretend typing" for that line. Use <SPACE> unless you
want to run a one of these meta-commands.
.
NOTE 2: After cmd output, enter <CR> to move to the next line in the demo.
This avoids the audience getting distracted by the next command
as you explain what happened with this command.
.
NOTE 3: The line to be run is displayed in the Window Title BEFORE it is typed.
This lets you know what to explain as it is typing.
———————————————————————————
"@;
Write-Host "– Press Enter to continue –" -BackgroundColor white `
-ForegroundColor Magenta;
Read-Host; cls;
$_i -= 1
}

#################### PAUSE
{$_.StartsWith("p")}
{
$_InterkeyPause = [int]$_.substring(1)
$_i -= 1
}

#################### Backup
"b" {
if($_i -gt 0)
{
$_i —

while (($_i -gt 0) -and ($_lines[$($_i)].StartsWith("#")))
{ $_i -= 1
}
}

$_i —
$_PretendTyping = $false
}

#################### QUIT
"q"
{
Write-Host -ForeGroundColor $CommentColor "<Quit demo>"
$host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
$host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
cls;
return
}

#################### SKIP
"s"
{
Write-Host -ForeGroundColor $CommentColor "<Skipping Cmd>"
}

#################### DUMP the DEMO
"d"
{
for ($_ni = 0; $_ni -lt $_lines.Count; $_ni++)
{
if ($_i -eq $_ni)
{ Write-Host -ForeGroundColor Yellow "$("*" * 25) >Interrupted< $("*" * 25)"
}
Write-Host -ForeGroundColor $DumpColor ("[{0,2}] {1}" -f $_ni, $_lines[$_ni])
}
$_i -= 1
Write-Host "– Press Enter to continue –" -BackgroundColor white `
-ForegroundColor Magenta;
Read-Host; cls;
}

#################### TIMECHECK
"t"
{
$_Duration = [DateTime]::Now – $_StartTime
Write-Host -ForeGroundColor $CommentColor $(
"Demo has run {0} Minutes and {1} Seconds`nYou are at line {2} of {3} " -f
[int]$_Duration.TotalMinutes,
[int]$_Duration.Seconds,
$_i,
($_lines.Count – 1)
)
$_i -= 1
}

#################### FIND commands in Demo
{$_.StartsWith("f")}
{
for ($_ni = 0; $_ni -lt $_lines.Count; $_ni++)
{
if ($_lines[$_ni] -match $_.SubString(1))
{
Write-Host -ForeGroundColor $CommentColor ("[{0,2}] {1}" -f $_ni, $_lines[$_ni])
}
}
$_i -= 1
}

# #################### SUSPEND
# –> not working in StudioShell: help (!) Suspend (not working)
#
# {$_.StartsWith("!")}
# {
# if ($_.Length -eq 1)
# {
# Write-Host -ForeGroundColor $CommentColor "<Suspended demo – type ‘Exit’ to resume>"
# function Prompt {"[Demo Suspended]`nPS>"}
# $host.EnterNestedPrompt()
# }else
# {
# trap [System.Exception] {Write-Error $_;continue;}
# Invoke-Expression $(".{" + $_.SubString(1) + "}| out-host")
# }
# $_i -= 1
# }

#################### GO TO
{$_.StartsWith("#")}
{
$_i = [int]($_.SubString(1)) – 1
continue
}

#################### EXECUTE
default
{
trap [System.Exception] {Write-Error $_;continue;}
Invoke-Expression $(".{" + $_lines[$_i] + "}| out-host")
Write-Host "– Press Enter to continue –" -BackgroundColor white -ForegroundColor Magenta;
$_Duration = [DateTime]::Now – $_StartTime
#X – $Host.UI.RawUI.WindowTitle = "[{0}m, {1}s] {2}" -f [int]$_Duration.TotalMinutes, `
# [int]$_Duration.Seconds, $($_Lines[$_i])
#X – [System.Console]::ReadLine()
Read-Host;
}
} # Switch
} # for
## Next three list to put backl the console default colors and do a clear screen:
$host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
$host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
cls;
$_Duration = [DateTime]::Now – $_StartTime
Write-Host -ForeGroundColor $CommentColor $("<Demo Complete {0} Minutes and {1} Seconds>" `
-f [int]$_Duration.TotalMinutes, [int]$_Duration.Seconds)
Write-Host -ForeGroundColor $CommentColor $([DateTime]::now)
} # function
[/sourcecode]

Please, notice I named the script as “Start-DemoSS.ps1” meaning for StudioShell, still the function will be loaded as “Start-Demo“.

Usage Syntax – To load from script folder location:

.  .\Start-DemoSS.ps1

Usage Syntax – To excute a Demo.txt file:

Start-Demo .\MyDemoScript.txt

What’s inside the demo file? This is a text file containing PowerShell oneliners, and “#” comment lines.

In case you want to try StudioShell: http://studioshell.codeplex.com/

If you haven’t yet try it, go ahead download and install this Visual Studio Extension to your SQL Server Management Studio 2012.  This tool will integrate a PowerShell host into your SSMS which you will love for presentations and quick scripts developement from one envirment.  But Yes! this a just a simple host and you’ll find it useful for learning SQL PowerShell.

I will be presenting with this tool in my upcoming webinar with Patrick DeBlanc SQLLunch next week on February 15th “PowerShell Query for the T-SQL Developer” at 11:30am CST / 12:30pm EST.

To download StudioShell, here’s the link: http://studioshell.codeplex.com/

After you install this application, open SSMS,  from the top menu click on “View“, and select “StudioShell“.  Then, you can place this pane anywhere inside your SSMS application.  You can copy/paste t-sql code put remember to import the SQLPS module to have access to your SQL PowerShell commands.

A word of advice! if you venture to go, under ‘Tools | Options“, to change the “Console Choice” to be “Old School“, you will crash your SSMS application when you exit the StudioShell console.  So, DON’T make any changes to your Console Choice options, or you’ll end up loose all your work.

I’m a believer of Tools that can help you be productive, and this one caught my attention.  Please, try it!

Good Job JimChristopher (StudioShell Developer)/@beefarino !!

Sometimes working with data can be challenging.  As a SQL Developer, creating ETL solutions, is our responsibility all this data makes it to our users.  Sometime data manipulation might be limited in SSIS, we are force to look for other alternatives.  In my case, I use PowerShell, and I need a way to trap some import processing errors during the execution of the “Invoke-SQLcmd” cmdlet.  Then, I will be able to investigate why some on my data wasn’t making it into my SQL tables.

I try to use PowerShell the “Try-Catch” to trap the error but it was wrong implementation to trap the error in the SQL Server PowerShell “Invoke-SQLcmd” command.  So, How can I trap the command errors?  Well, you can use “CommonParameters“, which are an additional set of parameters automatically provided by Windows PowerShell.  In these additional set of parameters you will find the “-ErrorVariable” parameter which allow you to stores the error information in a given variable.  The error variable you provide in any cmdlet will be overridden each time the cmdlet execute, unless you add an “+” in front of it so it can append to it and collect error information.

For more
Information about all “CommondParameters“, execute at the PS prompt:

[sourcecode language=”powershell”]

Help About_CommonParameters -full

[/sourcecode]

Now, my implementation was to use the “-ErrorVariable” parameter to trap and save the error message with the SQLquery script that failed. Also, I make sure to the variable to a null value after I saved the results to disk. In my scenario, the data imported could break my SQL query and I want to make sure I can identify these records.

So, I’m using the ErrorVariable parameter in my “Invoke-SQLcmd” command.  Here’s a code snippet sample of it:

[sourcecode language=”powershell”]
$reccount = 1; $sqlerr = $null;

:

Foreach($item in $Data)
{
:

$outfile = "C:\ImportError\TSQLQUERY_$($reccount).sql";
$SqlQuery =  "Insert into $tablename ($tblfields) values($fldsvalue);";
Invoke-Sqlcmd -Database $dbName -ServerInstance $ServerName `
-Query $SqlQuery -ErrorVariable sqlerr;

if($sqlerr -ne $null)
{
"/*Error Found – on record# [ $reccount ]:> `r`n"+$Error[0] + "*/ `r`n" `
|  Out-File $outfile -Encoding ASCII;
$SqlQuery | Out-File $outfile -Encoding ASCII -Append;
"`r`n /* "+"".padleft(45,"=")+" */`r`n" `
| Out-File $outfile -Encoding ASCII -Append;
$sqlerr = $null;
}
:
$reccount++;
}
[/sourcecode]

Basically, I want to make sure my “$sqlerr” Error Variable is initialized as $null in both the beginning and after the error have been saved to the output file. Then, we can view later the Error message and the SQL script that error out to later view the output file displaying it in your SSMS or your favorite editor.

Keep in mind, this will not stop the errors from been displayed on screen.  But, you could use another Common Parameter: ” -ErrorAction SilentlyContinue” to prevent displaying the errors.

Sample output  file image:

I hope you find this code snippet useful!

For all whom attended the SQLPASS PowerShell Virtual Chapter – “Extending Your T-SQL Scripting with PowerShell” session on Wednesday November 16th, I finally got it posted here.

My sincere Apologies for the long delay in posting my session slides and demo scripts.  I thought I lost the material after having a disk drive corruption but I was able to recorver it.

Please, click on the link :< download now>: from SkyDrive. (rename file *.zip.txt to *.zip before extracting)

Don’t hesitate to contact me if you have any issues downloading it.