Take a look at my SQL Server SMO article “Using SQL Server Management Objects with PowerShell” in the “SQL Server Pro” April magazine is available now on the internet: http://www.sqlmag.com/article/windows-powershell/sql-server-managment-objects-powershell-144832
Category: PowerShell
About PowerShell.
Orlando IT Pro Camp Keiser University 3/23/2013 – Presentation
Here’s the “Getting Started with Windows 8 PowerShell” presentation plus one sample script to get you started.
Topic description: This is an introduction session on how to work with PowerShell 3.0 in Windows 8. I will cover find PowerShell, how to create your PowerShell shorcuts, updating PowerShell help documentation, and briefly covering the some of the enhancements in the ISE editor.
Click on the following link:
Thanks and keep participating with the IT Community!
Don’t worry – FLPSUG website under construction
It’s long overdue to our the Florida PowerShell User Group website to be update to something modern. So, we are doing some reconstruction and using DotNetNuke as our new CMS.
Please be patient! In the meantime you can connect to my blog page where I’m be posting the progress and new upcoming thing in our FLPSUG group.
Thank you for your support.
Orlando Code Camp 2013 – PowerShell sample code…
Saturday March 16th, 2013
First, Thanks to the organizers for having me speak about PowerShell and the gift they gave to all the speakers. Also, Thanks to all the sponsor’s and all whom attended my PowerShell sessions:
1. PowerShell Working with PSObjects.
2. PowerShell working with XML.
Here’s the zip file with both presentation and sample code for download:
Please don’t hesitate to contact me if you have any questions.
It was a pleasure to participate in this event.
Quickblog on PowerShell Here-String/Splatting simple formatting
Sometime is nice to find simple samples that can make life easy. Here’s a quick sample on how you can use this string manipulation to make it readable. Also, I included a way to dynamically create a PowerShell variable from an existing (it may be useful for someone) just as a proof-of-concept.
In this sample I’m going to create a T-SQL script and display it in my PowerShell console. First, let’s create some variables that will be use to replace some values in our string:
[sourcecode language=”powershell”]
## – Dynamically creating a variable with a value:
$SubstituteVariable = "SQLDatabase";
New-Variable $SubstituteVariable -Value "Developer";
$Tablename = "AddressBook";
[/sourcecode]
The above code will create three PowerShell variable but we are end up using only the $SQLDatabase and the $Tablename in our T-SQL script.
The next few PowerShell code will show you a simple ways to initialize a string variable holding the T-SQL scripts.
1. One-liner sample – All acceptable except when using complex Multi-line T-SQL Scripts.
[sourcecode language=”powershell”]
## [1] – Normal string one liner:
$SQLquery = "Select [firstname], [lastname] from [$SQLDatabase].[dbo].[$Tablename]";
$SQLquery;
[/sourcecode]
2. Using Here-String/Splatting, you can change the above sample and make it a multi-line string. This still is a One-liner, and it use the tab in front of the column names. *note: In PowerShell V3.0 the tab will be ignore but in PowerShell V2.0 it will work OK.
[sourcecode language=”powershell”]
## [2] – Using Here-String/Splatting. This string contains manual tabs done in and editor the it was copy/paste to the console: (bug in V3 console only. ISE works.)
$SQLquery1 = @"
Select
[firstname],
[lastname]
from [$SQLDatabase].[dbo].[$Tablename]
"@;
$SQLquery1;
Write-Host "$SQLquery1" -ForegroundColor ‘Yellow’;
[/sourcecode]
3. The last Here-String sample we are using the .NET ‘-f’ string formatter which will allow you to replace the values for {0} and {1} place holder. In this sample code inside the string i’m hard coding tab as `t (tick symbol plus t) which in this case it works OK in both PowerShell V2 and V3:
[sourcecode language=”powershell”]
## [3] – Using Here-String/Splatting with .NET ‘-f’ formatting:
$SQLquery2 = @"
Select
`t[firstname],
`t[lastname]
from [{0}].[dbo].[{1}]
"@ -f $SQLDatabase, $tablename;
Write-Host "$SQLquery2" -ForegroundColor ‘Cyan’;
[/sourcecode]
4. When using a PowerShell editor (ISE or others) an invalid Here-String variable PowerShell will give an error if you try to use tabs on each line of the code: (can’t use white space in editor with Here-String)
[tab] $x = @”
[tab] Testing
[tab] Testing
[tab] “@;
Basically, Here-String variable works great when you want to store a multi-line string but have some caveats:
1. PowerShell v3 console only. Manual tabs are ignored.
2. When using splatting (@”..”@) the @” can begin in any column position but the terminating “@ ends on the beginning of the new line. See sample #3.
3. When using a PowerShell editor Whitespaces in a Here-String block of code are not allowed.
Sarasota IT Pro Camp 2013 – PowerShell presentation…
Once again, another great Saturday IT Pro event at Keiser University in Sarasota, Florida. It was awesome to see some great speakers giving their sessions, and I got to meet Pinal Dave which is pretty well known in the SQL Server Community Worldwide. See below picture (Me and Pinal Dave).
Microsoft IT Pro Evangelist Blain Barton put together a great Windows Azure session with Herve Roggero (Windows Azure MVP). See below picture.
Thank You all for attending my PowerShell Working with .NET PSObject session. Here’s is my full presentation with all the PowerShell scripts I meant to cover. Please feel free to try them out. During our session I was able to cover only one of the three Excel PowerShell sample.
Thank You Kieser University for having us!
QuickBlog: How to remove Array element (or range) with PowerShell…
As I look for a way that I could eliminate a single or a range of elements, I found many C# .NET code on how do it. But only one of the answer in many caught my eye. This answer was “System.Collections.ObjectModel.Collection“. This help me search deeper and of course found that there’s a couple of methods I could use if I create my Array as an ArrayList type.
This way I got available two methods I can use: .RemoveAt( element# ) and .RemoveRange( AtElement# , HowMany# ) .
So, in scenario where you want to remove some elements of the array in PowerShell try using the following typed accelerator [System.Collections.ArrayList] .
Here’s a basic sample code:
[sourcecode language=”powershell”]
## – creating your Arraylist .NET object and creates 20 elements:
[System.Collections.ArrayList] $b = 1..20;
## – Here’s how you verify the type of object you created:
$b.GetType()
## – This example will remove Array element #4 and display the results:
$b.RemoveAt(4);
$b
## – This example will remove a range of 3 elements starting at element #7:
$b.removerange(7,3)
$b
[/sourcecode]
It’s all part of the beauty of .NET with PowerShell!
Show-PSDemo function – Simple way to demo scripts.
While working on my PowerShell demo scripts today, I decided to create a simple function to display my scripts during my presentations. Here’s my new and simple Show-PSDemo function:
[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.1.15
# Created on: 1/13/2013 9:07 AM
# Created by: Maximo Trinidad
# Organization: PutItTogether
# Filename: Show-PSDemo_function.ps1
# Version: 0.1
#========================================================================
function Show-PSDemo{
Param([String] $FileLocation)
## – Verify for a valid file location:
if((Test-Path -Path $FileLocation) -ne $true)
{
Write-Host "No script files to execute!" `
-ForegroundColor ‘Yellow’;
Break;
}
else
{
[Array] $executethis = Get-Content $FileLocation;
};
## – 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";
$StartDemoTime = [DateTime]::now; $i = 0;
Clear-Host;
Write-Host "Demo Start Time: $([DateTime]::now)" -ForeGroundColor ‘White’;
Write-Host "`t Running Script file: $FileLocation" -ForegroundColor ‘Yellow’;
foreach($line in $executethis)
{
$i++
## – Identify comment lines:
if($line.Startswith(‘#’)){
Write-Host -NoNewLine $("`n[$i]PS> ")
Write-Host -NoNewLine -Foreground ‘Green’ $($($line) + " ")
}
Else
{
## – add section identify oneliners with continuation tick:
[string] $Addline = $null;
if($line -match ‘`’)
{
#Write-Host " Found tick = `t`r`n $($line)" -ForegroundColor yellow;
$Addline = $line.replace(‘`’,”).tostring();
$Scriptline += $Addline;
$tickFound = $true;
$continuation = $true;
## – List oneliner with continuation tick:
Write-Host -NoNewLine $("`n[$i]PS> ");
Write-Host -NoNewLine $line;
}
else
{
## – identify the last line of a continuation oneliner:
if($tickFound -eq $true)
{
$Scriptline += $line;
$tickFound = $false;
$continuation = $false;
## – List oneliner with continuation tick:
Write-Host -NoNewLine $("`n[$i]PS> ");
Write-Host -NoNewLine $line "`r`n";
}
Else
{
## – Single onliner found:
$Scriptline = $line;
$continuation = $false;
Write-Host -NoNewLine $("`n[$i]PS> ")
Write-Host -NoNewLine $Scriptline "`r`n";
};
};
if($continuation -eq $false)
{
## – Executive:
Write-Host "`r`n`t Executing Script…`r`n" -ForegroundColor ‘Yellow’;
Invoke-Expression $(‘.{‘ +$Scriptline + ‘}| out-host’);
$Scriptline = $null;
}
## – ——————————————————————–
if($continuation -eq $false)
{
Write-Host "`r`n– Press Enter to continue –" -ForegroundColor ‘Magenta’ `
-BackgroundColor white;
Read-Host;
};
};
};
$DemoDurationTime = ([DateTime]::Now) – $StartDemoTime;
Write-Host ("`t <Demo Duration: {0} Minutes and {1} Seconds>" `
-f [int]$DemoDurationTime.TotalMinutes, [int]$DemoDurationTime.Seconds)
-ForeGroundColor ‘Yellow’ ;
Write-Host "`t Demo Completed at: $([DateTime]::now))" -ForeGroundColor ‘White’;
## – Set back to Default Color:
$host.UI.RawUI.ForegroundColor = $defaultForegroundColor;
$host.UI.RawUI.BackgroundColor = $defaultBackgroundColor;
};
## – loading:
## . .\Temp\Show-PSDemo_Function.ps1
## Show-PSdemo -FileLocation C:\temp\PowerShell_SQLdata.ps1
[/sourcecode]
It has one parameter “-FileLocation” which is you full folder and file location. The file extension doesn’t matter as long is a text file with PowerShell code in it.
It allows to:
1. The use of Comments lines.
2. The use of the “$_” in a “Select-Object“.
3. Allows the use of the “`” tick which identify a line continuation.
4. A “Pause” is included after executing each line or block of code.
Simple to load as a function, then execute:
PS C:\> . .\YourScriptFolder\Show-PSDemo_function.ps1
PS C:\> Show-PSDemo -FileLocation C:\YourScriptFolder\MyDemoScript.ps1
It works in both PowerShell Console and PowerShell ISE editor.
Microsoft free PowerShell Language Specifications available for download…
Microsoft has done a great job is providing documentation about PowerShell. Here’s two important links:
1. PowerShell Language Specification Version 2.0 at: http://www.microsoft.com/en-us/download/details.aspx?id=9706
2. PowerShell Language Specifiction Version 3.0 at: http://www.microsoft.com/en-us/download/details.aspx?id=36389
It’s good reading material.
Thank You Microsoft!
In 2013 – Make it personal, learn about PowerShell.
Here’s a response a gave in Linkedin this month and I thought I should blogged about it.
There’s no excuse for not wanting to learn about PowerShell
First, There’s really the only requirement would be to have PowerShell installed on your machine. If you have Windows 7 then you are set to go. Next look for free resource material which you can find on the internet:
http://powershell.org/wp/powershell-books/http://powershell.com/cs/
http://www.maxtblog.com/2012/07/windows-powershell-v3-0-resource-links-start-now/
http://www.maxtblog.com/2010/07/free-powershell-resources/
Also, PowerShell include an extensive help documentation at your fingertips. Just type the following command to access all the “About_*” topics you can read on:
Ps C:> help About_*
Start first reading about the “about_execution_policies“:
PS C:> help about_execution_policies
There’s some TechNet 2011 and 2012 recorded sessions that are very informative. *Check them out:
Channel 9 – Look at the PowerShell videos (many areas): http://channel9.msdn.com/tags/PowerShell/
TechEd North America 2012 “PowerShell” videos: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012?sort=sequential&direction=desc&term=PowerShell
* I recommend to start learning everything about PowerShell version 2.0.
Then, you can use forums and even use the Twitter hash tag #PowerShell and #PShelp so the community can give assits those in need.
PowerShell V2.0 -> PowerShell v3.0 -> PowerShell V2.0…
There one thing to take into consideration when working with PowerShell V3.0. Most companies might still being in process of adopting V2.0 and if is not already in place. Version 3.0 brings a lot to the table but can cause some headaches if you forget the script you built (using 3.0) might not work in Version 2.0. Keep a closer look at the v3.0 member enumerator enhancement (which I love), and the behavior changes on some of the existing(updated) cmdlets.
So, with this in mind. Ask yourself, will my script work in PowerShell V2.0?. Then test the script loading the “PowerShell -version 2.0” at the PowerShell 3.0 console prompt to execute the script. This way you can verify, and at the same time learn to work with both V2 and V3 difference.
PowerShell v3.0 is a great .NET scripting language to learn! Once you start using PowerShell you’ll never stop.
🙂