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
[Field1]
,[Field2]
,[Field3]
,[Field4]
,[Field5]
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;
$SqlAdapter.Fill($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;
$RowHeader++;
};

## - 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) = `
$rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};

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

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 = `
"C:\Temp\NewExceldbResults_$((Get-Date).ToString("yyyyMMdd_hhmmss")).xls";

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($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“: https://onedrive.live.com/redir?resid=7FD7082276C66197!30947&authkey=!AKkr99vUvdqDKCw&ithint=file%2c.zip

*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
{
<#
.SYNOPSIS
Function to list all PowerShell Variable objects in your current session.

.DESCRIPTION
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.

.EXAMPLE
Show-HelpPowerShellObject
#>

[CmdletBinding()]
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!!

FLPSUG Last meeting May 15th on “PowerShell Azure SQL Database”

FLPSUG Lync meeting Thursday May 15 2014 – Session: “PowerShell Working w/Microsoft Azure SQL Database” Speaker Maximo Trinidad (MVP) and Co-hosting Adnan Cartwright (MVP).

In this presentation I will be covering how to subscribe to Azure, setup PowerShell to connect to your subscription, use scripting to create a SQL Database Server and then use SMO with PowerShell push data to your SQL Azure tables. All this using PowerShell scripting plus showing some editor, scripting techniques, and tips to avoid issues when working and setting Azure with PowerShell. (live demo with Windows Azure). Thanks to Adnan for assisting me in this meeting.

Here’s the Powerpoint presentation, demo scripts, and link to the recorded video. The video is the full and unedited meeting: https://onedrive.live.com/redir?resid=7FD7082276C66197!31206&authkey=!AIbxWUhyiUth7Dg&ithint=file%2c.zip

Video link: http://youtu.be/9-fMxXY7DcQ

Co-host Adnan Cartwright link: http://www.fisg.us/

 

Get a hold of PowerShell (WMF) v5.0 Preview

Windows Management Framework 5.0 May 2014

Again, this version is only available for Windows 8.1 and Windows Server 2012 R2.

On PowerShell  v5.0 Preview just released on May 14th.  This version has no problem with the Azure PowerShell cmdlets installation.  Check out what’s hot in version 5.0 Prewview:  DSC (Desired State Configuration), OneGet and PowerShellGet modules.

Blog: http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx

Download at: http://www.microsoft.com/en-us/download/details.aspx?id=42936

PowerShellV5May2014

Also, Check out the new PowerShell “Script Browser & Script Analyzer 1.2” for ISE and Windows:
http://www.microsoft.com/en-us/download/details.aspx?id=42525

This new ISE Add-On will create two shortcuts: one to open the Script Browser in ISE and the other as an individual Windows application.

Script Browser Icons

Script Browser Icons

Script Browser Windows Application

Script Browser Windows Application

Script Browser in ISE

Script Browser in ISE

Go and get them!

Quick Rundown – Microsoft Azure SQL Database Server and PowerShell

Azure SQL Database 
1. Web and Business editions are no longer available. Now there’s Basic, Standard, and Enterprise editions. (New)
2. There is a limit of 6 SQL Database Servers and up to 150 databases per subscription.
3. Create database from 1 GB up to 500GB of storage.
4. Database Throughput Unit(DTU) Service performance levels available: (New)

  • DTU Service Level:
    1  – Basic
    5 - S1
    25 - S2
    100 - P1
    200 - P2
    800 - P3

AzureSQLdb_DTU

For more information about Azure SQL Database Throughout Units Service, check out Scott Kline and Tobias Ternstrom on this link: http://channel9.msdn.com/Series/Windows-Azure-Storage-SQL-Database-Tutorials/Scott-Klein-Video-02

Quick tips when testing Azure SQL Database Server:
1. There’s no need to specify a storageaccount.
2. Start with “Basic” or “Standard” Service level.
3. Start with  1GB in size for testing.
4. Current Azure Portal version will create random database server names.

Observations on the “Preview” Azure Portal on Azure SQL Datbases:
1. It show the ability to group databases.
2. You can provide a SQL Database Server name (not random).

Upcoming Azure Portal (Preview)

Assign a Group and Name your Database Server

Assign a Group and Name your Database Server

Windows Management Framework 5.0 May 2014

On PowerShell  v5.0 Preview just released on May 14th.  This version has no problem with the Azure PowerShell cmdlets installation.

Blog: http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx

Download at: http://www.microsoft.com/en-us/download/details.aspx?id=42936

Azure PowerShell cmdlets updated

Azure PowerShell cmdlet was updated to version 0.8.2 on 05/12/2014 with 390 commands.AzurePosh_082_05152014

 

 

It’s all about discovering and exploring with PowerShell

Nothing like discovering and exploring what’s already at your fingertip. All you need to get started with PowerShell is already loaded in your system. There are plenty help documentation to keep you busy for awhile.

It’s time to get serious with PowerShell.

Checkout this year TechEd PowerShell session with Don Jones and Jeffrey Snover:

Now, using two known commands: Out-Gridview and Get-help we can search for help information in a flash.  Here I’m providing two custom PowerShell functions that might help you in exploring and learning about PowerShell:

1. Show-HelpPowerShellCommand - Select the module and then which command(s) to display thedocumentation.
2. Show-HelpAboutPowerShellTopic - Select the topic(s) to display the documentation.

Just copy/paste the code into a script file then load them in either PowerShell console or ISE.  To execute the command just type the function name: Show-HelpAboutPowerShellTopic or Show-HelpPowerShellCommand.

function Show-HelpAboutPowerShellTopic
{
<# .SYNOPSIS Function to list all PowerShell About_* topic from the selected list. .DESCRIPTION This function will display in the 'Out-Gridview' a list of all PowerShell About_* topics installed in the system. Then, you can select one or multiples topic(s) available. Press the Crtl-Key and select the command(s) you want to get the help documentation. .PARAMETER No Parameter(s) required. .EXAMPLE Show-HelpAboutPowerShellTopics #>

[CmdletBinding()]
Param ()

[Array] $Global:selAbout = $null;
While ($Global:selAbout -eq $null)
{
[Array] $Global:selAbout = $null;
$Global:selAbout = ((Get-Help About_* | Select-Object Name) `
| Out-GridView -PassThru -Title "Listing all PowerShell About_* topics");

if ($Global:selAbout -eq $null) { break };
foreach ($item in $Global:selAbout) { Get-Help $item.Name -ShowWindow };
$Global:selAbout = $null;
};
};

Check the image samples of Show-HelpAboutPowerShellTopic:

Loading Show-HelpAboutPowerShellTopics

Loading Show-HelpAboutPowerShellTopics

Selecting multiple topics

Selecting multiple topics

Viewing results and back to topics listing

Viewing results and back to topics listing

function Show-HelpPowerShellCommand
{
<# .SYNOPSIS Function to list all cmdlets from the selected module(s). .DESCRIPTION This function will display in the 'Out-Gridview' a list of all PowerShell modules installed in the system. Then, you can select one of the modules to list all commands available in another 'Out-Gridview' window. Press the Crtl-Key and select the command(s) you want to get the help documentation. .PARAMETER No Parameter(s) required. .EXAMPLE Show-HelpPowerShellCommand #>

	[CmdletBinding()]
	Param ()
	
	[Array] $selItem = $null; [Array] $selCmdlet = $null;
	While ($selItem -eq $null)
	{
		$selItem = get-module -ListAvailable | Select-Object -Unique name, version, path `
		| Sort-Object Name | Out-GridView -PassThru;
		If ($selItem -eq $null) { break };
		[Array] $selCmdlet = $null;
		$selCmdlet = (Get-Command -Module $selItem.Name) | Sort-Object Name `
		| Out-GridView -PassThru -Title "Module: $($selItem.Name) Total cmdlets: $(((Get-Command -Module $selItem.Name) | Sort-Object Name).count)";
		$selItem = $null;
		ForEach ($cmd in $selCmdlet) { Get-Help $cmd.Name -ShowWindow; };
		If ($selCmdlet -eq $null) { break };
	}
};
Loading Show-HelpPowerShellCommand

Loading Show-HelpPowerShellComman

Select one module first

Select one module first

Select cmdlet(s)

Select cmdlet(s)

View help and exit list

View help and exit list

Please notice that these functions will stay active until you click on ‘Cancel’ in either of the select Topics or Module list.

I’m hoping this will make it fun to use.

Latest Azure PowerShell version 0.8.0 has a preview module included

In my previous blog I mention the latest version has two module.  Well, I was wrong!  It’s important that you take the time to read the documentation and pay attention to what’s trending in the social network (such as twitter).  I did notice someone tweet about the new Azure module included in this last released: the “AzureResourceManager“.  And here’s where the fun begin.

There are three modules:

  1. Azure
  2. AzureProfile
  3. AzureResourceManager (Preview)

When executing the “Get-Module -ListAvailable” command you will notice that only 2 will show up: Azure and AzureProfile.

Azure_GetModule_02

This new module is a “PREVIEW“, and the documentation states “The Azure and Azure Resource Management modules are not designed to be used in the same Windows PowerShell session. To make it easy to switch between them, we have added a new cmdlet, Switch-AzureMode.”  Here’s the link to the documentation: http://msdn.microsoft.com/en-us/library/jj554330.aspx

This means that in order to use the new commands from the “AzureResourceManager” you need to run the “Switch-AzureMode” which will prevent you from using the Azure commands such as Get-AzureVM on the same PowerShell session.  Now, keep in mind that you can always open another session to keep working with the Azure module commands.

PowerShell with module Autoload On

PowerShell with module Autoload On

At the same time, If you need to use the command “Import-Module Azure“, you’ll notice that it will give an error telling that it can’t find the module. The trick here is, if you haven’t turned off the PowerShell Module Autoload option, the commands will be available.  Here’s a TechNet link on how to Turn-Off the PowerShell Autoload module (not recommended): http://blogs.technet.com/b/heyscriptingguy/archive/2013/02/20/powertip-turn-off-powershell-module-autoload.aspx

Import-Module Azure Error

Import-Module Azure Error

This is a bug that have been recently reported to the Microsoft Azure PowerShell team.  By default, PowerShell have the Module Autoload “ON” and you will be able to list all the Azure module commands.

PowerShell Autoload Azure commands

PowerShell Autoload Azure commands

So, you can still work with your Azure PowerShell commands and use the “Switch-AzureMode” on another session.

Use the "Switch-AzureMode" to preview the new AzureResourceManager cmdlets

Now you can continue to work with PowerShell Azure command and check out is new (Preview) module AzureResourceManager.

List of the Preview Module AzureResourceManager Cmdlets:Azure_ListPreview_06

Get-AzureLocation
Get-AzureResource
Get-AzureResourceGroup
Get-AzureResourceGroupDeployment
Get-AzureResourceGroupGalleryTemplate
Get-AzureResourceGroupLog
New-AzureResource
New-AzureResourceGroup
New-AzureResourceGroupDeployment
Remove-AzureResource
Remove-AzureResourceGroup
Save-AzureResourceGroupGalleryTemplate
Set-AzureResource
Stop-AzureResourceGroupDeployment
Test-AzureResourceGroupTemplate

Happy PowerShelling!

Problem installing the latest Azure PowerShell Cmdlets with WMF v5.0 Preview

If you’re experiencing problems installing the latest Azure PowerShell cmdlets (0.8.0 – 04/03/2014)  after installing Windows Management Framework v5.0 Preview (WMF) then don’t worry.

You can go back the uninstall the WMF v5.0 Preview, then proceed to download/install the recent *Azure PowerShell cmdlets, and go back to re-install the WMF v5.0 Preview.  It will back be working like nothing happened.

Azure_WMF5_01

Looks like there are two Azure modules in this update:

  1. Azure
  2. AzureProfile

*Note: I’m this steps may need to be repeated until WMF v5.0 becomes RTM.

Azure_WMF5_02

:)

Find your Windows Azure PowerShell cmdlets version.

I don’t know if you notice when you go to download the latest version of Windows Azure PowerShell cmdlets, the version number is no longer displayed  on the Download page.  As you already know, Azure PowerShell commands gets updated sporadically, so you need to periodically check if there are new updates.  The download page was previously giving you the hint that there was an updated set of commands.

Last year Windows Azure Download page used to display the Azure PowerShell version which made it easy to check for the updated product:

Last year Windows Azure PowerShell version displayed

Last year Windows Azure PowerShell version displayed

Currently, as of February 2014, the version number is Gone!

Windows Azure PowerShell version is Gone!

Windows Azure PowerShell version is Gone!

Well,  here’s where PowerShell comes to the rescue.

The following quick code can query both you current version and check for the latest version available on the Internet. And, Yes! This code can be improved and taken a little further.  But, this will give you a head start.

Get-WindowsAzurePowerShellVersion function:WindowsAzurePowerShell_00

Copy/Paste code in PowerShell Console.

WindowsAzurePowerShell_01

View Results.

WindowsAzurePowerShell_02

Notice the first portion of the code uses WMIWin32_Product” Class which will take a few minutes to query your system for what’s installed.  By a strong recommendation from my MVP college Aleksandar Nikolic I change the code from using the WMI Win32_Product class and instead use the Get-Module Version property. This way it will effectively provide the needed information.

Then, for the “Microsoft.Web.PlatformInstaller” piece, if you previously installed Windows Azure PowerShell (or any of the other options), this assembly will be already in your system.

Note: My college Aleksandar Nikolic suggested not to use Win32_Product. BAD IDEA!!  Check the following links why not to use “Win32_Product“:

So, I corrected the code below following Alek suggestion.

If you want to further learn to further automate your Azure PowerShell download and installation, I recommend you to read the following article at the “PowerShell Magazine“: http://www.powershellmagazine.com/2014/02/27/using-powershell-and-web-platform-installer-to-install-azure-powershell-cmdlets/

Keep learning PowerShell!

Script Code:

function Get-WindowsAzurePowerShellVersion
{
[CmdletBinding()]
Param ()

## - Section to query local system for Windows Azure PowerShell version already installed:
Write-Host "`r`nWindows Azure PowerShell Installed version: " -ForegroundColor 'Yellow';
(Get-Module -ListAvailable | Where-Object{ $_.Name -eq 'Azure' }) `
| Select Version, Name, Author | Format-List;

## - Section to query web Platform installer for the latest available Windows Azure PowerShell version:
Write-Host "Windows Azure PowerShell available download version: " -ForegroundColor 'Green';
[reflection.assembly]::LoadWithPartialName("Microsoft.Web.PlatformInstaller") | Out-Null;
$ProductManager = New-Object Microsoft.Web.PlatformInstaller.ProductManager;
$ProductManager.Load(); $ProductManager.Products `
| Where-object{
($_.Title -match "Windows Azure PowerShell") `
-and ($_.Author -eq 'Microsoft Corporation')
} `
| Select-Object Version, Title, Published, Author | Format-List;
};