Post on PowerShell SQLServer SMO simplicity

Stay tuned for a series of post and learn how to start using SQL Server SMO with PowerShell V4 (or greater). Collect information from your SQL Server with PowerShell starting with a few lines of code.  See shortcuts and string formatting working in your favor while manipulating your data.  The scripts will evolved as you learn to apply new techniques.

Yes! PowerShell is about evolution of your automation skills for productivity.

How far can we take this? From this code:

($MySQL.Databases) `
| Select-object Name, Owner, RecoveryModel, Size, DataSpaceUsage, SpaceAvailable `
| Ft -AutoSize;

to an HTML solution.


At the end, it’s all about reusable code.

We glad to welcome our new sponsor Devart with their product “dbForge” which is an excellent tool next to SQL Server Management Studio.  I’ll be briefly showcasing this product.



FLPSUG February 26th meeting “Azure Edition”

Join us tomorrow in this special edition of our Florida PowerShell User Group meeting.

Our topic: “PowerShell Building Azure Quick VMs” – This is a PowerShell Azure via to quickly create Virtual Machines in it’s basic form. It will give you a quick way to get you started with Microsoft Azure.  Live Sample demo will be shown.

This is the same topic already given at the SQL PASS Virtual PowerShell meeting last Thursday February 19th.

I added a few more content and code.  All audience are welcome but mainly oriented to new Azure DevOps.

To register:



Florida PowerShell User Group – New Year 2015 Resolutions

Lets Do THIS!!  Come and join us.  It’s free essential training for a couple of hours.

  • IT Skills
    Any IT Pro/DevOps resolution for this year should be to improve your skills.  You need to start getting into PowerShell because is the the main technology for automating your On-Premise, Cloud and/or Hybrid infrastructure.  If you don’t care about it then you need to pick to a different career.
  • More Azure Sessions
    This new year I’m going to extend my PowerShell session to include more Azure presentations.  If you are a newbie you’re welcome to join us.  Or, if you’re already working with Azure PowerShell, then join us and share your experience.
  • Get to know Microsoft Cloud
    This year, get to know Microsoft Azure and all its component because they are all manageable with PowerShell.  With PowerShell you’ll need to building your knowledge of working with DSC (Desired State Configurations), OneGet, and Runbooks for Azure Task Automation.  There’s plenty of information out there and still you need to connect the dots to make it work.
  • Learn about tools
    One thing for sure is that Windows ISE editor and PowerShell version 4.0 (or greater) plays an important roll when buidling scripts.  Also, when combining the free Visual Studio Community Edition will be a most when integrating PowerShell and providing source code management with Team Foundation Online or Github. This is a MOST to learn to use!
  • Extending meetings
    Here’s where our new coming Azure PowerShell meeting will start diving into the essential of using DSC as a foundation to improve our understanding of Desired State Configuration.  At the end, of this journey we’ll end up taking a look at CHEF tool.

So, our normal monthly FLPSUG meeting (3rd Thursday) will be inviting MVP’s speakers to showcase their expertise.  Then, on our new second FLPSUG meeting (starting in February) we’ll be strictly concentrating on PowerShell Azure for Cloud Task Automation and having MVP Adnan cartwright (co-hosting) in these series.

Let us Welcome New Year 2015!!

Join Us next meeting on January 22nd 6:30PM (EST):


Getting started with Windows 10 PowerShell v5.0 Preview

As we already know with the new version of Windows 10 comes PowerShell Version 5.0 PREVIEW loaded with more enhancements and new Cmdlets.  It also come with the PowerShell ISE (Integrated Scripting Environment) which has been enhanced.  But, how can we get started?

The same procedure needed to get started in previous PowerShell versions still applies:

1. Add PowerShell on your “Start Menu” or just run it from the Windows System menu group.


2. Open either of the PowerShell Console or the shell within the ISE then set the “ExecutionPolicy“.  Preferably executing “as Administrator“:

Set-ExecutionPolicy -ExecutionPoilicy RemoteSigned


3. To start the Windows PowerShell ISE editor you will need to execute the “ISE” command from the PowerShell console.
Then when is open, you can do a right-click on the ISE task bar and do “Pin to Taskbar“.  ISE can’t be pinned to the “Start Menu” yet.


Although, without setting the ExecutionPolicy, you can still execute single command lines. But, failure to do so will cause a script file not to execute.

Next, If you don’t have the time to buy a book then take a look at the PowerShell Help “About_*” topics.   There are a additional step that update your PowerShell documentation on your machine.  Of course, Internet connection is required.

4. Open the PowerShell Console or the shell in ISE to run the following command “Update-Help”, and again preferably executing “as Administrator”:




After you have updated all your PowerShell Version 5.0 documentation try usign the following command lines to list and select any of the existing help “About_*” topics:

[Array] $s = (Get-Help About_* | Select Name) | out-gridview -PassThru;
foreach($i in $s){ Get-Help $i.Name -ShowWindow;};



Basically, these two commands will help in listing all help About_* topics and using ‘Out-Gridview’ cmdlet with the  -Passthru’ parameter you can select multiple topics to read.  Just press the Ctrl key when selecting the topics.

I’ve create a help module if you want to give it a try to assist in your PowerShell journey.  Check this link:

4. When loading PowerShell modules the following folder “C:\Users\…\Documents\WindowsPowerShell\Modules” doesn’t exist.  So you will need to go to the User document folder create this folder.

This folder is where you install all you customs and/or add any existing on PowerShell modules (including the ones for ISE).  Below is a list of suggested PowerShell Add-on modules you may want to look at:

Make sure to read the ReadMe files and verify that the module(s) can run on this version of PowerShell.

Now, at this point, you’ll be ready to start using PowerShell v5.0.

FLPSUG October 16th Meeting PowerShell Excel Deep Dive…

Thanks to everyone who attend last Thursday meeting on “Quick Deep Dive to Excel with PowerShell” .   Here’s the demo sample scripts containing the following:

1. 0_TipsTrickResourceWithExcel.ps1 – Tips, Trick, and web resource information.
2. 1_TrapSystemLogData.ps1 - Sample script to build an PSObject with both Application/System errors and warning logs use to print the Excel report.
3. 2_CreateSystemLogReport.ps1 - Procedure that will generate the Eventlog results show grouped and count of errors in Excel format.
4. 3_Function_Convert-psObjectToExcel_01.ps1 - Another variation of creating a basic formatted Excel file from a PSObject.
5. 4_SMOGetdatabaseSize.ps1 - Sample script use to build a PSObject for the previous function.

Download Demo Scripts:

PowerShell using SMO to get SQL Database Table information

Using SQL Server Management Object .NET class can assist in extract information about your SQL Server engine.  This is a sample PowerShell script code using SMO to extract database tables information and display it in the PowerShell console;

## - SMO query for Tables information within the database:
## --

## - Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## - Setting variables with Server and Database name:
$SQLInstanceName = 'WIN81ENT01'; $Global:SourcedbName = 'AdventureWorks2014';

## - Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLInstanceName

## - Uncomment code block within #region - #endregion to enable use of SQL Authentication:
#region - Changing from default Windows to SQL Authentication:

#   ## - Variables required to SQL UserID and SQLPassword:
# $SQLUserName = "YourSQLUserID"; $sqlPwd = "YourSQLUSerPassword";
#   ## - Block of Code required to build SQL Authentication:
# $MySQL.ConnectionContext.LoginSecure = $false;
# $MySQL.ConnectionContext.set_Login($SQLUserName);
# $SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
# $MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);


## - Create PSObject with all database table information:
$dbtables = $MySQL.Databases[$Global:SourcedbName].tables;

## - End of Script

Notice that in the code for connecting to SQL Server using SQL Authentication is included.  So, to enabled it, just uncomment the block of code, then provide the SQL UserName and Password.

After you have created the psObject with the database table information, we can proceed to display it on console using the Out-Gridview cmdlet.

## 1 - Display Table Information on a GridView:
$dbtables | Select-Object `
@{ Label = 'SQLServerName'; Expression = { ($SQLInstanceName) }; }, `
@{ Label = 'DatabaseName'; Expression = { ($_.Parent) }; }, `
@{ Label = 'TableName'; Expression = { ($_.Name) }; },
@{ Label = 'DataSpaceUsed(KB)'; Expression = { ($_.dataspaceused) }; }, `
@{ Label = 'IndexSpaceUsed(KB)'; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
Label = "LastTableUpdate"; Expression = { `
$sqlupdtbl = @"
--object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID('$($Global:SourcedbName)')
and Object_ID = Object_id('$($_.Name)')
"@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update;
}} | Out-GridView `
-Title "Display Database: $SourcedbName Tables Information";

Output to a gridview

Also, it’s possible to export the psobject information to a *.csv file format for later viewing using the Export-csv cmdlet.

## 2 - Create csv file:
$dbtables | Select-Object `
@{ Label = 'SQLServerName'; Expression = { ($SQLInstanceName) }; }, `
@{ Label = 'DatabaseName'; Expression = { ($_.Parent) }; }, `
@{ Label = 'TableName'; Expression = { ($_.Name) }; },
@{ Label = 'DataSpaceUsed(KB)'; Expression = { ($_.dataspaceused) }; }, `
@{ Label = 'IndexSpaceUsed(KB)'; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{ Label = "LastTableUpdate"; Expression = { `
$sqlupdtbl = @"
--object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID('$($Global:SourcedbName)')
and Object_ID = Object_id('$($_.Name)')
"@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update; }} `
| Export-Csv -NoClobber -NoTypeInformation `
-Path "C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv";

## 3 - Open file:
Invoke-Item "C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv";

Export to *.CSV file

Keep in mind, in order to use load and use the “Microsoft.SQLServer.Smo” assembly, you must had already install at least the SQL Server Management Studio or only using it’s individual components (

For more information about SQL Server SMO check my article at:



Head start to PowerShell 5.0 Experimental Preview

Yes! Windows Management Framework 5.0 Preview Experimental – July 2014 has been available and full of goodies for IT Pro(s) and DevOp(s). I’m hoping you all realized PowerShell is your number ONE tool for automation productivity in your Infrastructure environment(s). Please, take the time to learn it and don’t be left behind.

You all heard Microsoft initiative to have everyone start migrating from Windows Server 2003 to Windows Server 2012 R2 for either your ‘On Premise’ and/or ‘MS Cloud’ environment(s). Definitely, PowerShell can provide the help in completing these tasks.

In order to use **PowerShell Version 5.0 Experimental, you are required to use Windows Server 2012 R2 and/or Windows 8.1. Also, prior to installing the preview, you will need to install a hotfix. This is all documented in the download link. This why is important to read the download instruction before proceeding with the installing the preview.

This new preview provide you with lots of enhancements:

  1. DCS (Desired State Configuration) – Bugs fixes and new modules.
  2. OneGet – simplify how you discover and install software packages.
  3. PowerShellGet – new way to discover, install, and update PowerShell Modules.
  4. Network Switched cmdlets.
  5. PowerShell ISE – DCS authoring improvements
  6. Language enhancements – Develop classes. (You need to check this one out!)

**NOTE:  This is a Preview and it doesn’t mean that all you see will be included in the RTM version. 

Read the Preview Documentation for more information.


Important Resources

Below are the necessary resource links to get you started with the latest materials about PowerShell Version 5.0: (Good reading material)

  1. PowerShell DSC Resource Kit Wave 5 Arrives:
  2. TechNet Gallery – resources for IT professionals – Gallery listing of DCS:
  3. DSC Resource Kit (All Modules):
  4. Holiday Gift – Desired State Configuration (DSC) Resource Kit Wave-1:
  5. OneGet (v5.0 April Preview):
  6. Getting Started with DSC:
  7. PowerShellGet (v5.0 May Preview):
  8. Recent PowerShell Version 5.0 and DCS articles at the PowerShell Team Blog site: \
  9. Also, recent DCS Articles at site:
  10. Check out the Microsoft Virtual Academy:


Getting to know the MS Cloud technology

Yes! Start learning about MS Cloud, better known as Microsoft Azure. If you got a MSDN subscription then you got to use your free Azure credit which ranges between $150 to $200. There are no excuses. Just try it!

Now, you have the ability to build Windows Server(s) and/or Windows 8.1 desktop. YES!! A virtual machine Windows 8.1 Client. So, you won’t stay behind and keep up with today technologies.

Check out the Microsoft Cloud OS blog site:

PowerShell is mention All Over the Cloud!!


PowerShell Extracting SQL Server Data into Excel

I recently helped someone with providing a solution using PowerShell to extract data from SQL Server into an Excel file. We all know that  we could use SSIS to provide the means to do this but there are some situations you may want to use scripting instead. So, found one script I did back in 2009 that will do such a thing. The funny thing is, when I looked at it, I realized that for the Excel part I had a  lot of unnecessary extra code and it could be improved greatly. So, here’s the updated version.

This *script will do the following steps:
1. Connect to SQL Server and get the SQL Server data.
2. Build the Excel file with columns heading and data.
3. Save the Excel file and Close/Terminate Excel process.

*Note: This script is PowerShell Version 2.0 compatible.

Getting you SQL Data

This script uses the ‘System.Data.SqlClient.SqlConnection’ which you can run on any machines without SQL Server installed. In this example the connection string is a trusted ‘Windows Authentication’. Then, I’m using the Here-String @”..”@ to insert the T-SQL script I want to execute against SQL Server. Keep in mind, this connection string can be change to use SQL Server Authentication.

## ---------- Working with SQL Server ---------- ##

## - Get SQL Server Table data:
$SQLServer = 'SQLServer01\MSSQLInstance01';
$Database = 'Database1';
$SqlQuery = @'
Select top 10
from Database1.dbo.Table1
where [Field4] = 'X001'

## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = `
"Server = $SQLServer; Database = $Database; Integrated Security = True";

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;

## - Extract and build the SQL data object '$DataSetTable':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$DataSetTable = $DataSet.Tables["Table"];

Buidling the Excel file

This block of code will generate the Excel file consuming the SQL data object ‘$DataSetTable’. The result of the SQL DataSet will be use to automatically create the columns heading and data rows. This is the heart of the script where the magic happen.

## ---------- Working with Excel ---------- ##

## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

## - Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;

## - Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;

foreach ($rec in $DataSetTable.Rows)
foreach ($Coln in $getColumnNames)
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";

## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = `
$rowData++; $ColData = 1;

## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;

Saving and Terminating Excel

Now that I’ve build the Excel sheet, I need to save the file, quit and terminate Excel. And, Yes! It’s needed to terminate/kill the Excel process because this process will remain active even if when exiting/closing the PowerShell session.

## ---------- Saving file and Terminating Excel Application ---------- ##

## - Saving Excel file - if the file exist do delete then save
$xlsFile = `

if (Test-Path $xlsFile)
Remove-Item $xlsFile

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

## - End of Script - ##

SQL Data to Excel file
SQL Data to Excel file

Additional Note

One thing to understand, this process will work with small datasets. So, the more data you extract the slower it may take to build the Excel file. This is why is important to test the script(s) and look at other best possible solution. Maybe it’s better to use SSIS (SQL
Server Integration Services) but it doesn’t hurt try other technologies.

More Discover PowerShell – How about Help with PowerShell Variables?

Finally got this last function working in order and created a new module: “DiscoverPowershell” with all three Show-Help* functions:

1. Show-HelpPowerShellCommand – Meant to select one module at the time and then multiple commands.
2. Show-HelpAboutPowerShellTopic - Multi-select can be applied.
3. Show-HelpPowerShellObject (New) – Multi-select can be applied.
Check out the first two functions on my previous blog.

In the module I tweak is just a little bit but the functionality stay the same. Basically, you can select multiple Item(s) in the Out-Gridview and display the results.

Here’s the link to download and install the module folder “DiscoverPowerShell“:!30947&authkey=!AKkr99vUvdqDKCw&

*Note: Module requirements: PowerShell V4 (or greater) on Windows 7, Windows 8.1 and Windows 2012 R2.

Here’s the third function: Show-HelpPowerShellObject

function Show-HelpPowerShellObject
Function to list all PowerShell Variable objects in your current session.

This function will display in the 'Out-Gridview' a list of all PowerShell Variable objects in your
session. Press the Crtl-Key and select the PowerShell variable you want to Display information.

.PARAMETER No Parameter(s) required.


Param ()

[Array] $selItem = $null; [Array] $myObj = $null;
While ($selItem -eq $null)
$selItem = Get-Variable `
| Select name, @{ Label = 'objectType'; Expression = { $_.GetType(); } }, value `
| Sort-Object Name | Out-GridView -PassThru -Title "Select the PSVariable Object";
If ($selItem -eq $null) { break };
[Array] $myObj = $null;
ForEach ($myObj in $selItem.Name)
((get-variable $myObj).Value) | get-member `
| Out-GridView -Title ('Displaying Selected PSObject - $' + "$myObj");
If ($myObj -eq $null) { break };
$selItem = $null;
Copy/Paste code
Copy/Paste code
Multi-select items
Multi-select items
Selected items displayed and back to list
Selected items displayed and back to list

It’s all about having fun with PowerShell!!