Florida First ITCamp 2011 is starting in Tampa on Saturday March 19th…

To all IT Pro’s!!

I just heard from our Southeast Microsoft IT Evangelist Blain Barton that we have a date set for our first ITCamp 2011 in Tampa. It’s going to be on Saturday March 19th at KForce in Tampa. We are planning to have the following sessions:
1. Exchange
2. Sharepoint
3. SQLServer
4. Office
5. PowerShell
6. Server and Desktop Deployments

This is just beginning and I will be helping Blain in organizing this event and speakers sessions.

With that said!!!  We’ll be calling for speakers sometime next month.

Stay tuned for more information soon to come.


Orlando SQLSaturday 49, SQL PowerShell Track was a success!

Special Thanks to all SQLSaturday organizers to allow us to prepare this track, and to all sponsors for the giveaways.  Here’s a picture of all SQL PowerShell speaker for that day:


From left to right: Ron Dameron, Aaron Nelson, Max Trinidad, Chad Miller, and visiting from Microsoft Mr. Ed Wilson (Scripting Guy).

If you want to download my presentation and sample, please click on the following link:

Before opening the file, make sure to rename the file to a *.zip file type.

For other SQL PowerShell presentation downloads, click on this link: http://www.sqlsaturday.com/eventhome.aspx

Thanks again to all attendees that participated in our sessions.

Tip in cleaning *SVN folders in PowerShell…

I would say that cleaning these left over *.svn folders can be a pain if you have then in lots of subfolders.  When I realized using PowerShell, it can be very easy to cleanup.  As you know, these folders are hidden and will be propagated to other folders.  So, here’s a one liner that can get rid of these folders:

dir . -include *.svn -Recurse -force | Del -Recurse -force

Simple enough!  The first half of the line is a “Dir”, the alias for “Get-ChildItem”, which will include all the *.svn objects repeatedly under the current folder and forcing to look for hidden ones.

dir . -include *.svn -Recurse –force

Now, the other half, when follow by the “|” pipe symbol, this will pass all the objects collected from the first command and pass it to the next command.  In this case “Del”, the alias for “Remove-Item”, will get rid of all the objects found.  You must have both parameters: “ –Recurse” and “ –Force” in order to work.

Del -Recurse -force

Put it together and there’s no registry hacking, or building a batch file. 

Just plain and simple PowerShell!

SQLServer and PowerShell a perfect companion!

Finally, after three years, I’ve seen a lot more DBA’s blogging about their solutions combining T-SQL and PowerShell.  KUDOS To Everyone!!!  One thing PowerShell has bring to the table is the ease of getting things done.  Still,  there’s a learning curve everyone has to overcome.  The truth is, the more you use it, the harder is to let go.  So, here’s some things that might help the DBA’s:

1. Be careful with PowerShell ISE.  It does have a bug that you won’t notice until you may try to do a copy/paste of your code into a different editor or into your PowerShell Console.  And, this it due to the editor saving  as a “UNICODE” and not ASCII.  You will seen your lines of code in one single line when viewing the script on a different editor, such as notepad .  Again, just be careful.


By the way, this bug has been document in Microsoft Connect and there’s a few blogs about a work around: http://www.nivot.org/2010/05/21/PowerShellISEHackingChangeDefaultSaveEncodingToASCII.aspx  http://dougfinke.com/blog/index.php/2009/07/20/how-to-work-around-a-powershell-integrated-scripting-environment-bug/

2. Do you know? If you don’t want to install SQL Engine but want to take advantage to use PowerShell and manage SQL Server, then just install “SQL Server Management Studio 2008 R2” and you can use SQLPS.   Yes! Why adding unnecessary processes to consume your system resources.   Here’s the link: http://www.microsoft.com/downloads/en/details.aspx?familyid=56AD557C-03E6-4369-9C1D-E81B33D8026B&displaylang=en

3. SQL Server 2008 R2 may not have lots of commands.  But, try to use Invoke-SQLCmd,  this is a very powerful cmdlet specially when you learn to use it to extract data using T-SQL and Stored-Procedures that generate results.  Keep in mind, you may export the results to different file format.    Check out this example:

## Make sure SQLPS is loaded in your session$s = Invoke-SQLCmd -database master -Query ‘exec sp_who’

$s | Select spid, status, loginame, cmd, blk | out-gridview


This is a good sample of collecting SQL Servers information into a .NET variable and displaying the results using “Out-GridView”.  Also, you could have use “Out-file” command and save it in your drive.  PowerShell give you lots of options to handle information.  Think out side of the box!

4. Don’t be afraid to learning new technologies, specially .NET Framework.  You are probably getting into it if you’re working building SSIS packages.  PowerShell syntax looks very similar to C# and you can extend scripting with .NET Framework.   A good example would be if you want to add to send an email from your script.   The issue is, you can’t run any of the new PowerShell V2 commands under SQLPS and/or SQL Agent.  You will need to use straight .NET code to by pass this limitation.  SQLPS is PowerShell V1 mini-shell (more info:http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!842.entry ).  


## – This is the .NET Framework way to create a send email step and can be
##use for both PowerShell V1/V2
## Send email with monitoring results
$mailmess = new-object system.net.mail.mailmessage
$mailmess.From = “FromUser@Company.com”
$mailmess.Subject = “Type Any Subject here…”
$mailmess.Body = “Type Any Detail here…”;
$mailclient = New-Object System.Net.Mail.SmtpClient
$mailclient.host = “Your.SMTP.PostOffice”

## – This is the PowerShell V2 cmdlet to send email. ( will not run under SQLPS.exe
Send-MailMessage -from “FromUser@Company.com” -to “ToUser@Company.com”  `
    -Cc “CCtoUser@Company.com” `
    -subject “Type Any Subject here…” `
    -Body “Type Any Detail here…” `
    -SmtpServer ‘Your.SMTP.PostOffice’
## Now, If you are using SQLPS.exe (only) then you need to use the .NET Framework.?
## This applies when running the SQLPS.exe console and/or writting the code in the
## SQL Server Agent PowerShell jobstep.

## You can only run PowerShell V2 scripts in the SQL Server Agent if you include to
## execute PowerShell.exe with the script file.  This way the script is running
## under PowerShell v2 process.
##  sample for Job Step:
##  PowerShell -nologo ‘C:\Developer\PSscripts\PSSQLMonitoring_Summary.ps1’

There’s more but I think I will leave some for later.  For those in the Florida area, I will seen you soon at the next SQLSaturday #49 in Orlando and at the SW Florida .NET Developers Group in Fort Myers.

Happy PowerShelling!!

Some SQL Server and PowerShell News – 10/07/2010 …

Powershell Virtual Chapter

Yes, join my college and Tampa friend Chad Miller, on his inaugural event on October 20th.  Check for details at:


Worldwide Online TechDay 2010

Also, don’t forget to signed up to the first ever “Worldwide Online TechDay 2010” –  Saturday, October 30.  This event was put together by my MVP friend Laerte Junior and his colleges..  It’s originated in Brazil, having me, Chad Miller and a few others SQL Server PowerShell enthusiast will be giving live online presentations.  For more information, here’s the link:


About SQL Server SP2

I’ve been on top of the recent release of SQL Server 2008 SP2 just because Microsoft send a TechNet Newsletter announcing that SQL Server 2008 R2 ‘SQL Server 2008 SP2’ was released.  Well, as you know by now, they have corrected the mistake.  Again, SQL Server 2008 SP2 is ONLY for SQL Server 2008 and NOT R2.


More upcoming Florida Events:

1. SQLSaturday #49 on Saturday October 16th.  We are having an all day SQL PowerShell track full of good presentations all the long.  I will be giving a new session “Working with SQL Server – SQLPS”, first session in the morning starting at 9:00am.  Come on over and remember, it’s Free: http://www.sqlsaturday.com/49/eventhome.aspx

2. I will be repeating my SW Florida .NET CodeCamp 2010 “Working with PowerShell” Session at the SW Florida .NET Developers Group meeting on Tuesday, October 26th 6:30PM.   Thanks to John Dunagan for inviting me over again.   Sign up at: http://swfldev.net/

If you leave anywhere in these Florida areas, I hope to see you soon.

Happy PowerShelling!!

Basic SQL Server Stored-Procedure scripting with PowerShell…

Here’s a quick way you can use PowerShell to script a Stored-Procedure with a few one-liners.  This is in its basic form and you can use this code to build your own functions.  I will be including both an SMO and a SQLPS versions.  This is how it’s done:

Here’s the SMO version: (copy/Paste code)

###  – This is the SMO Version  –  ###
## Load the SMO Assembly V1 version (works in V2)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null

## Connect to the SQL Server and get the Stored-procedures
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) “YourServerName”
$List_storedProc = $MySQL.Databases[“Master”].StoredProcedures

## List the first 10 procedures
$List_storedProc | Select -first 10 schema, name | ft -auto

## Display selected script
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script()

## Save the script to a file
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql


In the above section, you have all the necessary SMO code to create a SQL script of a Stored-Procedure.  Just change the database name and you will be able to list all the SQL Stored-Procedures for that database. 

Now, here’s the SQLPS version: (copy/Paste code)

###  – This is the SQLPS Version  –  ###
## – Change directory to the Stored-procedures folder
cd SQLSERVER:\SQL\MAX-PCWIN1\DEFAULT\Databases\master\StoredProcedures

## – Directory list the first 10 Stored-Procedures using the “-force” parameter
dir -force | select -first 10

## – Stored the result in a variable
$s = dir -force

## Display selected script
($s |  ? {$_.Name -eq “Sp_Configure”}).script()

## – save the scripted item to a files
($s |  ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql


Using SQLPS.exe or loading the SQLServer provider in your profile to gain access to your SQL engine components, it gives you a slightly edge because all the needed SMO assemblies gets loaded for you.  Even if you install SQL Server 2008, PowerShell itself will not load these SMO Assemblies, it has to be part of your PowerShell profile, a separate script, or in a module.

In SQLPS, or from the PS Console (or ISE prompt) with the SQLPS module loaded, you can use the change directory “CD” path provided in the sample and change the database name with the instance ( ..\Max-PCwin1\Default\..) to yours ( ..\YourServerName\Default_or_YourInstanceName\.. ).  That’s it!

cd SQLSERVER:\SQL\SrvName\DEFAULT_or_InstName\Databases\master\StoredProcedures

Go ahead and try it!   You will appreciate PowerShell a lot.