Tag Archives: PowerShell

Zero to Hero with PowerShell and SQL Server–Precon at DevDataDay

{dev = data} DAY Birmingham 2016Are you looking to learn about PowerShell? Do you use SQL Server? Then boy have I got a bargain for you!

I’m doing a precon for our upcoming devdataday event. The precon will take place on Friday, August 19th, the day before devdataday.

This is designed to be a course for the PowerShell novice. No prior knowledge required! I’ll start the day with an introduction to PowerShell, basic usage and cmdlets. Next we’ll dive into programming scripts in PowerShell, starting with the basics of loops, conditional logic, and functions. We’ll move into advanced topics, including creating your own reusable modules and testing them.
The real meat of the day comes next, learning how to interact with SQL Server from PowerShell. Both the SQL Provider and SMO (SQL Management Objects) DLL library will be covered in full. Most importantly you’ll learn how to read the online SMO library documentation and how to convert the examples into PowerShell syntax. The day will culminate by building a reusable module for working with many SQL Server common tasks, such as T-SQL code generation, or performing health checks on your servers.
The day will wrap up with a look at Pester, the new open source PowerShell testing tool. You’ll see how to test all the code you generated during the day. Don’t be left behind, learn how to leverage the power of PowerShell in your SQL Server environment.

To register, just go to https://devdatadaypowershellprecon.eventbrite.com/

Updating and Sorting the Microsoft Word QuickStyle Gallery with PowerShell

 

Introduction

I’m currently working on my fifth book, this one is for the folks at PACKT Publishing. It’s still in the early stages so I can’t say too much, but if you like SQL Server Reporting Services, you’ll enjoy this book.

All book publishers use their own set of styles in Microsoft Word to allow them to control the layout of the book when it goes to print. There will be special styles for italics, bold, code listings, and more. The techniques here though are applicable to more than just book or magazine publishers.

You’re company may have a limited set of styles it approves for use in internal documents. Perhaps you need to adjust your set of QuickStyles based on what type of document you are authoring. One set for company memos, another for technical documentation, and yet another set of styles for love letters.

Using the PowerShell script in this post can make it easy for you to setup your QuickStyle gallery based on the type of document you are creating.

When working on the book it’s been convenient to clear out the list of built in styles in the QuickStyle gallery, and replace them with the ones I need for my publisher. Doing it manually though is a time consuming process.

Unfortunately I experienced further oddities. Every time I got a chapter back from my editor, my curated list of styles had been replaced. I don’t think it’s anything my editor did deliberately, she’s a really nice lady. I believe it’s some quirk of Word, related to the fact we appear to be on different versions. I knew I couldn’t keep sucking up time adding, removing, and sorting these each time I got a chapter back. I needed a way to automate.

I did some looking online, but couldn’t find a total solution that fit my needs. I found some VBA code for adding and removing styles from the QuickStyle gallery, but nothing on how to sort items in the gallery.

Being the geek I am means I love PowerShell. So I rolled up my sleeves and dove into the Microsoft Word object model. Specifically, I focused in on the Style object.

I’m a firm believer in the adage that you should completely understand any script you cut and paste off the web. So while I’ll place the complete script at the bottom of this post for easy copy / paste, I’ll step through it first to explain the various components.

Before I proceed, one important note: updating the QuickStyle gallery using the code below only updates one specific document. Other existing Word documents, or new documents you create, won’t be affected by this script.

1. Set a variable to hold the file name to update.

$wordFile = 'C:\Book\Chapter02.docx'

I start by setting a variable to hold the file name of the Word document I want to update. At some point I’ll probably turn this into a module and make this a parameter to the main routine, but for today this gets the job done and is easy for people who also want to use the script.

2. Back up the file.

$bakfile = $wordFile + '.bak'
Copy-Item -Path $wordFile -Destination $bakfile -Force

 

The next thing I do is make a backup. First, if something happens I want to be able to go back. Second, you should always backup before doing something like this, so making it built in prevents issues (as well as avoiding nasty comments on this post). Winking smile

3. Load an array with your desired styles.

$myStyles = @( 'Normal [PACKT]',
               'Numbered Bullet [PACKT]',
               'Screen Text [PACKT]',
               'Code In Text [PACKT]',
               'Code Within Bullets [PACKT]',
               'Code listing [PACKT]',
               'Italics [PACKT]',
               'Figure [PACKT]',
               'Chapterref [PACKT]',
               'Bold [PACKT]',
               'Heading 1,Heading 1 [PACKT]',
               'Heading 2,Heading 2 [PACKT]',
               'Heading 3,Heading 3 [PACKT]',
               'Tip [PACKT]',
               'Layout Information [PACKT]',
               'Figure Caption [PACKT]',
               'Part Heading [PACKT]'
             )

Next up, I load a list of the styles I want to place in my QuickStyles. Also, the array should have the styles in the order you wish them in the gallery. This list is specific to the styles I use most frequently on this project. To see the complete list of available styles within your document, open up Word and load your document. On the bottom right of the QuickStyle gallery is a little button that will bring up the style list.

SNAGHTML3fa2582

Once open, you’ll see a list of all the styles available in this document. Here’s the top of the list, scrolling down will reveal a LOT of styles.

image

The names that appear in here are the ones that you’d use in the array. Just type them into the array declaration exactly as they appear in the Styles list.

4. Load up style type enumerations.

# Load up the style type enumerations.
$wdStyleTypeParagraph = 1  # Paragraph style.
$wdStyleTypeCharacter = 2  # Body character style.
$wdStyleTypeTable = 3      # Table style.
$wdStyleTypeList = 4       # List style.

When we set the property to place something in the QuickStyles gallery, we have to be careful as only certain types of styles may appear in the QuickStyles area. We check this using a property of the Style object called Type, which has several enumerations. While I probably could have loaded the enumerations from the class, sometimes simple is good. Since the values for the enumerations were easily found in MSDN, I just replicated them as variables in PowerShell.

In this script we only use two of them, but for completeness I listed them all should I (or you) ever want to reuse this script as a basis for other Word work with PowerShell.

5. Create a new instance of Word.

$word = New-Object -ComObject Word.Application

The next step is pretty simple. All we do is create a new object of type Microsoft Word, and put a reference to it in the $word variable. Creating the new Word object is akin to opening up Word without a document in it.

6. Make Word visible.

$word.Visible = $true

This next step is optional. By default, when you create a new Word object it is not displayed to the user. If you are applying this to a whole batch of Word documents, I’d omit this step as it will slow down the process. For just one document though it’s not much of an impact. And to be honest, it’s pretty cool to watch Word as it removes then adds the styles.

7. Open the Word document to update.

$doc = $word.Documents.Open($wordFile)

This is probably an obvious step, but we need to load the document we want to update into our Word object. The Open method returns a new variable of type Document (MSDN). The $word variable represents Microsoft Word, the $doc variable represents the specific DOCX file (or DOC) that we want to update.

8. Reset all styles to remove them from the QuickStyles; additionally reset their priority.

foreach ($sty in $doc.Styles)
{
  # Only these two types can be QuickStyles
  if ( ($sty.Type -eq $wdStyleTypeCharacter) -or ($sty.Type -eq $wdStyleTypeParagraph) ) 
    { $sty.QuickStyle = $false }
  
  $sty.Priority = 100
}

This next section is important to understand. The document has a collection of Style objects, stored in the Styles collection. We start by looping over each Style in the document.

Each Style object has a property named QuickStyle, which has a value of true or false. This flag sets whether a style should appear in the QuickStyles gallery (true) or not (false). If you recall from the enumerations section above, there are some styles that cannot be in the QuickStyle area. If we were to attempt to set the QuickStyle property on these, we would get an error (even if setting it to false). Hence the need to check the Type property and only attempt to set the QuickStyle property for those Styles who can appear in the QuickStyle list.

The real challenge in terms of investigation came in ordering the styles within the QuickStyle area. To order them manually, start by clicking the lower right button on the styles dialog.

image

When the Manage Styles window appears, click on the Recommend tab.

image

 

To the left of each style name, you see a number or the word “last”. This number represents the order in which it will recommend styles to you. In other words, the order in which they will appear in the QuickStyles gallery.

With the buttons at the bottom you can rearrange the order in which they appear. Note, it is possible to have multiple styles with the same value. If that happens Word will arrange all styles with the same recommended value alphabetically. The word “last” simply tells Word to put these styles at the very end of the QuickStyles gallery.

It took a lot of research, but I finally figured out the “Recommended” value is contained in a property called “Priority” within the Style object. The word last is represented by the value 100, giving a valid range of 1 to 100 for Priorities.

In the above code I am setting all styles, even the styles built into Word, to 100 which is the equivalent of ‘last’. For my situation this is fine, as I’m only going to be using this specific set of styles in this specific document so I don’t really care about the built in ones. Even if I changed my mind later, all I would need to do is add the built in styles (or any new ones I create) to my array and rerun the script.

At this point then we’ve removed all styles from the QuickStyles gallery, as well as set their priorities to ‘last’. Now it’s time to add the styles we want and order them.

9. Add our desired styles to the QuickStyle gallery and order them.

$priority = 1
foreach ($mySty in $myStyles)
{ 
  # Setting to true will make the style appear in the QuickStyle gallery
  $doc.Styles($mySty).QuickStyle = $true
  # The priority is an integer which determines the sort order within the QS gallery
  $doc.Styles($mySty).Priority = $priority++
}

With this next code, we are going to add our list of styles as well as sort them. It starts by setting the starting point for our Priority (the order) to the value 1, our starting point.

We then enter a foreach loop, where we iterate over each style in the array we assembled back in step 3. Within the loop, we set the QuickStyle property to true. Naturally I’ve only placed style names into the array that are allowed to be in the Style gallery. If you have doubts you could add a check against the Type property, as we did in Step 8, before setting the QuickStyle property.

Note that the Styles collection allows us to address a style by passing in the name of the style. When you assemble the array then, it is important to match the name that appears in Word exactly so it can find it in the array.

Next up is setting the Priority. Again, the Priority is the order in which styles are displayed in the gallery. After assigning the value I use the ++ operator to increase the value for the next iteration of the loop.

Note that since I have far less than 100 items, I don’t have any error checking for exceeding the 100 mark. If you have more than 100 styles for your QuickStyles you’ll need to modify this code. Although quite frankly if you have more than 100 styles in your QuickStyles, it’s not really that quick and you should probably rethink your editing habits. Winking smile

10. Save your work.

$doc.Save()

As the next to the last step, we’ll have the script go ahead and save the document.

11. Close Word (optional).

$doc.Close()

If you modified this script to work in a big loop so you could batch updates, or perhaps you will just want to apply the fix and go on, you could have the script go ahead and close Word. On the other hand, if after applying the updates to the style gallery you’ll immediately want to start editing, just comment out this last step. 

Summary

A final note to close this out, I’ve tested the script with Word 2016 and PowerShell 5.0. From the documentation in MSDN the same techniques also apply to Word 2013. I’ve not tried it on previous versions of Word, but in theory it should work all the way back to the version in which the QuickStyle gallery was introduced. If you’ve tried it on an older version let us know the results by posting a comment below.

Additionally, while I’ve written this on PowerShell 5.0 I’m not doing anything new or unusual, it should work as far back as PowerShell 3.0.

Below is the script, complete with comments to act as a reminder of the explanations above. Just copy and paste, modify the $wordFile and the $myStyles array, and you should be good to go.

 


The Script

<#-----------------------------------------------------------------------------
  Updating and Sorting the Microsoft Word QuickStyle Gallery, ArcaneCode style!

  Author: Robert C. Cain | @ArcaneCode | arcane@arcanetc.com
          http://arcanecode.com
 
  This script is Copyright (c) 2016 Robert C. Cain. All rights reserved.
  No warranty or guarantee is implied or expressly granted. Use at your own
  risk. 

  This script may not be reproduced in whole or in part without the express
  written consent of the author. 
-----------------------------------------------------------------------------#>

# Set the path / file name of the file you want to update
$wordFile = 'C:\Book\Chapter02.docx'

# Make a backup before we do changes. Note if the BAK exists it will be overwritten
$bakfile = $wordFile + '.bak'
Copy-Item -Path $wordFile -Destination $bakfile -Force

# Create an array of the styles you wish, in the order you want 
# them to appear in the QuickStyle area 
$myStyles = @( 'Normal [PACKT]',
               'Numbered Bullet [PACKT]',
               'Screen Text [PACKT]',
               'Code In Text [PACKT]',
               'Code Within Bullets [PACKT]',
               'Code listing [PACKT]',
               'Italics [PACKT]',
               'Figure [PACKT]',
               'Chapterref [PACKT]',
               'Bold [PACKT]',
               'Heading 1,Heading 1 [PACKT]',
               'Heading 2,Heading 2 [PACKT]',
               'Heading 3,Heading 3 [PACKT]',
               'Tip [PACKT]',
               'Layout Information [PACKT]',
               'Figure Caption [PACKT]',
               'Part Heading [PACKT]'
             )

# Load up the style type enumerations.
$wdStyleTypeParagraph = 1  # Paragraph style.
$wdStyleTypeCharacter = 2  # Body character style.
$wdStyleTypeTable = 3      # Table style.
$wdStyleTypeList = 4       # List style.

# Create a new instance of Word
$word = New-Object -ComObject Word.Application

# This is optional, if set it will display Word and let you watch the fun
$word.Visible = $true

# Open the document you wish to reset the styles for
$doc = $word.Documents.Open($wordFile)

# First, reset all styles to not be Quick Styles, 
# and set the priorty to 100 (which will be 'last' in Word)
foreach ($sty in $doc.Styles)
{
  # Only these two types can be QuickStyles
  if ( ($sty.Type -eq $wdStyleTypeCharacter) -or ($sty.Type -eq $wdStyleTypeParagraph) ) 
    { $sty.QuickStyle = $false }
  
  $sty.Priority = 100
}

# Now set the styles like we want 'em!
$priority = 1
foreach ($mySty in $myStyles)
{ 
  # Setting to true will make the style appear in the QuickStyle gallery
  $doc.Styles($mySty).QuickStyle = $true
  # The priority is an integer which determines the sort order within the QS gallery
  $doc.Styles($mySty).Priority = $priority++
}

# Save the document. 
$doc.Save()

# Close up word (Optional, if you want to start editing 
# right away you can comment this out)
$doc.Close()

I have to give a shout out to the folks at Sapien for their PowerShell Studio tool. It has a great feature, Copy HTML, which takes pieces of PowerShell code, copies it to the clipboard and in the process adds the appropriate HTML tags to do the nice coloring.

SQL Saturday 498 Chattanooga

Do you like PowerShell? SQL Server? Are you anywhere close to Chattanooga TN? Then don’t miss this SQL Saturday, June 25th 2016.

I’m giving two sessions (yes two for the price of one!). The first session will be PowerShell 201. It covers advanced concepts you need to know, such as debugging, remoting, security, and code signing.

The second session will educate on using PowerShell with SQL Server. We’ll cover the use of the SQL Provider (SQLPS) as well as the more advanced SQL Management Object library (SMO).

You’ll find the full schedule here, as well as links to register, directions, and the like.

http://www.sqlsaturday.com/498/Sessions/Schedule.aspx 

Best of all, my demos are already uploaded, so you can download early and play along during the presentation!

I’ll mention Chattanooga is a great vacation town, so bring your whole family. There’s a huge aquarium, discovery museum, Lookout Mountain, and tons of attractions to keep them occupied while you’re having fun at SQL Saturday.

Testing PowerShell with Pester

My newest Pluralsight course is now live, Testing PowerShell with Pester! In this 4 hour course I walk through a complete introduction to Pester, to showing you how to both modify existing code as well as create a brand new module using Pester to guide development using the Test Driven Development methodology. You’ll find all the details at:

https://www.pluralsight.com/courses/powershell-testing-pester

Not as familiar as PowerShell as you’d like to be? I have several other courses at Pluralsight that may help, especially my Beginning PowerShell Scripting for Developers course. You can see my full catalog of courses at:

https://www.pluralsight.com/authors/robert-cain

What? You don’t have a Pluralsight subscription? No problem, just shoot me an email to free<at>arcanetc.com and I’ll send you a code good for a 30 day trial to Pluralsight, with which you can watch my courses, or any of the great courses by my fellow Pluralsight authors.

Much TODO with Sapien PowerShell Studio 2016 and Pester

I’ve been working a lot with Peter lately, and as I’ve blogged about before I use Sapien’s PowerShell Studio tool. The new 2016 version has an interesting new capability that while it may seem small, works extremely well when developing Pester tests.

When I create a Pester test, for example for a function, I outline all the things I need to test. Once I’ve created the list, I then go into my test script, and create It statements for each test.

Pester has a switch you can pass to It called –Pending. This will cause Pester to skip over the test. This allows you to stub out needed tests without them interfering with your code. Pester itself will even display pending tests in its output.

    It ‘is a test yet to be implemented’ –Pending {

    }

Knowing you have a test to implement, and being able to quickly find where that code is, are two different things. Tests can get rather long rather quickly. I also admit I don’t always develop the tests linearly. For example, I may develop part of a unit test, then copy that code lower in the script and adapt it for the acceptance test. Then go back up to the next It assertion I need to work on.

That’s where a nifty little feature in the new 2016 version of PowerShell Studio comes in handy. When you add a #TODO: statement (pound sign TODO followed by a colon), it will appear in the navigation drop down in the PowerShell Studio editor.

Here’s a little example. In the screen capture below, you see I have the first test, which has been implemented. Following are two more tests using the –Pending switch.

image

 

Here’s a slightly closer view. You can see the various #TODO: comments. When I click in the navigation drop down, these TODOs show up, allowing me to jump to them easily.

image

This has been surprisingly useful when working with Pester. While the example shown above seems trivial, some of my tests are hundreds of lines long. Being able to quickly navigate to tests I still need to implement keeps my workflow going ahead at a good pace, and keeps me from getting frustrated trying to find the next test I want to implement.

I also have to say the new dark color theme leaves me all warm and fuzzy inside. Smile

Presentation for the SQL PASS PowerShell Virtual User Group

Today (April 6, 2016) I was pleased to present “High Class PowerShell” to the SQL PASS PowerShell Virtual User Group. In it I covered the creation of custom objects and classes in PowerShell.

The first question everyone asks is “where’s the code”. I’ve pasted the code samples for the main demo at the bottom of this post. You can review it here, or copy – paste into your own ISE.

The closing code on calling classes created in PowerShell was discussed in these two blog posts, so I won’t repeat it here.

During the presentation, I mentioned this code is a small subset of my larger Pluralsight course, Beginning PowerShell Scripting for Developers. You’ll find it and my other courses here:

https://www.pluralsight.com/authors/robert-cain

Don’t have a Pluralsight subscription? No problem, Pluralsight has graciously provided me some trial codes I can share with you. These will allow you a one month, no obligation trial with which you can watch any of my courses, or indeed any course found on Pluralsight. Just email free@arcanetc.com to request your code. (If you didn’t get to see the presentation, no worries, you can still email us for the Pluralsight trial code!)

When the video of the presentation goes live I will have a follow up blog post, meanwhile, and without further ado, here is the code sample.

<#-----------------------------------------------------------------------------
  High Class PowerShell

  Author: Robert C. Cain | @ArcaneCode | arcanecode@gmail.com
          http://arcanecode.com
 
  This module is Copyright (c) 2016 Robert C. Cain. All rights reserved.
  The code herein is for demonstration purposes. No warranty or guarentee
  is implied or expressly granted. 
  This module may not be reproduced in whole or in part without the express
  written consent of the author. 
-----------------------------------------------------------------------------#>

#-----------------------------------------------------------------------------#
# Demo 0 -- Object Oriented Terminology
#-----------------------------------------------------------------------------#
<#
                                  class = blueprints
                             properties = describe
                    methods (functions) = actions
                                 object = house
             instantiating a new object = building a new house
each object is an instance of the class = each house is a copy of the blueprint


#>

#-----------------------------------------------------------------------------#
# Demo 1 -- Create a new object 
# This is the most common method of creating objects
#-----------------------------------------------------------------------------#

# The most basic way to create a custom object is to build a hash table with 
# the properties. Using [ordered] will ensure the properties remain in the
# desired order. 
$properties = [ordered]@{ Schema = $Schema
                          Table = $Table
                          Comment = $Comment
                        }

# Start by creating an object of type PSObject
$object = New-Object –TypeName PSObject -Property $properties

# Now you can access it's properties
$object.Schema = 'MySchema'
$object.Table = 'MyTable'
$object.Comment = 'MyComment'
$object


# Much of the time, the process of creating a custom object is wrapped in a 
# function to make it easier. 
function Create-Object ($Schema, $Table, $Comment)
{
  # Build a hash table with the properties
  $properties = [ordered]@{ Schema = $Schema
                            Table = $Table
                            Comment = $Comment
                          }

  # Start by creating an object of type PSObject
  $object = New-Object –TypeName PSObject -Property $properties

  # Return the newly created object
  return $object
}

$myObject = Create-Object -Schema "MySchema" -Table "MyTable" -Comment "MyComment"
$myObject

# Display in text. If you try to reference a property in text, it doesn't
# work quite right.
"My Schema = $myObject.Schema"

# Instead, wrap it in $() to access a property. This will force PowerShell to
# evaluate the object.property call as an expression, and return that to the
# string
"My Schema = $($myObject.Schema)"

# Changing properties is easy
$myObject.Schema = "New Schema"
$myObject.Comment = "New Comment"
$myObject


#-----------------------------------------------------------------------------#
# Demo 2 -- Create a new object by adding properties one at a time
# In the previous demo a property hash table was used to generate the object
# Behind the scenes it does the equivalent of what this function does
#-----------------------------------------------------------------------------#
function Create-Object ($Schema, $Table, $Comment)
{
  # Start by creating an object of type PSObject
  $object = New-Object –TypeName PSObject

  # Add-Member by passing in input object
  Add-Member -InputObject $object `
             –MemberType NoteProperty `
             –Name Schema `
             –Value $Schema

  # Alternate syntax, pipe the object as an input to Add-Member
  $object | Add-Member –MemberType NoteProperty `
                       –Name Table `
                       –Value $Table
  
  $object | Add-Member -MemberType NoteProperty `
                       -Name Comment `
                       -Value $Comment

  return $object
}

$myObject = Create-Object -Schema "MySchema" -Table "MyTable" -Comment "MyComment"
$myObject

# No difference in the way we display values or assign them
"My Schema = $($myObject.Schema)"

$myObject.Schema = "New Schema"
$myObject

# Show this is a PSCustomObject
$myObject.GetType()

# Each property has it's own .NET Data Type. 
# Note this is different from the Member Type
$myObject.Comment.GetType()

# To see the Member Types, pipe to Get-Member
$myObject | Get-Member 


#-----------------------------------------------------------------------------#
# Demo 3 -- Add alias for one of the properties
#-----------------------------------------------------------------------------#
Clear-Host

# Demo 3 -- Add alias so both Comment and Description reference the same thing
Add-Member -InputObject $myObject `
           -MemberType AliasProperty `
           -Name 'Description' `
           -Value 'Comment' `
           -PassThru  # Use passthru to see the new object

"Comment......: $($myObject.Comment)"
"Description..: $($myObject.Description)"

# Change the value
$myObject.Description = 'This is now a description'
"Comment......: $($myObject.Comment)"
"Description..: $($myObject.Description)"

# Demo 3 -- Add script block to object
Clear-Host
$block = { 
           $fqn = $this.Schema + '.' + $this.Table 
           return $fqn
         }

Add-Member -InputObject $myObject `
           -MemberType ScriptMethod `
           -Name 'FullyQualifiedName' `
           -Value $block `
           -PassThru

# Using Get-Member we can see the newly added script method
$myObject | Get-Member 

# When calling our new function, parens are very important, without them 
# PowerShell will just display the function
$myObject.FullyQualifiedName()  


#-----------------------------------------------------------------------------#
# Demo 4 -- Script block with parameters
#-----------------------------------------------------------------------------#
Clear-Host
$block = { 
           param ($DatabaseName)
           $fqn = "$DatabaseName.$($this.Schema).$($this.Table)"
           return $fqn
         }

Add-Member -InputObject $myObject `
           -MemberType ScriptMethod `
           -Name 'DatabaseQualifiedName' `
           -Value $block 

# Place any parameters within the parens
$myObject.DatabaseQualifiedName('MyDBName')  


#-----------------------------------------------------------------------------#
# Demo 5 -- Script Property
#-----------------------------------------------------------------------------#
# These are analogues to properties in C#, with a Getter and Setter function
Clear-Host

# Add a property we can work with
Add-Member -InputObject $myObject `
           –MemberType NoteProperty `
           –Name AuthorName `
           –Value 'No Author Name'

$myObject # Show the added property

# This defines the GET for this property
$getBlock = { return $this.AuthorName }

# This defines the SET. Adding a simple check for the name
$setBlock = { 
              param ( [string]$author )
                            
              if($author.Length -eq 0)
              { $author = 'Robert C. Cain, MVP' }
              
              $this.AuthorName = $author
            }

# Now add the custom Get/Set ScriptProperty to the member
Add-Member -InputObject $myObject `
           -MemberType ScriptProperty `
           -Name Author `
           -Value $getBlock `
           -SecondValue $setBlock

# Demo its use when passing as value
$myObject.Author = 'ArcaneCode'
"`$myObject.Author now equals $($myObject.Author )"

# Now pass in nothing to see the setter functionality kicking in
$myObject.Author = ''
$myObject.Author

# Unfortunately the original property is still available, and thus
# the custom get/set can be bypassed
$myObject.AuthorName = ''
$myObject.Author                       # Author reflects value of AuthorName

$myObject.Author = ''                  # Going thru scriptproperty sets correctly
$myObject.Author


#-----------------------------------------------------------------------------#
# Demo 6 -- Set default properties
# Note: Thanks to Poshoholic for his cool code sample, see it at:
# http://poshoholic.com/2008/07/05/essential-powershell-define-default-properties-for-custom-objects/
#-----------------------------------------------------------------------------#
Clear-Host

# When just running the object, it displays all properties
$myObject

# If you have a lot, this can get overwhelming. Instead you can define a
# default set to display.

# Define the property names in an array
$defaultProperties = 'Schema', 'Table', 'Comment', 'Author'

# Create a property set object and pass in the array 
$defaultPropertiesSet `
  = New-Object System.Management.Automation.PSPropertySet(`
      ‘DefaultDisplayPropertySet’ `
      ,[string[]]$defaultProperties `
      )

# Create a PS Member Info object from the previous property set object
$members `
  = [System.Management.Automation.PSMemberInfo[]]@($defaultPropertiesSet)

# Now add to the object
$myObject | Add-Member MemberSet PSStandardMembers $members

# Now the object will just display the default list in standard output
$myObject

# Little easier to read in a list
$myObject | Format-List

# To display all properties, pipe through format-list with wild card for property
$myObject | Format-List -Property *


#-----------------------------------------------------------------------------#
# Demo 7 - Create a class from .Net Code and call a static method
#-----------------------------------------------------------------------------#

# Load the contents of the file into a variable
$code = @"
using System;

public class MyObjectStatic
{   public static string composeFullName(string pSchema, string pTable)   {     string retVal = "";  // Using retVal for Write-Verbose purposes     retVal = pSchema + "." + pTable;     return retVal;   } // public static void composeFullName
} // class MyObjectStatic

"@

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Call the static method of the object
$mySchema = "dbo"
$myTable = "ArcaneCode"
$result = [MyObjectStatic]::composeFullName($mySchema, $myTable)
$result


#-----------------------------------------------------------------------------#
# Demo 8 - Instantiate an object from .Net Code in embedded code
#-----------------------------------------------------------------------------#

$code = @"
using System;

public class MyObjectEmbedded
{   public string SomeStringName;   public string composeFullName(string pSchema, string pTable)   {     string retVal = "";  // Using retVal for Write-Verbose purposes     retVal = pSchema + "." + pTable;     return retVal;   } // public string composeFullName
} // class MyObjectEmbedded

"@

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Instantiate a new version of the object
$result = New-Object -TypeName MyObjectEmbedded

# Set and display the property
$result.SomeStringName = "Temp"
$result.SomeStringName

# Call the method
$result.composeFullName($mySchema, $myTable)


#-----------------------------------------------------------------------------#
# Demo 9 - Create object from .Net Code in an external file
#-----------------------------------------------------------------------------#

# Path and File Name
$file = 'C:\PS\Classes and Modules\HighClassPowerShell.cs'

# Open the file in the ise to look at it
psedit $file

# Load the contents of the file into a variable
$code = Get-Content $file | Out-String

# Add a new type definition based on the code
Add-Type -TypeDefinition $code `
         -Language CSharpVersion3 

# Call the static method of the object
$mySchema = "dbo"
$myTable = "ArcaneCode"
$result = [MyObjectExternal]::composeFullName($mySchema, $myTable)
$result


#-----------------------------------------------------------------------------#
# Demo 10 - Add to an existing object
#-----------------------------------------------------------------------------#

# Going to add a script method and note property to the System.IO.FileInfo 
# objects returned by Get-ChildItem

# Define the custom script method
$script = { 
            $retValue = 'Unknown'

            switch ($this.Extension)
            {
              '.ps1'  { $retValue = 'Script' }
              '.psm1' { $retValue = 'Module' }
              '.psd1' { $retValue = 'Module Declration' }
              '.cs'   { $retValue = 'C# File'}
              default { $retValue = 'No Clue. Seriously stumped here.'}
            }

            return $retValue
          }

# Load a variable with a collection of file objects
Set-Location 'C:\PS\Classes and Modules'
$items = Get-ChildItem

$itemCount = 0
foreach($item in $items)
{
  # Add a note property, setting it to the current item counter
  $itemCount++
  $item | Add-Member –MemberType NoteProperty `
                     –Name ItemNumber `
                     –Value $itemCount

  # Add script property to the individual file object
  Add-Member -InputObject $item `
             -MemberType ScriptMethod `
             -Name 'ScriptType' `
             -Value $script 

  "$($item.ItemNumber): $($item.Name) = $($item.ScriptType())"
}

# Show our new note and script having been added to the FileInfo type
$items[0] | Get-Member


#-----------------------------------------------------------------------------#
# Demo 11 - Serializing an Object
#-----------------------------------------------------------------------------#

  # Create a simple object
  $mySiteProperties = [ordered]@{ 
                                  WebSite = 'ArcaneCode'
                                  URL = 'http://arcanecode.com'
                                  Twitter = '@ArcaneCode'
                                }

  # Convert it to an object
  $mySite = New-Object –TypeName PSObject -Property $mySiteProperties
  
  # Show the object
  $mySite
  
  # Save the object to a file and 
  $savedDataFile = 'C:\PS\Classes and Modules\mySite.xml'
  $mySite | Export-Clixml $savedDataFile
  psedit $savedDataFile 

  # Now grab the saved object and recreate in a different variable
  $newMySite = Import-Clixml $savedDataFile
  $newMySite


#region Enum

#-----------------------------------------------------------------------------#
# Show Enums in PS
#-----------------------------------------------------------------------------#

# Define the valid values for the enum
Enum MyTwitters
{
  ArcaneTC
  ArcaneCode
  N4IXT
}

# Note when typing the last : will trigger intellisense!
$tweet = [MyTwitters]::ArcaneCode 
$tweet

# See if they picked something valid
[enum]::IsDefined(([MyTwitters]), $tweet)

# Set it to something invalid and see if it passes as an enum
$tweet = 'Invalid'
[enum]::IsDefined(([MyTwitters]), $tweet)

#endregion Enum


#region Basic Class

#-----------------------------------------------------------------------------#
# Basic Class
#-----------------------------------------------------------------------------#
  
Class Twitterer
{
  # Create a property
  [string]$TwitterHandle
  
  # Create a property and set a default value
  [string]$Name = 'Robert C. Cain'

  # Function that returns a string
  [string] TwitterURL()
  {
    $url = "http://twitter.com/$($this.TwitterHandle)"
    return $url
  }

  # Function that has no return value
  [void] OpenTwitter()
  {
    Start-Process $this.TwitterURL()
  }

}

$twit = [Twitterer]::new()
$twit.GetType()

$twit.TwitterHandle = 'ArcaneCode'
$twit.TwitterHandle
  
# See default property value
$twit.Name

# Override default value
$twit.Name = 'Robert Cain'
$twit.Name

$myTwitter = $twit.TwitterURL()
$myTwitter

$twit.OpenTwitter()

#endregion Basic Class


#region Advanced Class

#-----------------------------------------------------------------------------#
# Advanced Class
#   Constructors
#   Overloaded Methods
#-----------------------------------------------------------------------------#

Class TwittererRedux
{
  # Default Constructor
  TwittererRedux ()
  {
  }

  # Constructor passing in Twitter Handle
  TwittererRedux ([string]$TwitterHandle)
  {
    $this.TwitterHandle = $TwitterHandle
  }

  # Constructor passing in Twitter Handle and Name
  TwittererRedux ([string]$TwitterHandle, [string]$Name)
  {
    $this.TwitterHandle = $TwitterHandle
    $this.Name = $Name
  }

  # Create a property
  [string]$TwitterHandle
  
  # Create a property and set a default value
  [string]$Name = 'Robert C. Cain'

  # Static Properties
  static [string] $Version = "2016.04.06.001"

  # Function that returns a string
  [string] TwitterURL()
  {
    $url = "http://twitter.com/$($this.TwitterHandle)"
    return $url
  }

  # Overloaded Function that returns a string
  [string] TwitterURL($twitterHandle)
  {
    $url = "http://twitter.com/$($twitterHandle)"
    return $url
  }

  # Function that has no return value
  [void] OpenTwitter()
  {
    Start-Process $this.TwitterURL()
  }

  # Can launch a twitter page without instantiating the class
  static [void] OpenTwitterPage([string] $TwitterHandle)
  {
    $url = "http://twitter.com/$($TwitterHandle)"
    Start-Process $url
  }

}

# Create a class using default constructor
$twitDefault = [TwittererRedux]::new()

# Display without assigning
"TwitterHandle = $($twitDefault.TwitterHandle)"

# Now assign and display again
$twitDefault.TwitterHandle = 'ArcaneTC'
"TwitterHandle = $($twitDefault.TwitterHandle)"

# Show version one of TwitterURL
"URL = $($twitDefault.TwitterURL())"

# Show overloaded version
"URL = $($twitDefault.TwitterURL('ArcaneCode'))"

# Create a new instance using the second constructor
$twitAdvanced = [TwittererRedux]::new('N4IXT')

# Display without assigning - Should have what was passed in constructor
"TwitterHandle = $($twitAdvanced.TwitterHandle)"

# Create yet another instance using the third constructor
$twitAdvanced2 = [TwittererRedux]::new('ArcaneCode', 'R Cain')
$twitAdvanced2.TwitterHandle
$twitAdvanced2.Name

# Static Value - Can be called without initializing the class
[TwittererRedux]::Version

# Use the static method
[TwittererRedux]::OpenTwitterPage('ArcaneTC')


#endregion Advanced Class

Resolving Ambiguous Class Names Across PowerShell Modules

Right after my last post, I got to wondering what would happen if I had two modules with the same class named defined?

The dictionary defines ambiguous as “unclear or inexact because a choice between alternatives has not been made”. In context of this discussion, it means we have two different PowerShell modules that both have definitions for a class that is named the same (but may not necessarily look the same).

To quote one of the most often used phrases in technical blogging, “Let’s look at an example!”.

Here is our first module, which defines a class MyAmbiguousClass. As you can see it has one property and one method.

# MyAmbiguousModule1.psm1
class MyAmbiguousClass
{
  [string] $MyString = 'Ambigouous String 1'

  [string] GetSomeValue()
  {
    return 'Ambigouous Value 1'
  } 
}

Pretty simple. Now let’s look at a second module. This also has a class defined of MyAmbiguousClass, but it has two properties and one method, all with different names from the previous modules definition.

# MyAmbiguousModule2.psm1
class MyAmbiguousClass
{
  [int] $MyInteger = 2

  [string] $MyName = 'ArcaneCode'

  [string] DidILeaveTheStoveOn()
  {
    return 'Probably not'
  } 
}

You would then declare these at the top of your script thusly:

using module 'C:\PS\Classes and Modules\MyAmbiguousModule1.psm1'
using module 'C:\PS\Classes and Modules\MyAmbiguousModule2.psm1'

As described in my previous post, you would instantiate a new instace with this command:

$myClass = [MyAmbiguousClass]::new()

Now of course you can see the issue, it is not clear which version of the MyAmbiguousClass is being instantiated. What’s even scarier is PowerShell will execute this command without producing an error!

When PowerShell creates the new variable it looks through its memory to find the first time this class is defined, then it uses that definition. In this example, since the MyAmbiguousModule1.psm1 was loaded first, it is that definition that gets used.

If, however, the ‘using module’ statements had been reversed, and MyAmbiguousModule2.psm1 was first, then it is the definition for MyAmbiguousClass found in MyAmbiguousModule2.psm1 that would have been used.

Yikes!

So how to resolve? Well it turns out to be quite simple. All you have to do is prefix the class name with the module name, like so:

$myClass1 = [MyAmbiguousModule1.MyAmbiguousClass]::new()

Now intellisense will list the correct methods and properties, and we can access them in code.

$myClass1.MyString
$myClass1.GetSomeValue()

Will produce

Ambigouous String 1
Ambigouous Value 1

Likewise, we’ll prefix the second version with its module name, MyAmbiguousModule2:

$myClass2 = [MyAmbiguousModule2.MyAmbiguousClass]::new()
$myClass2.MyInteger
$myClass2.MyName
$myClass2.DidILeaveTheStoveOn()

Produces this output

2
ArcaneCode
Probably not

And there you go, resolving ambiguous class names across PowerShell modules is just that easy.

Accessing a PowerShell Class Defined In A Module From Outside A Module

PowerShell 5 introduced the concept of classes. This is an exciting new feature that will really help promote code reusability. It seems natural then, that you would want to organize your classes into PowerShell Modules.

It’s not obvious though, how to use a class defined inside a module from outside that module. There are, in fact, three methods available to you.

Let’s say you have a module, MyModule.psm1. In it you’ve defined a class:

class MyClass
{
  [string] $MyString = 'My String Value'

  [string] GetSomeValue()
  {
    return 'Some Value'
  } 

}

Simple enough. So how do we get to this masterpiece of class coding? The first technique has us creating a function inside the module. Within that function we return a new instance of the class.

function Get-NewMyClass()
{
  return [MyClass]::new()
}

# Export the function, which can generate a new instance of the class
Export-ModuleMember -Function Get-NewMyClass

Using it simply requires we import the module, then call the function. From there we can access the properties and methods of our function.

# Import our test module
Import-Module -Force 'C:\PS\Classes and Modules\mymodule.psm1'

# Use the function to generate a new instance of the 'MyClass' class
$myClassFunction = Get-NewMyClass

# Call a method from it
$myClassFunction.GetSomeValue()

# Get a property of the class
"MyString=$($myClassFunction.MyString)"

Pretty simple and straightforward. This can be a useful method if there are extra checks you wish to do inside the module prior to returning the new instance of the class.

The next method simply has us create a variable within the module, and export that as a module member.

$newClass = [MyClass]::new()
Export-ModuleMember -Variable newClass

You’d then use it in your script, just like any other variable:

# The module also has a variable exported from the function
# of type MyClass. 
"NewClass=$($newClass.MyString)"
$newClass.GetSomeValue()

With that shown I have to say I really don’t like this method. Primarily it is due to the issue of name collisions. If you have a variable also named $newClass in your script, it will block out the one from the class causing the collison.

The final option is the new using clause. I want to give a shout out to PowerShell MVP Dave Wyatt (blog | twitter) for pointing this one out to me.

As the first line of your script (except for comments, which are allowed before it) you use the syntax “using module” followed the module name, or path to the module.

using module 'C:\PS\Classes and Modules\mymodule.psm1'

Here I hard coded the path to the module since this is a simple demo. If the module had been installed in one of the default locations for modules, such as the WindowsPowerShell\Modules folder in the current users Documents area, you could have omitted the path and just put the name of the module.

After this, you would simply create a new variable from the class and use it normally.

$myClassUsing = [MyClass]::new()

# Setting / Getting property
$myClassUsing.MyString                  # Show default property
$myClassUsing.MyString = 'ArcaneCode'   # Change the value
$myClassUsing.MyString                  # Show the change

# Calling a method
$myClassUsing.GetSomeValue()

Note that if you had multiple modules you wanted to access the class definitions in, you would simply have multiple lines of “using module”, each line referencing the module you wanted.

using module 'C:\PS\Classes and Modules\mymodule.psm1'
using module 'C:\PS\Classes and Modules\anothermodule.psm1'

 

There you go. Three ways you can access a class definition stored inside a module, externally. Now not only can you create useful, reusable classes, you can also organize them into modules and use them outside the modules.

High Class PowerShell–Presenting for the SQL PASS PowerShell Virtual Chapter

I’m pleased to announce I’ll be presenting for the SQL PASS PowerShell Virtual Chapter this Wednesday April 6th, 2016, at 12 pm Eastern Time (GMT-5).

In this session I’ll focus on the ability to create custom objects in PowerShell, covering PowerShell versions 3 to 5. I’ll then dive into the use of the new Class and Enum types introduced in PowerShell 5.

To join me, simply go to https://attendee.gotowebinar.com/register/2277748661677465857 and register. It’s free, fast and easy. If you can’t make it live, don’t worry these sessions are recorded and will be posted for viewing later.

Hope to see everyone there!

Easy Installation of Your Own PowerShell Modules

I’ve been working a lot with PowerShell of late, developing modules and deploying them to my own Modules folder. This is a pretty basic set of tasks, something I do over and over. As such, I decided to script it!

First thing I wanted to do was ensure the WindowsPowerShell\Modules folder exists under the users Documents folder. To do that I created a simple function, Add-WindowsPowerShellFolder. If the WindowsPowerShell folder, and under it the Modules folder, don’t exist, they are created.

#—————————————————————————–#
# This checks to see if the WindowsPowerShell folder is in the users
# document folder, then checks to see if the Modules folder exists under
# the WindowsPowerShell folder. If they do not exist, they are created.
# This is primarily a helper function for the Install-MyModule function
#—————————————————————————–#
function Add-WindowsPowerShellFolder()
{
 
  $psUserPath = "C:\Users\$([Environment]::UserName)\Documents\WindowsPowerShell"
  if($(Test-Path $psUserPath) -eq $false)
  {
    New-Item -ItemType Directory -Force $psUserPath
  }

  $psUserModulePath = "$($psUserPath)\Modules"
  if($(Test-Path $psUserModulePath) -eq $false)
  {
    New-Item -ItemType Directory -Force $psUserModulePath
  }

}

Next, I check to see if the folder for the specific Module I’m wishing to deploy exists, and if not create it.

#—————————————————————————–#
# This checks to see if the folder for the Module you want to install
# exists, and if not it adds it.
# This is primarily a helper function for the Install-MyModule function
#—————————————————————————–#
function Add-ModuleFolder($ModuleName)
{
 
  $psUserModulePath = "C:\Users\$([Environment]::UserName)\Documents\WindowsPowerShell\Modules"

  $psModulePath = "$($psUserModulePath)\$($moduleName)"
  if($(Test-Path $psModulePath) -eq $false)
  {
    New-Item -ItemType Directory -Force $psModulePath
  }

}

Finally I call the main function, the Install-MyModule. This has two parameters, the name of the Module and a collection of files to copy.

First, I use the functions above to validate the needed folders exist. There is also a switch, –Replace, which will delete all files and subfolders in the target module folder.

After this is a simple routine that loops over each file and copies it to the target. This allows you to copy files from multiple folders all to the same target.

This routine does assume there are no subfolders to be deployed to the target module folder, which is pretty common.

 

#—————————————————————————–#
# This is the main function of this script. It first ensures the requisite
# folders exist in order to deploy.
#
# If the -Replace switch is used, the target folder will be cleaned out
# prior to the copy.
#
# Next, it iterates over the list of files passed in and copies them to the
# target folder.
#—————————————————————————–#
function Install-MyModule()
{
  [CmdletBinding()]  
  param ( 
         [Parameter( Mandatory = $true,
                     ValueFromPipeline = $false,
                     ValueFromPipelineByPropertyName = $false,
                     HelpMessage = ‘Module Name.’
                     )]
         [string] $ModuleName,
         [Parameter( Mandatory = $true,
                     ValueFromPipeline = $false,
                     ValueFromPipelineByPropertyName = $false,
                     HelpMessage = ‘File to deploy.’
                     )]
         [string[]] $Files,
         [switch] $Replace
        )  # End the parameter block

  begin
  {
    # Validate the PS folder exists
    Add-WindowsPowerShellFolder

    # Set the path to the users modules folder
    $psUserModulePath = "C:\Users\$([Environment]::UserName)\Documents\WindowsPowerShell\Modules"

    # Add a new folder for the module name being installed
    Add-ModuleFolder -ModuleName $ModuleName

    # Set the path to the users module folder including the module to create
    $psModulePath = "$($psUserModulePath)\$($ModuleName)"

    # If the user passed the -Replace switch delete all files from
    # the target folder
    if ($Replace -eq $true)
    {
      Remove-Item "$psModulePath\*.*" -Force -Recurse
    }

  }
 
  process
  {

    foreach($file in $files)
    {
      # Copy our module to the users module folder (force will overwrite if there)
      Copy-Item $file `
                $psModulePath `
                -Force
    }
  }
 
}

Using it is straight forward. First, place the above code into a PS1, then execute it to load the functions into memory. You could also put it in your profile if it is something you do often.

In this example I’ve developed a module called ZipCodeLookup. This example installs it for me into my modules folder.

Install-MyModule ‘ZipCodeLookup’ ‘C:\PS\MyCode\ZipCodeLookup.*’ –Replace

Now you have a simple function you can call to deploy your modules, making your development life a little easier. It can also come in useful when sending a module to a coworker or setting up some common scripts on a different machine. It takes care of making sure the folders are in place as well as copying in your module.

Chattanooga SQL Saturday June 27 2015–Zero to Hero with PowerShell and SQL Server

This Saturday, June 27 2015, I will be at SQL Saturday #410 in Chattanooga, TN. I’ll be presenting a session “Zero to Hero with PowerShell and SQL Server”, in which we’ll start with the basics of PowerShell, then move into working with SQL Server via the PS SQL Provider.

This is going to be an extremely fast paced session, so you may wish to download the code samples ahead of time. I have already uploaded the samples to the event site. Just go to the Sessions menu, Schedule, and you’ll see a bit download button under the session. As of right now I’m the last session of the day, in room 219, but that is subject to change so be sure to check the schedule upon arrival.

Be warned, there is far more code in the download then we’ll be able to cover in a one hour session. It is well commented though, and you should be able to understand it after the session.

If you are looking for even more PowerShell goodness, my friend Aaron Nelson ( @sqlvariant | blog ) is doing a PowerShell for Data Professionals just after lunch. Our two sessions should work well together for those interested in using PowerShell in the world of SQL Server.

Hope to see you there!

Zero to Hero with PowerShell and SQL Server at 24 Hours of Pass

On September 9th I am co-presenting “Zero to Here with SQL Server and PowerShell” for the 24 Hours of PASS. If you’ve not heard of 24 Hours of PASS, it is 24 straight hours of online presentations. This time the sessions are a preview of the SQL PASS Summit in Seattle, WA in November.

At the PASS Summit I, along with two co-workers, am presenting a full day Pre-Con entitled Zero to Hero with PowerShell and SQL Server. I’m also doing a regular session, Make SQL Server POP with PowerShell.

The session for 24 Hours of PASS will take place at 00:00 GMT on September 10th, or for those of us in the states, September the 9th, 8 PM Eastern, 7 PM Central, 6 Mountain, or 5 Pacific. The session is titled the same as the precon, Zero to Hero with PowerShell and SQL Server. Through the preceding link you can see more about the session, get to the full schedule, and most importantly register!

Be sure to check out my co-presenters too, Bradley Ball (@SqlBalls | http://sqlballs.com ) and Jason Strate (@StrateSQL | http://www.jasonstrate.com )

What’s New in PowerShell 3… and 4!

I did a webinar today for Pragmatic Works, What’s New in PowerShell 3… and 4! You can find the recording at:

http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=632

The demo files and slides can be found at:

http://gallery.technet.microsoft.com/Whats-New-in-PowerShell-3-78e1d49e

I had some audio issues during the early part, my apologies and bear with me as it gets better once I switched to phone.

Arcane-SQL–A PowerShell Module for Generating SQL Code

Overview

There are many PowerShell modules available for assisting the busy DBA with managing their SQL Server environment. This isn’t one of them. This module is targeted toward SQL Developers, with special functionality for data warehouse developers. A common task for BI professionals, one that is performed on almost every project, is the creation of a staging area. This might be a set of tables in the data warehouse, perhaps in their own schema, or in an entirely separate database often called an operational data store (ODS).

The staging tables are typically similar in structure to the ones in the source database. Similar, but not identical, as there are some small modifications which are commonly made to the staging tables. First, large data types such as VARCHAR(MAX) are seldom useful in data analysis and thus could be removed. Next, even the most casual user of SSIS will quickly see SSIS prefers to work with the double byte character sets (WSTR in SSIS, which maps to NVARCHAR in T-SQL) as opposed to the single byte (STR/VARCHAR) character sets. It can be helpful to convert these in the staging area.

This Module can (optionally) do all of these things and more when it is used to generate CREATE TABLE or SELECT statements. Imagine if you will a source system with thousands of tables and the need to create a staging area for it in a new data warehouse. This quickly becomes a long, boring tedious task. Now imagine being able to write a bit of PowerShell code and generate these tables in just a few minutes time.

Before diving in, it is highly suggested you download and review the example script, Arcane-SQL-Example.ps1. This demonstrates the most commonly used functions and provides patterns for their use.

Functionality

While the module is full of functions, there are a few core ones that should be highlighted. Complete documentation can be found in the module itself, which has been fully documented using the native PowerShell Help system. In addition there is an example script file which demonstrates some of the most common tasks.

  • Enable-SqlAssemblies – This is the most important function, without calling it nothing else works. Be aware the SQL Server assemblies (including the SMO – SQL Management Objects – and SQL Provider) need to be on the machine where this script is run. This module has been tested on, and intended for, SQL developers with SQL Server Developer Edition installed on their workstations.
  • Join-InvokeInstance and Join-ProviderInstance – Most of the interaction done with the SQL Provider requires the server name and instance name, assembled in a path like syntax. The Invoke-SQLCommand likewise requires this formatting, however it has a little quirk. If the instance is "default" then the Invoke requires it to be omitted while the provider requires it to be present. These two functions reduce the confusion, simply pass in the server name and instance, and they will format things correctly.
  • Get-TablesCollection – When working with tables it is common to iterate over all the tables in a database. This function will generate a PowerShell array of table objects, each object being of type Microsoft.SqlServer.Management.Smo.Table. By having table objects the wide variety of properties for the table are available, such as Schema name, Table name, and Row Count.
  • Get-TableByName – Most commonly scripts will retieve an array of tables using the above Get-TablesCollection, then iterate over them in a foreach loop. There are times however when only a single table from the collection is desired. For those types the Get-TableByName can be used to retrieve a specific table object based on the name of the table. 
  • Remove-SchemasFromTableCollection and Select-SchemasInTableCollection – Get-TablesCollection will return an array of all the tables in a database. Often there is a need to only work with a subset of that table collection. These two functions will filter based on the schema and return a new array. The first, Remove-SchemasFromTableCollection, removes all tables from the array of schemas that are passed in. The second, Select-SchemasInTableCollection, will retain only those tables in the schemas passed into the function.
  • Remove-TablesFromTableCollection and Select-TablesInTableCollection – These work as filters, similar to the functions above. Instead of the schema however, they are based on table name. All tables that begin with the text passed in are either removed, or in the latter function the only ones retained.
  • Get-PrimaryKeyIndex – Returns the primary key object for the passed in table object.
  • Get-PrimaryKeyColumnNames – returns a comma delimited list of the column names in the primary key
  • Decode-IsPrimaryKeyColumn – Will determine if the passed in column name is part of the primary key index
  • Get-TruncateStatement – Will generate a SQL Truncate Table statement based on the table object passed in.
  • Get-DropTableStatement – Generates a Drop Table statement, including the check to see if the table exists, for the passed in table object.
  • Get-CreateStatement – To simply say this function generates a create table statement would do it disservice. It will take a table object and reverse engineer it, generating a create table statement. Unlike other code generators, it has a suite of parameters which allow customization of the generated statement with an eye toward the needs of a data warehouse developer. A few are:
    • DataTypeAlignColumn – Set the column number to line up the data type declarations on. Passing in a value of 1, will suppress alignment and simply place the data type after the column name. The default is column 50.
    • OverrideSchema – It is common place staging tables in the data warehouse in their own schema, often named ‘Staging’ or ‘ETL’. Passing in a value here will include the new schema name in the create table declaration. If the table object passed in had a schema other than dbo, it is placed in front of the table name with an underscore. If it was dbo, the source schema is simply omitted.
    • PrependToTableName and AppendToTableName – Allows extra text to be placed before or after the table name. For example, it is common to create tables with _Delete, _Update, and _Insert in the staging area. This provides a simple way to do that.
    • AdditionalColumns – When creating tables in a data warehousing environment, there are often extra columns to hold metadata about the ETL process. A user of this function can create an array of additional columns using the Add-ColumnDefinition function and have them added to the create table statement.
    • Scrub – This is a very powerful switch. When added it will perform a cleanup to make the output suitible for data warehousing. Columns with large data types such as VARCHAR(MAX) are removed. All single byte character sets in the source are converted to double byte sets.
    • SuppressIdentity – Source systems will sometimes use the IDENTITY clause in the primary key column. Using this switch will suppress that identity clause from being generated in the new create table statement.
    • SuppressNotNull – Often staging tables will not be concerned with null versus not null values. Using this switch will create all columns as nullible, regardless of their setting in the source.
    • IncludeDropTable – Adding this switch will include a ‘if exists drop table’ style clause prior to the create table statement.
    • PrimaryKeysOnly – Will generate a create table statement that only has the primary keys found in the source system.

Finally, if a column in the source table object has a custom data type, the script will reverse engineer the data type back to its basic SQL data type.

  • Get-SelectStatement – Like its sister function Get-CreateStatement, under the covers this function provides a lot of power and flexibility to the statement it creates. Additional columns can be added, columns can be specified to order the output by, table aliases can be used, and most powerful of all is the ability to generate a HASHBYTES column, including the ability to remove specified columns from the hash byte calculation. Here are some of its parameters:
    • AsColumn – The routine will line up the AS <column alias> at the column number passed in here. The default is 50. To not use aligning, set this to 1.
    • PrependToColumnName – Text to include before each column name.
    • AppendToColumnName – Text to place after each column name.
    • AdditionalColumns – A collection of additional columns to be added to the SELECT statement. Useful for adding metadata columns. All items in the AdditionalColumns collection should be generated using the Add-OutputColumn function.
    • OrderByColumns – A list of columns to add to the ORDER BY clause. All items in the OrderByColumns collection should be generated using the Add-OutputColumn function.
    • TableAlias – Allows user to specify an alias to use for the table. The alias is then put in front of each column name.
    • HashBytes – If included the select statement will include a HASHBYTES function with all columns except the primary keys and any columns included in the OmitFromHashBytes collection. The name passed in this parameter will be used for the name of the HashBytes column.
    • OmitFromHashBytes – A collection of column names that should be excluded from the HashBytes calculated column. Useful for excluding metadata columns. All items in the OmitFromHashBytes collection should be generated using the Add-OutputColumn function.
    • Scrub – When included this will remove certain data columns from the output, such as BINARY, NVARCHAR(MAX), XML, and other large types not normally used in data warehouses. Additionally VARCHAR/CHAR are converted to NVARCHAR/NCHAR, and DATETIME converted to DATETIME2(4).
    • Flatten – When included will return the SELECT statement as one long string, without any Carriage Return / Line Feed characters. Additionally, any additional spacing (such as indicated with the AsColumn) is eliminated.
    • IncludeOrderByPK – When included the Primary Keys in the table object are included in the order by clause. If any columns are passed in the OrderByColumns parameter, the Primary Keys occur first in the Order By clause, followed by any columns in the OrderByColumns parameter.
    • IncludeNoLock – When included, a WITH NOLOCK clause is added to the SELECT statement.

Construction

Those PowerShell experts who review the code may note that in many places code does not follow the most "powershelly" way of doing things. In some places rather than using pipelining it was instead decided to use a foreach loop, for example. The intended audience for this module are T-SQL developers who may not be as comfortable in PowerShell as they are T-SQL. Thus using code that more closely aligned with T-SQL patterns would make it more useful and modifiable by SQL developers.

When development first started attempts were made to use advanced functions, using the pipeline for input and output. At some point however this didn’t make sense for a majority of the functions. Time constraints further impinged this effort. Some future revision may attempt to migrate selected functions back to an advanced design, but for now they will have to stand as is.

Development Environment

This module is intended to be used on a developer workstation, not on a server, and especially not on a production server. As such deployment has been made simple, just copy the Arcane-SQL folder to the developers PowerShell module library. On a standard Windows 7 machine this would be C:\Users\<<usernamehere>>Documents\WindowsPowerShell\Modules. If the Windows PowerShell folder and modules subfolder do not exist, they will need to be created first.

To keep things simple, no attempt was made to sign the script. If this is an issue the developer using this module can self sign it on their PC. Check the execution policy on the workstation where the module is installed to ensure sufficient rights to run the module.

This module was developed on machines with both SQL Server (Developer Edition) 2008R2 and 2012 installed (some machines with both) and worked without issue. One machine it was tested on had 3 versions of SQL Server, 2008R2, 2012, and 2014. On that one machine there were some errors with some of the functions passing in the SMO table objects. Those are still being investigated.

SQL Security was assumed to be handled using built in Windows Credentials. Thus the logged in user would need to have rights based on Windows credentials to the SQL Server they are targeting. 

The machine being developed on was using PowerShell v4, however v3 should work as well.

This module was developed using SAPIEN PowerShell Studio 2014. To make life easy for other developers the PowerShell Studio files (Arcane-SQL.psproj, Arcane-SQL.psproj.build, and Arcane-SQL.psprojs) were included in the code. If you are using a different editor, such as the PowerShell ISE, simply discard these files.

Warranty

To put it succinctly, there is none. No guarantee is made for the code in this module, users of this module assume all risks. While I am happy to receive bug reports, I make no promises or guarantees if, or when, they will be fixed.

Contributions

No, not the money kind, code contributions. If anyone wishes to extend the functionality of this module I am happy to collaborate as long as the coding standards demonstrated in this module are adhered to, and the contributions are relevant to the goals of this module. Be aware though this is not a money making effort, so expect no monetary reimbursement for any contributions.

Download

You can download the module and its example at:

http://gallery.technet.microsoft.com/Arcane-SQL-A-PowerShell-185651ad

Arcane Fun Fridays–Going Retro with PowerShell

In terms of the tech world I’m an old geezer. I actually remember the days of monochrome screens, both the amber and green kind. I even have my old Commodore 64 with its light blue font on dark blue background. So I was feeling a bit nostalgic, and decided to create some shortcuts to PowerShell windows with a retro look.

For pre Windows 8, in the start menu navigate to Start then go to your Accessories and/or Administrative tools (varies with which version of Windows you have). Find the icon for Windows PowerShell (not the ISE, just the regular window). (I figure if you are using PowerShell, you probably know where it is without me having to tell you.)

Right click on it, pick “Send To”, then “Desktop (create shortcut)”.

If you are on Windows 8, the simplest thing is to go to your start menu and locate Windows PowerShell. Pin it to your task bar. Now, desktop taskbar shift + right click (the shift is very important when you right click) and do the same Send To / Desktop I mention above.

OK, you now have a short cut, let’s start our retroization. First, give it a decent name. For my example I’ll pick “PS Amber”. Next, right click on it and pick Properties. In the dialog click on the Colors tab. Click on Screen Text, and set the Red / Green / Blue to 255, 185, 0 respectively.

image

Now, this is the part that will bring back the cool “retro” effect. If you are an old geezer like me, you’ll recall the amber monitors also had a big of an orangey background, they weren’t entirely black. Go to the Screen Background, and set its Red value to 32, then use 0 for Green and Blue. That will give it just the hint of orangeness to make it authentic. Of course based on your monitor calibration you may need to tweak it up or down just a tad, adjust until you are happy.

For the Pop-up text, I used the same settings as the Screen Background, and likewise for the Pop-up Background I used the same colors as the Screen Text. And here’s what I wound up with (click on it for a bigger image):

image

Following the same steps above, I created a second short cut called PS Green. I set its Screen Text to an RGB of 0, 255, 0, and the Screen Background to 0, 32, 0. Like with the previous section, I used the reverse colors for the pop ups, 0,255,0 for the background and 0, 32, 0 for the popup text.

image

And finally, the setting that made me wax eloquently over my beloved old Commodore 64:

Screen Text and Popup Background: 44 / 136 / 255

Screen Background and Popup Text: 0 / 0 / 34

image

(If you are too young to remember the Commodore 64, you can read more about it on Wikipedia http://en.wikipedia.org/wiki/Commodore_64 or see it in action, there’s a slew of videos about it on youtube http://www.youtube.com/results?search_query=commodore%2064&sm=3)

Setting up different shortcuts to different color themes can not only make you feel nostalgic, but it can also be quite beneficial when you have multiple PowerShell windows open at the same time. The drastically different colors can make it easy to know which window you have doing what task.

Have fun with it, adjust the colors until they look just right on your own monitor, then create your own crazy color combos. If the are really good post them in the comments area. If I get enough I’ll create a follow up post, giving appropriate credit for each submission.