Ready, Set, Go SQLSaturday !!! Come to see us…

Yes! The stage is set, and we are ready to give some Awesome presentations.  Please, don’t be a stranger!! Come to both upcoming SQLSaturday events: South Florida (SQLSat40) and Orlando (SQLSat49).   We finally organized a full day of PowerShell.  It’s going to be fun and very informative.

Please, click on the following link for both the registration and to see the full event schedule with all other great tracks at:

On July 31st, South Florida SQLSaturday #40: http://www.sqlsaturday.com/40/schedule.aspx

On October 16th, Orlando SQLSaturday #49: http://www.sqlsaturday.com/49/schedule.aspx

Here’s the PowerShell Tracks for both events:

Start Time SoFla – SQLSaturday #40 – PowerShell Track
8:30 AM Ronald Dameron
  Why SQL Server DBAs should learn PowerShell
9:45 AM Ronald Dameron
  Automate ID Administration w/ PowerShell & SQLPSX
11:00 AM Aaron Nelson
  PowerShell for the Data Professional
1:00 PM David Corrales
  Sneak Preview: SAPIEN’s Visual PowerShell 2011
2:15 PM Maximo Trinidad
  Working with SQL Server – SQLPS
3:30 PM Maximo Trinidad
  Using PowerShell with SQL Server Agent
8:30am Argenis Fernandez
  Multi-Server Management with SQL Server 2008+
Start Time Orlando -SQLSaturday #49 – Powershell Track
9:00 AM Maximo Trinidad
  Working with SQL Server – SQLPS
10:15 AM Aaron Nelson
  The Dirty Dozen: PowerShell Scripts for Busy DBAs
11:30 AM Maximo Trinidad
  Using PowerShell with SQL Server Agent
1:15 PM Ron Dameron
  Why a DBA Should Learn PowerShell (MINI)
1:30 PM Ronald Dameron
  Automate Login Administration & Compliance Reports
2:45 PM Aaron Nelson
  PowerShell 2.0 Beyond the Dirty Dozen
4:00 PM Chad Miller
  ETL with PowerShell

Remember, Registration is free!!

Invoke-SQLCmd minor issue running some SQL Stored-Procedures…

First time I notice something strange with “Invoke-SQLCmd”, I was when executing the system Stored-Procedure “SP_Who2” and got the following error message:

PS C:\Users\Max> $sqlWho = Invoke-Sqlcmd “SP_Who2”
Invoke-Sqlcmd : The pipeline has been stopped.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : A column named ‘SPID’ already belongs to this DataTable.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], DuplicateNameException
    + FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users\Max>

I was trying to create a collection of object with the results from my “SP_Who2” and got the error stating “.. A column named ‘SPID’ already belongs to this DataTable..”.   So, I end up scripting out the SP_Who2, finding there are two columns with the same name ‘SPID’ and PowerShell didn’t like it.  I made the change to rename one of the columns to be SPID2 and save the T-SQL script to my PowerShell script file. 

And, the next time the ran my “Invoke-SQLCmd”, I had no problems and got my results so I could manipulate my .NET objects.

So, this is to make you aware that you will experience this minor issues when executing some of the SQL system stored-procedures.  And, for those who wonder… is this a BUG??  I really don’t think so!!  Because, the issue is in some of the system stored-procedures.  Should I submit this issue to the SQL Team to fix all stored-procedures generating columns with the same name?  This could be a major and unnecessary task.  Anyway, you were served!!

Happy PowerShelling!!

QuickBlog: Build a list of SQL Server without SMO…

Here I’m sharing a piece of PowerShell  code snippet which allow you to create a string array of all your SQL Server with their instances.  This code is a .NET namespace and use the enumerator class “SqlDataSourceEnumerator” to give you all available SQL Server instances in your network.

Just go ahead and give it a try:

$SQLInst = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() `
| select @{Expression={if(($_.InstanceName).length -gt 0)`
{$_.ServerName+"\"+$_.InstanceName}else{$_.ServerName}}; Name="SQLName"}
[Array] $MyServers
$MyServers = foreach($i in $SQLInst) {$i.SQLname}

Please, notice I’m using the reverse accent character for line continuation so it’s easy to view.  This is a good code snippet, if you need to provide a script solution that may required to get some SQL information from scheduled task on a server that have no SQL engine installed.

Hint: Now, you may have another way to void typing all your server in your PowerShell script.

Also, take a look at this other blog from Thomas LaRock “SQL RockStar” about “Finding Servers on Your Network” – Excellent material – http://thomaslarock.com/2009/12/finding-servers-on-your-network/  (Thanks to Chad Miller! For providing the link)

Here’s another snippet using above Thomas LaRock blog but I use PowerShell to extend the command “sqlcmd /L”:

$s = sqlcmd /L
[array] $Servers = $null
[array] $Servers = foreach($i in $s){if($i.length -gt 0){$i.Trim()}}
$Servers

Happy PowerShelling!!!

🙂

Technorati Tags: ,

PowerShell Modules – How to create Aliases for my functions?

There’s tons of information about PowerShell Modules on the internet.  Then I realized, in my PowerShell Modules, How do I create Aliases for my functions?  I couldn’t find any information on the internet about Aliases in modules.  So, thanks to Ed Wilson (Microsoft Script Center), he gave me a hint in one of his articles: http://blogs.technet.com/b/heyscriptingguy/archive/2010/02/21/hey-scripting-guy-february-21-2010.aspx

I’m assuming you already know how to create a module *psm1 file. Well, don’t worry!  The following sample script will get you started.  And, if you want to convert a PowerShell script to a Module file, is as simple as copy the *ps1 to *psm1 file.  Basically, that’s it!!

So, before I created my module file, I first created a PowerShell script “HelloWorld.ps1” which contains the following code:

## NAME: HelloWorld.ps1
Function Show-HelloWorld {
 [CmdletBinding()]
   Param($arg)
           Write-Host "Hello World!!.." $arg
};Set-Alias shw Show-HelloWorld -Description "Sample Hello World in module"

After verifying that my function works, then I copy/rename my PowerShell script to PowerShell module file “HelloWorld.psm1” and saved it in my new module folder name “HelloWorld”.

Now, here’s what I found when creating my Aliases for my module:

  1. In the *psm1 file, I could use either: Set-Alias or New-Alias but not both when buidling my manifest.  One catch, I discovered, if I use the ‘-Description’ parameter in either Cmdlets, the function description won’t load with the module.  (its a possible bug)
  2. The last line in the *psm1 file need to have the Cmdlet “Export-ModuleMember” (a shown below) to include the Aliases.
  3. I think is a good practice to named you PS Module script the same way your Module folder.
  4. Create the Module folder name and place the module *psm1 file in it.

Here’s sample script code:

## PS Module Name: HelloWorld.psm1
Function Show-HelloWorld {
 [CmdletBinding()]
   Param($arg)    ?
   Write-Host "Hello World!!.." $arg
};
#-- Set-Alias will work but can't be New-Alias
#Set-Alias shw Show-HelloWorld -Description "Sample Hello World in module"
New-Alias -Name shw -value Show-HelloWorld -Description "Sample Hello World in module"

#-- Need to be included at the end of your *psm1 file.
export-modulemember -alias * -function *

Now, we need to create our new module folder under our users documents “..\WindowsPowerShell\Modules” called “HelloWorld”.   Using our PowerShell console, we need to create our PowerShell manifest *psd1 file using the following line and pressing enter to all other prompts:

New-ModuleManifest -Path 'C:\Users\YourName\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psd1' -ModuleToProcess 'C:\Users\YourName\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psm1'
PS C:\Users\Max> New-ModuleManifest -Path 'C:\Users\max\Documents\Win...
' -ModuleToProcess 'C:\Users\max\Documents\WindowsPowerShell\Modules\...

cmdlet New-ModuleManifest at command pipeline position 1
Supply values for the following parameters:
NestedModules[0]:
Author:
CompanyName:
Copyright:
Description:
TypesToProcess[0]:
FormatsToProcess[0]:
RequiredAssemblies[0]:
FileList[0]:

Keep in mind, to start using New-ModuleManifest, you only need to use two parameters: “-path” – location to store your manifest file and “-ModuleToProcess” – location of your module file.

Don’t be afraid to skip the prompts, you can always go back into your recently build manifest file *psd1 and manually filled the information.  After successfully running the “New-ModuleManifest” and created the manifest file “HelloWorld.psd1”, I can proceed to load our new module using “Import-Module HelloWorld”.  If there were any errors, I would need to go back to file my *psm1 module file.

I’m are ready to test the new “HelloWorld” module executing the “Import-Module HelloWorld” im the PowerShell Console:

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\Max> New-ModuleManifest -Path 'C:\Users\max\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psd1'
-ModuleToProcess 'C:\Users\max\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psm1'

cmdlet New-ModuleManifest at command pipeline position 1
Supply values for the following parameters:
NestedModules[0]:
Author:
CompanyName:
Copyright:
Description:
TypesToProcess[0]:
FormatsToProcess[0]:
RequiredAssemblies[0]:
FileList[0]:
PS C:\Users\Max>
PS C:\Users\Max> Import-Module HelloWorld
PS C:\Users\Max> Get-Alias Shw

CommandType     Name                                                Definition
-----------     ----                                                ----------
Alias           shw                                                 Show-HelloWorld

PS C:\Users\Max> shw
Hello World!!..
PS C:\Users\Max> shw Max
Hello World!!.. Max
PS C:\Users\Max>

Now, I can either use my new Cmdlet function “Show-HelloWorld” or just use the Alias “shw” to execute my function.  That’s it!!  I’ve just created, loaded and tested a new module in our PowerShell Console.  There’s room to grow here!! 

Remember, Don’t TAP OUT!!  Happy PowerShelling!!

South Florida SQL Server User Group (SFSSUG) is proud to announce the following:

USER GROUP MEETINGS

image

Meetings are from 6 pm to 8:30 pm unless otherwise indicated.
West Palm Beach: CompTec, 1750 North Florida Mango, Suites 302 and 303, West Palm Beach, FL 33409
Miramar: DeVry University, 2300 SW 145th Avenue, Miramar, FL 33027.

*There is no regular meeting on July 28. Instead, all the SQL Saturday volunteers will meet at DeVry to organize and prepare for SQL Saturday #40!

07/31/10 – SQLSaturday # 40

Less than 3 weeks to go!

Schedule is now available!! Check it out at:
http://www.sqlsaturday.com/40/schedule.aspx

Please join us for a free day of SQL Server training at DeVry University, 2300 SW 145th Avenue, Mirimar, FL 33027. Just register at:

http://www.sqlsaturday.com/40/eventhome.aspx

Some Good Twitter and SQL Server PowerShell resources

I’m not forgetting all the hard working MVP’s and non-MVP’s DBA’s that are blown away with PowerShell.  Here’s my favorite web list and twitter names:

Chad Miller (@cmille19): http://sev17.com/

Ron Dameron (@RonDBA): http://www.simple-talk.com/author/ron-dameron/

Aaron Nelson(@SQLvariant): http://sqlvariant.com/wordpress/

Laerte Junior (@LaerteSQLDBA): http://laertejuniordba.spaces.live.com

Argenis Fernandez (@afernandez): http://www.sqlps.com/blog/

Patrick LeBlanc (@patrickdba): http://www.sqllunch.com/

Allen White (@SQLRunr): http://sqlblog.com/blogs/allen_white/default.aspx

Buck Woody (@BuckWoody): http://blogs.msdn.com/b/buckwoody/

Jen and Sean McCown (@MidnightDBA): http://midnightdba.itbookworm.com/Default.aspx, http://www.midnightdba.com/Jen/ and http://www.midnightdba.com/DBARant/

Keep on the good work!!

I love SQL Server PowerShelling!!!  (@MaxTrinidad)  🙂

Free PowerShell Resources:

Here’s some other resource information about PowerShell in general that you should miss:

SAPIEN Technologies – from their Community Tools page (free):

  • PowerShell TFM V1 eBook – A most to have book. (Acrobat Reader requiered)
  • Windows PowerShell Help 2.0
  • iPowerShell – if you own an iPhone. (Yes! I’m a Microsoft MVP and own an iPhone).

http://www.primaltools.com/downloads/communitytools/

image image

Shay Levy’s PowerShell Toolbar – Nice to have it on the browser: http://powershell.ourtoolbar.com/

image image

PowerGUI Pro – a free community for PowerGUI, a graphical user interface and script editor for Microsoft Windows PowerShell!  Get it at: http://www.powergui.org/

image Channel 9 – Look at the PowerShell videos (many areas): http://channel9.msdn.com/tags/PowerShell/

IDERA – Tools for SQL Server, Sharepoint and PowerShell: http://www.idera.com/Content/Home.aspx?ProdCat=PS

And I won’t forget  image Microsoft Script Center: http://technet.microsoft.com/en-us/scriptcenter/default.aspx  Good Job!!

Happy PowerShelling!!

Two for Two – SQLSaturdays #40 & #49 SQL PowerShell Tracks !!

Finally, after 2 years of speaking about PowerShell working with SQL Server, everyone has realized to give it a try and enjoy the benefits it brings in automation and productivity.  In Florida, thanks for all the organizers of SQLSaturday to tells us submit our sessions so we can keep spreading the word and educate DBA’s and non-DBA’s about this amazing technology.   So, here’s a brief list of all the submitted sessions to be available in the upcoming Florida’s SQLSaturday events: 

For SQLSaturdays Events and registration: http://www.sqlsaturday.com/default.aspx 

SQLSaturday #40, South Florida – July 31st    
Title
Speaker Level
Automate ID Administration w/ PowerShell & SQLPSX Ronald Dameron Intermediate
Database Hardening via PowerShell Ronald Dameron Intermediate
PowerShell 2.0 Beyond the Basics Aaron Nelson Intermediate
PowerShell for the Data Professional Aaron Nelson Beginner
Sneak Preview: SAPIEN’s Visual PowerShell 2011 David Corrales Beginner

Working with SQL Server – SQLPS

Maximo Trinidad Intermediate
Using PowerShell with SQL Server Agent  Maximo Trinidad Intermediate
Why SQL Server DBAs should learn PowerShell Ronald Dameron Beginner
SQLSaturday #49, Orlando – October 16th     
Title
Speaker Level
ETL with PowerShell Chad Miller Intermediate

Automate Login Administration & Compliance Reports

Ronald Dameron Intermediate
PowerShell 2.0 Beyond the Dirty Dozen Aaron Nelson Intermediate
The Dirty Dozen: PowerShell Scripts for Busy DBAs Aaron Nelson Beginner
Multi-Server Management with SQL Server 2008+
Argenis Fernandez Beginner
Using PowerShell with SQL Server Agent Maximo Trinidad Intermediate
Working with SQL Server – SQLPS Maximo Trinidad Intermediate

There’s plenty of interesting SQL Server sessions for those hungry for knowledge, and don’t forget to support the sponsors.

Please come and join us in this event.  See you all there!!