SQL table export using PowerShell

Most of the PowerShell guys may need to extract data from SQL databases. There could be two or more ways to do the same task however as per my latest findings in one of the project I figured out including BCP in our script is the best way to achieve that. In my following two examples I am using AdventureWorksDB and a SQL query that write data into a text file.

Example 1 using Invoke-SQLCmd.

$SQLQueryy= "USE AdventureworksDW2016CTP3
SELECT  ProductKey, OrderDateKey, ShipDate FROM [dbo].[FactResellerSalesXL_CCI]
WHERE ProductKey =532"
$StartTime = (Get-Date)
Invoke-Sqlcmd -ServerInstance SQL2 -Query $SQLQueryy -QueryTimeout 65535 |Format-Table -HideTableHeaders -AutoSize |Out-File 'C:\temp\via_InvokeSQL.txt'  -Width 500
$Endtime=(Get-Date)
$TotalTime = "Total Elapsed Time : $(($Endtime-$StartTime).totalseconds) seconds"
$TotalTime

UsingSQLCmd.jpg

This script takes almost 14 seconds to extract the rows from SQL.

Example 2 Using BCP

$StartTime = (Get-Date)
bcp  "USE AdventureworksDW2016CTP3 SELECT  ProductKey, OrderDateKey, ShipDate FROM [dbo].[FactResellerSalesXL_CCI] WHERE ProductKey =532"  queryout 'C:\temp\via_BCP.txt' -T -c -S SQL2
$Endtime=(Get-Date)
$TotalTime = "Total Elapsed Time : $(($Endtime-$StartTime).totalseconds) seconds"
$TotalTime

UsingBCP.jpg

This query take less than 1 seconds with neat & clean output file.

Design Windows form using PowerShell

As you all be agree, windows forms are more user friendly then command line interface. If you are a windows admin and figuring out how to write a windows form using PS code then use following link and download Form Builder PS script to easily write such code.

In my simple example, created a windows form with a ‘Button’ and a ‘Label’ however your script may have lots of different options.

FormMaker.jpg

Once you complete preparation of your windows form, then click on ‘Export’, this will develop code file for the form and windows control you have selected. Now you write down code on ‘Button’ click. I have added following line of code.

$Button_click=
 {
  $wmiOS = Get-WmiObject -Class Win32_OperatingSystem;
  $OS = $wmiOS.caption;
  $mLabel1.Text=$OS
 }

$mButton1.add_click($Button_click)

Outputfile.jpg

Entire script can be copied from Here.

    Add-Type -AssemblyName System.Windows.Forms
    Add-Type -AssemblyName System.Drawing
    $MyForm = New-Object System.Windows.Forms.Form
    $MyForm.Text="MyForm"
    $MyForm.Size = New-Object System.Drawing.Size(400,200) 

        $mButton1 = New-Object System.Windows.Forms.Button
                $mButton1.Text="GetOSVersion"
                $mButton1.Top="43"
                $mButton1.Left="7"
                $mButton1.Anchor="Left,Top"
        $mButton1.Size = New-Object System.Drawing.Size(120,23)
        $MyForm.Controls.Add($mButton1) 

        $mLabel1 = New-Object System.Windows.Forms.Label
                $mLabel1.Text=""
                $mLabel1.Top="83"
                $mLabel1.Left="17"
                $mLabel1.Anchor="Left,Top"
        $mLabel1.Size = New-Object System.Drawing.Size(400,23)
        $MyForm.Controls.Add($mLabel1) 

        $Button_click=
        {
        $wmiOS = Get-WmiObject -Class Win32_OperatingSystem;
        $OS = $wmiOS.caption;
        $mLabel1.Text=$OS
        }
        $mButton1.add_click($Button_click)

        $MyForm.ShowDialog()

If you feel the underline code should remain hidden and the end user should not see the background logic then you can convert your PS1 script to EXE using PS2EXE tool.