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.

SAPIEN PowerShell Studio 2014–Customization Redux

Earlier in the week (post) I started this series on SAPIEN PowerShell Studio 2014 discussing customization. How appropriate to wrap up returning to that same topic.

The original version I downloaded was 4.1.47. Late last night I found out a new version has already been released, 4.1.49. I downloaded it and to my pleasure found two more options for color themes, Visual Studio 2013 Dark and Visual Studio 2013 Light.

image

If you read my earlier post you’ll already know what I picked, the new VS dark theme is much darker and richer than the Office 2013 black theme. Take a look:

image

Note that the colors of the editor are not set with this selection, only the backgrounds for things like the menus, title bar, borders, etc. If you want the nifty dark settings in the editor you’ll still need to set them yourself, or use the XML data I listed at the bottom of the already mentioned first post in the series.

You may be wondering how I found out about the update with these new features. Well, SAPIEN has a particularly nice set of forums, located at http://sapien.com/forums/. As you might expect there are forums for all of their products, but you will also find forums for the various scripting languages. In here you can ask generic questions not directly related to any of their products.

After my initial download I had placed a post asking some questions, specifically this post: http://sapien.com/forums/viewtopic.php?f=12&t=7435

Within an hour a support person named David had answered my questions, one of which as you’ll see was on the color themes.

Now, what particularly impressed me was four days later David remembered my forum post, and placed a follow up post letting me know about the update with the new dark color theme. Now that is some outstanding customer service, big kudos to David (whoever you are) for the follow up.

SAPIEN PowerShell Studio 2014–Tools

As I’ve been working with the SAPIEN PowerShell Studio (website), there are a few things in the Tools menu I have found useful. I wanted to call these out, as some may overlook what could be a set of very beneficial items.

If you navigate to the tools menu, over to the left you’ll see these tools:

image

Find in Files is pretty obvious, but don’t over look it. How many times have you been working on something and thought “OK, I wrote something like this once, but just which file was it in?” This will allow you to search for a string you enter. You can specify the folder, and can add a list of file types to look through. By default it will limit the search to PowerShell oriented files, but you could change this easily if, for example, you wanted to search through a CSV file. Especially nice is the ability to use regular expressions or wild cards.

image

Compare Files does just what it says. You select two files, and it will compare them and produce a report of the differences.

Custom Tool is interesting, essentially it becomes a menu you can customize and use to launch executables, parameterized as you want it. Rather than repeating what is already published, I’ll point you to a blog post on the SAPIEN website which explains how to customize this for your needs.

http://www.sapien.com/blog/2013/01/08/powershell-studio-2012-with-git-subversion-and-mercurial-oh-my/

Check Syntax is great, it will quickly look through the current script and identify any syntax errors. I’ve gotten to the point where I always use this after making major edits to a script. Much nicer to find out this way rather than after you start running the script.

Verify Script is similar, but instead of syntax it checks to see if all the required “pieces parts” are present. For example, it looks at any Import-Module statements and validates that those modules are indeed available, and that the functions you reference are there.

Sign Script I haven’t needed to use yet, but if you are in an environment where this is required than this will be a great little shortcut for you.

Restore Points are the last item on the list. When you create a restore point, you can then go make changes to your code, then if you don’t like them you can Rewind to the previous restore point, or use the Restore button to revert back to the point at which you created the original restore point. Once you are happy with your code you can then Delete the restore point.

This is ideal for those situations where you think “hmm, I wonder if this would work…” and want to try out something. But, if it doesn’t you want to be able to roll back to the point prior to your editing. Previously you would have needed to make a copy of the PS1 (or whatever you are editing) file, make changes, then either copy the file back or copy parts of it back into your code. Yuck. Restore Points make this much easier. Even better, the persist between sessions. You can close the entire PowerShell Studio, and when you return and reopen the file those restore points are still active.

I am amazed how little many developers know about the tools at their disposal. Often people look no further than the main tab and never explore the rest of their environment.

Even though individually these tools may seem like small things, together they provide quite a toolbox to solve a lot of common, everyday issues developers face. I hope it encourages you to more fully explore the SAPIEN PowerShell Studio 2014.

SAPIEN PowerShell Studio 2014–Navigation

Continuing my series on SAPIEN PowerShell Studio (website), the next feature I want to focus on is Navigation, both of your files and inside the code.

From a file perspective, PowerShell Studio allows you to organize into projects. Right now I am working on two modules plus one set of scripts which accomplish a common task. Each module I have organized into a project. Here is the project window for one of the modules I’m developing:

image

This allows me to quickly navigate between the various files I have in the project. I can easily open and close these files as I work on them without having to go through the laborious “File, Open…” mechanism in the PowerShell ISE. My other project is the collection of files which consume these modules. Each file performs a specific task, but ultimately get us to a completed solution. Using the Project feature in PowerShell Studio allows me to organize these into one solution, quickly jumping back and forth between them.

Even better, I can have these three items open in separate PowerShell Studio windows at the same time. Note that I did need to go into the Options window, and check on “Allow multiple instances” option.

image

This is a far, far easier way to work than inside the PowerShell IDE included with the native Microsoft PowerShell installation. Speaking of easy, let’s look at code navigation.

One of the first features is the functions window. This simply lists all the functions in the currently open code editor window. Simply double click on one to jump to that function.

image

This is awesome in an large script with many functions.

Next is the ability to easily expand / contract code.

image

You can quickly expand and collapse sections of code to make scrolling through it easier, allowing you to look at only the sections you need to see. I have one script of nearly 1,500 lines, and have placed regions throughout. This feature allows me to focus only on the code I want, and neatly hide what I am not currently working on.

The next thing I’d like to show is the split code window. This allows me to take a single code window, and look at two different parts at the same time. While many editors do have this feature, it is especially great for PowerShell.

image

The final feature I want to mention is Bookmarks. We’ve all had big scripts where we are working on a section of code, need to go look at something elsewhere in the code, then need to jump back to the previous line we were working on. It is useful, then, to drop a bookmark, scroll down to the code you want to look at, then quickly jump back.

image

Using the Bookmark menu you can place a marker in your code. Over to the left of the editor you’ll see the marker.

image

Now you can scroll down and find the other piece of code you want to look at. If it is something you’ll want to come back to continually, you can place a second bookmark. Then using the Next/Previous bookmark buttons you can easily jump back and forth between the two locations in your code.

image

While these are features that come with some editors, such as Visual Studio, they are certainly lacking in the PowerShell ISE. These are basic but critical features that make you much more productive when developing your own PowerShell scripts.

SAPIEN PowerShell Studio 2014–Customization

SAPIEN Technologies (website) released their PowerShell tool, PowerShell Studio 2014 this month. They give a 45 day trial, so I’ve downloaded it and am truly impressed. In the first day it already helped my productivity. I thought I’d spend a few blog posts looking at some of the features.

The focus for this first post is customization. Out of the box, here is the look and feel (click on image for bigger view):

image

And here is what my environment looks like:

image

Yes, I am one of those oddballs who likes dark color themes. And it was pretty easy to set this up. First, in the upper right there’s a drop down. From it you can pick from one of the standard “Office” themes.

image

As you can see, I selected Office 2012 Black. This gives the darker colors that surround the environment. Next to tackle the colors within the editor. In the Home toolbar there is an Options button…

image

which brings up an Options window.

image

Through the Font Style button you can set the values for each

image

Once you have everything set the way you wish, you can save your settings and move them from machine to machine. Just go back to the General tab.

image

Through it you can Save All Settings, which saves everything, from the colors to the layout of all the panels on the screen. Alternately, you can save just the editor settings by clicking the Save Editor Settings. To make it easy should you like this theme I’ve pasted the XML for the dark editor at the end of this blog. All you’ll have to do is copy it to notepad, save it as an XML file, then use the “Load Settings” feature to load it.

PowerShell Studio also makes it easy to alter the layout of the various panels that surround the editor. At the bottom left is a “Layouts” button. Clicking it shows the layouts optimized for the task you are doing.

image

Once you get a layout you like, you can save your custom layout for later. You’ll note my customized layout that I previously saved has been added to the list (ArcaneCode Layout). Now you can quickly jump back and forth between layouts to work on a specific task.

I love the fact that PowerShell Studio 2014 allows me to customize the environment to work the way I want to. You too can customize to your favorite settings.

Finally, as promised, here is the XML for my editor settings.

 

<registry name="SOFTWARE\SAPIEN Technologies, Inc.\PowerShell Studio 2014">
  <k name="Editor">
    <v name="ShowLineNumbers" value="1" kind="DWord" />
    <v name="EnableOutlining" value="1" kind="DWord" />
    <v name="EnableCurrentLineHighlighting" value="0" kind="DWord" />
    <v name="EnableAutoComplete" value="1" kind="DWord" />
    <v name="EnableObjectDescriptions" value="1" kind="DWord" />
    <v name="ShowColumnGuide" value="0" kind="DWord" />
    <v name="ColumnGuide" value="80" kind="DWord" />
    <v name="EnableTrackChanges" value="1" kind="DWord" />
    <v name="EnableAutomaticSyntaxCheck" value="1" kind="DWord" />
    <v name="EnableAliasTabExpansion" value="1" kind="DWord" />
    <v name="EnableCmdletAutoSelect" value="1" kind="DWord" />
    <v name="ConvertTabsIntoSpaces" value="1" kind="DWord" />
    <v name="TabSize" value="2" kind="DWord" />
    <v name="ShowModuleCmdlets" value="1" kind="DWord" />
    <v name="AutoCompleteRequiresExactMatch" value="0" kind="DWord" />
    <v name="AutoInsertModules" value="1" kind="DWord" />
    <v name="ShowExternalTools" value="1" kind="DWord" />
    <v name="EnableDotSourcePrimalSense" value="1" kind="DWord" />
    <k name="Code Formatting">
      <v name="EnableSmartIndent" value="1" kind="DWord" />
      <v name="AutomaticallyFormatOnNewLine" value="1" kind="DWord" />
      <v name="AutomaticallyFormatOnOpenBraces" value="1" kind="DWord" />
      <v name="AutomaticallyFormatOnSemicolon" value="1" kind="DWord" />
      <v name="CurlyBracketsNewLine" value="1" kind="DWord" />
      <v name="IndentParamBlock" value="1" kind="DWord" />
      <v name="IndentAttributeParameters" value="2" kind="DWord" />
      <v name="AlignParameters" value="1" kind="DWord" />
      <v name="AlignAttributeParameters" value="1" kind="DWord" />
    </k>
    <k name="Default Assemblies">
      <v name="mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" value="" kind="String" />
      <v name="System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" value="" kind="String" />
      <v name="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" value="" kind="String" />
      <v name="System.ServiceProcess, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" value="" kind="String" />
    </k>
    <k name="Style">
      <v name="FontName" value="Consolas" kind="String" />
      <v name="FontSize" value="11" kind="String" />
      <v name="BackColor" value="-16777216" kind="DWord" />
      <k name="Alias">
        <v name="Bold" value="True" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Cmdlet">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-160" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Code Snippet Field">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16711681" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Command As Parameter">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="True" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Comment">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-5329234" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="External Tool">
        <v name="Bold" value="True" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-65536" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Function">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Highlighted Reference">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-65281" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Number">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-6750690" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Operator">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Parameter">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Parameter Attribute">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16722899" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Reserved Word">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-256" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="String">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16722899" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Text">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Type">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-9144343" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Unknown Command">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-1" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
      <k name="Variable">
        <v name="Bold" value="False" kind="String" />
        <v name="Italic" value="False" kind="String" />
        <v name="Underline" value="False" kind="String" />
        <v name="ForeColor" value="-16722899" kind="DWord" />
        <v name="BackColor" value="-16777216" kind="DWord" />
      </k>
    </k>
  </k>
</registry>

Installing Windows PowerShell Modules on Multiple User’s Computers–Updated

A few years ago (2010, wow 4 years now) Ed Wilson, aka the Scripting Guy, wrote a blog post on how to copy PowerShell modules onto multiple user’s computers. You’ll find the original version of his script at:

http://blogs.technet.com/b/heyscriptingguy/archive/2010/01/19/hey-scripting-guy-january-19-2010.aspx

Although I got my copy from the new version of his excellent book, Windows PowerShell 4.0 Best Practices.

I have used this for a while, but it turned out there was a drawback. Let me explain.

I’m currently developing what is becoming a rather large module. For management, I wanted to break it down into smaller pieces. The solution is to put logical groupings of functions into individual PS1 files, then dot source those from the module.

. $PSScriptRoot\MyPiecesPartsScript.ps1

Then I could break it up as much as I wanted. The problem with the original script was it only copied PSM1 and PSD1 files. In his script he has a Get-ChildItem which feeds his Copy-Module function. When I added *.PS1 to the list of things to include, the script created a folder for each script. Not what I wanted.

The solution for me was to write another function which would get all the folders in my source and copy the PS1s to the appropriate module folder. I had one other criteria though. In each of my module folders I have a test script where I can test out my module. I name these with the same name as the module but with a –Test on the end. Naturally I don’t want to copy these to the users module folder.

Here then is the function I created. You could copy and paste this right below the functions in Ed’s script:

function Copy-SupportingScripts ()
{
  [CmdletBinding()]
  param ([Parameter( Mandatory = $true,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = 'Please pass a Directoy object.'
                     )]
         [System.IO.DirectoryInfo] $Folder,
         [Parameter( Mandatory = $false,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = 'Enter files to exclude.'
                     )]
         [string] $Exclude
        )

  foreach($dir in $Folder)
  {
    $UserPath = $env:PSModulePath.split(";")[0]
    $targetPath = "$UserPath\$($dir.Name)"
    $sourcePath = "$($dir.FullName)\*.ps1"
    Write-Verbose "Copy $sourcePath to $targetPath"
    if ($Exclude.Length -gt 0)
    {
      Write-Verbose "    Excluding $Exclude in the copy."
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Exclude *-Test.ps1 `
                -Force | Out-Null
    }
    else
    {
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Force | Out-Null
    }
  }
}

To call it, at the bottom of Ed’s original script you can use:

Get-ChildItem -Path C:\PS\Arcane-Modules -Directory |
  ForEach-Object { Copy-SupportingScripts -Folder $_ -Exclude *-Test.ps1 -Verbose }

Here is the final result of my script merged with Ed’s. Make sure to give him plenty of kudo’s for the original.

# —————————————————————————–
# Script: Copy-Modules.ps1
# Author: ed wilson, msft
# Date: 09/07/2013 17:33:15
# Updated by: Robert C. Cain, @ArcaneCode, Pragmatic Works
# Updated Date: 02/20/2014
# Keywords: modules
# comments: installing
# Windows PowerShell 4.0 Best Practices, Microsoft Press, 2013
# Chapter 10
# —————————————————————————–
Function Get-OperatingSystemVersion
{
(Get-WmiObject -Class Win32_OperatingSystem).Version
} #end Get-OperatingSystemVersion

Function Test-ModulePath
{
$VistaPath = "$env:userProfile\documents\WindowsPowerShell\Modules"
$XPPath =  "$env:Userprofile\my documents\WindowsPowerShell\Modules"
if ([int](Get-OperatingSystemVersion).substring(0,1) -ge 6)
   {
     if(-not(Test-Path -path $VistaPath))
       {
         New-Item -Path $VistaPath -itemtype directory | Out-Null
       } #end if
   } #end if
Else
   { 
     if(-not(Test-Path -path $XPPath))
       {
         New-Item -path $XPPath -itemtype directory | Out-Null
       } #end if
   } #end else
} #end Test-ModulePath

Function Copy-Module([string]$name)
{
$UserPath = $env:PSModulePath.split(";")[0]
$ModulePath = Join-Path -path $userPath `
               -childpath (Get-Item -path $name).basename
if ( (Test-Path $modulePath) -eq $false)
   { New-Item -path $modulePath -itemtype directory | Out-Null }
Copy-Item -path $name -destination $ModulePath -force | Out-Null

}

function Copy-SupportingScripts ()
{
  [CmdletBinding()]
  param ([Parameter( Mandatory = $true,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = 'Please pass a Directoy object.'
                     )]
         [System.IO.DirectoryInfo] $Folder,
         [Parameter( Mandatory = $false,
                     ValueFromPipeline = $true,
                     ValueFromPipelineByPropertyName = $true,
                     HelpMessage = 'Enter files to exclude.'
                     )]
         [string] $Exclude
        )

  foreach($dir in $Folder)
  {
    $UserPath = $env:PSModulePath.split(";")[0]
    $targetPath = "$UserPath\$($dir.Name)"
    $sourcePath = "$($dir.FullName)\*.ps1"
    Write-Verbose "Copy $sourcePath to $targetPath"
    if ($Exclude.Length -gt 0)
    {
      Write-Verbose "    Excluding $Exclude in the copy."
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Exclude *-Test.ps1 `
                -Force | Out-Null
    }
    else
    {
      Copy-Item -Path $sourcePath `
                -Destination $targetPath `
                -Force | Out-Null
    }
  }
}

# *** Entry Point to Script ***
$sourceFolder = "C:\PS\Arcane-Modules"

# Ensure the PowerShell folder exists in the users Documents folder
Test-ModulePath

# Copy the modules (psd1 and psm1) files
Get-ChildItem -Path $sourceFolder -Include *.psm1,*.psd1 -Recurse |
  ForEach-Object { Copy-Module -name $_.fullName }

# Copy any supporting ps1 files.
# Remove the -Exclude directive if you don’t want to exclude anything.
Get-ChildItem -Path $sourceFolder -Directory |
  ForEach-Object { Copy-SupportingScripts -Folder $_ `
                                          -Exclude *-Test.ps1 `
                                          -Verbose
                 }

One final and very important note. Ed’s original script was written in the PowerShell v2 days. My function uses the new –Directory switch introduced in PowerShell v3, so you will need at least v3 to make this work.

Exporting Data from SQL Server to CSV Files for Import to MongoDB Using PowerShell

I’ve been exploring other database systems, in order to determine how to import data from them using SQL Server Integration Services (SSIS). My first step though was to create some test data. I wanted something familiar, so I decided to export the Adventure Works Data Warehouse sample database and import into MongoDB. While I had many options I decided the simplest way was to first export the data to CSV files, then use the MongoDB utility mongimport. Naturally I turned to PowerShell to create an automated, reusable process.

First, if you need the Adventure Works DW database, you’ll find it at http://msftdbprodsamples.codeplex.com/. Second, I did my export from a special version of Adventure Works DW I created called AdventureWorksDW2014. This is optional, but if you want to have a version of Adventure Works DW updated with current dates, see my post at http://arcanecode.com/2013/12/08/updating-adventureworksdw2012-for-2014/. Third, I assume you are familiar with MongoDB, but if you want to learn more go to http://www.mongodb.org/.

Below is the PowerShell 3 script I created. The script is broken into four regions. The first, User Settings, contains the variables that you the user might need to change to get the script to run. It has things like the name of the SQL Server database, the path to MongoDB, etc.

The second region, Common, establishes variables that are used by the remaining two regions. You shouldn’t need to change or alter these. The third region accesses SQL Server and exports each table in the selected database to a CSV format file.

The final region, “Generate MongoDB import commands”, creates a batch (.BAT) file which has all the commands needed to run mongoimport for each CSV file. I decided not to have the PowerShell script execute the .BAT file so it could be reviewed before it is run. There might be some tables you don’t want to import, etc.

It is also quite easy to adapt this script to just create CSV files from SQL Server without using the MongoDB piece. Simply remove the fourth and final region, then in the Common and User Settings regions remove any variables what begin with the prefix “mongo”.

As the comments do a good job of explaining what happens I’ll let you review the included documentation for step by step instructions.

#==================================================================================================
# SQLtoCSVtoMongoDb.ps1
# Robert C. Cain | @ArcaneCode |
http://arcanecode.com
#
# If you need a simple way to export data from SQL Server to MongoDb, here is one way to do it.
# The script starts by setting up some variables to the server environment (see the User Settings
# region)
#
# Next, it exports data from each table in the selected database to individual CSV files.
# Finally, it generates a batch file which executes mongoimport for each csv file to import
# into MongoDb.
#
# I broke this into four regions so if all that is desired is a simple export of data to CSVs,
# you can simply omit the final region along with any variables that begin with "mongo".
#
# While I could have gone ahead and run the batch file at the end, I chose not to in order to
# give you time to review the output prior to running the batch file.
#==================================================================================================

Clear-Host

#region User Settings

  # In this section, set the variables so they are appropriate for your project / environment
 
  # This is the spot where you want to store the generated CSVs.
  # Make sure it does NOT end in a \
  $csvPath = "C:\mongodb"

  # If you are running this on a computer other than the server, set the name of the server below
  $sqlServer = $env:COMPUTERNAME

  # If you have a named instance be sure replace "default" with the name of the instance
  $sqlInstance = "\default"

  # Enter the name of the database to export below
  $sqlDatabaseName = "AdventureWorksDW2014"

  # The settings below only apply to the MongoDB code generation
  # Assemble path to mongodb. This assumes utlities are stored in the default bin folder
  $mongoPath = "C:\mongodb"
  $mongoImport = "$mongoPath\bin\mongoimport"

  # Set the server name and port
  $mongoHost = "localhost"   # Leave blank to default to localhost
  $mongoPort = ""            # Leave blank to default to 27107
 
  # Set the user name and password, leave blank if it isn’t needed
  $mongoUser = ""
  $mongoPW = ""

  # Enter the name of the database to import to.
  $mongoDatabaseName = "AdventureWorksDW2014"

  # Upserts are REALLY slow, especially on large datasets. Setting this to $true will turn off
  # the upsert option. If set to true, you are responsible for either deleting all documents
  # in the collection before hand, or allowing the risk of duplicates.
  #
  # Setting to false will enable the upsert option for mongoimport, and attempt to determine the
  # keys and (if found) add them to the final mongoimport command.
  $mongoNoUpsert = $true

#endregion

#region Common ————————————————————————————
 
  # This section sets variables used by both regions below. There is no need to alter anything
  # in this region.

  # Import the SQLPS provider (if it’s not already loaded)
  if (-not (Get-PSProvider SqlServer))
    { Import-Module SQLPS -DisableNameChecking }

  # Assemble the full servername \ instance
  $sqlServerInstance = "$sqlServer\$sqlInstance"

  # Assemble the full path for the SQL Provider to get to the database
  $sqlDatabaseLocation = "SQLSERVER:\sql\$sqlServerInstance\databases\$sqlDatabaseName"

  # Now tack on the Tables ‘folder’ to the SQL Provider path, the move there
  $sqlTablesLocation = $sqlDatabaseLocation + "\Tables"
  Set-Location $sqlTablesLocation

  # Get a list of tables in this database
  $sqlTables = Get-ChildItem

#endregion

#region Export SQL Data —————————————————————————
  # In this section we will export data from each table in the database to a CSV file.
  # WARNING: If the CSV file exists, it will be overwritten.

  # These are just used to display informational messages during processing
  $sqlTableIterator = 0
  $sqlTableCount = $sqlTables.Count

  # Iterate over each table in the database
  foreach($sqlTable in $sqlTables)
  {
    $sqlTableName = $sqlTable.Schema + "." + $sqlTable.Name   

    # I’ll grant you the next little bit of formatting for the progress messages is a bit
    # OCD on my part, but I like my output formatted and easy to read.
    $sqlTableIterator++
    $padCount = " " * (1 + $sqlTableCount.ToString().Length – $sqlTableIterator.ToString().Length)
    $sqlTableIteratorFormatted = $padCount + $sqlTableIterator

    if( $sqlTableName.Length -gt 50 )
      { $padTable = " " }
    else
      { $padTable = " " * (50 – $sqlTableName.Length) }

    Write-Host -ForegroundColor White -NoNewline "Processing Table $sqlTableIteratorFormatted of $sqlTableCount : $sqlTableName $padTable"
   
    # If the instance is "default", we have to exclude it when we use Invoke-SqlCmd
    if($sqlInstance.ToLower() -eq "\default")
      { $sqlSI = $sqlServer }
    else
      { $sqlSI = $sqlServerInstance }

    # Load an object with all the data in the table
    # Note if you have especially large tables you may need to modify this
    # section to break things into smaller chunks.
    $sqlCmd = "SELECT * FROM " + $sqlTableName
    $sqlData = Invoke-Sqlcmd -Query $sqlCmd `
                             -ServerInstance $sqlSI `
                             -SuppressProviderContextWarning `

    # Now write the data out.
    # Note utf8 encoding is important, as it is all mongoimport understands
    # Also need to omit the Type Info header PowerShell wants to write out
    Write-Host -ForegroundColor Yellow "    Writing to table $sqlTableName.csv"
    $sqlData | Export-Csv -NoTypeInformation -Encoding "utf8" -Path "$csvPath\$sqlTableName.csv"

  }

  # Just add a blank line after the processing ends
  Write-Host

#endregion

#region Generate MongoDB import commands ———————————————————-

  # In this region we will generage the commands to import our newly exported data
  # into an existing database in MongoDB. This is an example of our desired output (wrapped
  # onto multiple lines for readability, in the output it will be a single line):

  #  C:\mongodb>bin\mongoimport –host localhost -port 27107
  #                             –db AdventureWorksDW2014 –collection DimSalesReason
  #                             –username Me –password mySuperSecureP@ssW0rd!
  #                             –type csv –headerline –file DimSalesReason.csv
  #                             –upsert –upsertFields SalesReasonKey

  # Note several of these parameters are optional, and could use defaults, or be potentially
  # omitted from the final output, based on the choices at the very beginning of this script

  # Feel free to alter the $mongoCommand as needed for other circumstances

  # Final warning, the database must already exist in MongoDb in order to import the data. This
  # script will not generate the database for you.

  # Create the name for the batch file we will generate
  $mongoBat = $csvPath + "\Import_SQL_" + $sqlDatabaseName + "_to_MongoDb_" + $mongoDatabaseName + ".bat"

  # See if file exists, if so delete it
  if (Test-Path $mongoBat)
    { Remove-Item $mongoBat }

  # These are just used to display informational messages during processing
  $sqlTableIterator = 0
  $sqlTableCount = $sqlTables.Count

  # mongoimport allows us to do upserts, helping to eliminate duplicate rows on import.
  #
  # To make an upsert work there has to be a key column to match up on. Fortunately,
  # most tables in the SQL Server world have Primary Keys, so we can find out what
  # columns those are and add it to the command. Note if there is no PK in SQL Server,
  # no upsert will be attempted.
  #
  # Note though that upserts are REALLY slow, so the option to skip them is
  # built into the script and set at the top (mongoNoUpsert). The generated batch file
  # assumes that either a) you have deleted all data from the collection ahead of time,
  # or b) you are OK with the risk of duplicate data.

  # Iterate over each table in the database to build the mongoimport command
  foreach($sqlTable in $sqlTables)
  {
    $sqlTableName = $sqlTable.Schema + "." + $sqlTable.Name

    # A bit more OCD progress messages
    $sqlTableIterator++
    $padCount = " " * (1 + $sqlTableCount.ToString().Length – $sqlTableIterator.ToString().Length)
    $sqlTableIteratorFormatted = $padCount + $sqlTableIterator
    Write-Host -ForegroundColor Green "Building mongoimport command for table $sqlTableIteratorFormatted of $sqlTableCount : $sqlTableName"

    # Begin building the command
    $mongoCommand = "$mongoImport "
   
    if ($mongoHost.Length -ne 0)
      { $mongoCommand += "–host $mongoHost " }

    if ($mongoPort.Length -ne 0)
      { $mongoCommand += "–port $mongoPort " }

    $mongoCommand += "–db $mongoDatabaseName –collection $sqlTableName "

    if ($mongoUser.Length -ne 0)
      { $mongoCommand += " –username $mongoUser –password $mongoPW " }

    $mongoCommand += " –type csv –headerline –file $csvPath\$sqlTableName.csv "
       
    # Build the upsert clause, if the user has elected to use it.
    if ($mongoNoUpsert -eq $false)
    {
      $mongoPKs = ""
      foreach($sqlIndex in $sqlTable.Indexes)
      {
        if($sqlIndex.IndexKeyType -eq ‘DriPrimaryKey’)
        {
          foreach($sqlCol in $sqlIndex.IndexedColumns) #$sqlPKColumns)
          {
            if ($mongoPKs.Length -ne 0)
              { $mongoPKs += "," }
            # Note column names are returned with [ ] around them, and must be removed
            # Have to use -replace instead of .Replace() because $sqlCol is an column not a string
            $mongoPKs += ($sqlCol -replace "\[", "") -replace "\]", ""
          }
               
          $mongoCommand += " –upsert –upsertFields $mongoPKs"
        }           
      }
    }

    # Append the command to the batch file
    $mongoCommand | Out-File -FilePath $mongoBat -Encoding utf8 -Append

  }

  # Just add a blank line after the processing ends
  Write-Host

#endregion

 

Atlanta Code Camp 2013

On Saturday August 24, 2013 I’m presenting “What’s New In PowerShell v3… and 4!” at the Atlanta Code Camp. If you would like a copy of the demos, you can e-mail me either arcanecode at gmail.com or rcain at pragmaticworks.com.

Or, if you have BitTorrent Sync, you can use this secret key to get a copy of all my demos: BYEHZZ5K2DQ2AEBJDVSS4UUHZEGG646GZ  (Note this is the read-only key.)

I’m Speaking! SQL Saturday Nashville and PowerShell Saturday Atlanta

Just wanted to let folks know I’ll be doing presentations at two upcoming events.

The first is SQL Saturday #145 in Nashville. That’s this weekend, October 13th. I’ll be giving my “Introduction to Data Warehousing / Business Intelligence” presentation. Here is the slide deck I’ll be using: introtodatawarehousing.pdf

My second presentation will be October 27 in Atlanta at PowerShell Saturday #003. Yep, the PowerShell guys are taking the Saturday concept and kicking off a series of PowerShell Saturdays. This is only the third, but I see many more coming in the future.

At PowerShell Saturday I’ll be presenting “Make SQL Server Pop with PowerShell”. I’ll cover both the SMO and SQL Provider during this session.

Looks like it’ll be a busy October, but I’d hurry as both events are filling up so don’t wait and get registered now!

Preventing PowerShell from Running in the ISE

Once in a great while you will have the need to force your scripts to only run in the console. Mostly likely you have created special formatting commands that only work in the console, but would cause the script to crash in the ISE. It is prudent then to prevent that code from even executing. But how?

Turns out it’s pretty simple. The PowerShell ISE has a built in variable called $psise. Within the ISE you can use this variable to alter various settings in the environment, such as colors, or the position of the code window. When you run from the console however, $psise will be null.

So the solution is simple, just check to see if $psise is null. To make it even easier, I put the code inside a simple function I can import to my various scripts. It checks to see if we’re in the ISE, and if so presents the user with an error and breaks out.

Note, the break will only be effective if you call this from the outermost level script, that is the script you actually run from the console command line. Break will return control to the outer script, which in that case would be nothing.

I admit this is not something you’ll use all that often, if it were you might consider throwing an error as opposed to breaking so you can nest it at any level.

Here is my simple version of the script.

function Prevent-IseExecution ()
{
<#
  .SYNOPSIS
  Prevents a script from executing any further if it's in the ISE
  
  .DESCRIPTION
  The function determines whether it's running in the ISE or a window, 
  if it's in the ISE it breaks, causing further execution of the script to halt. 
#>

  # If we're in the PowerShell ISE, there will be a special variable
  # named $PsIse. If it's null, then we're in the command prompt
  if ($psise -ne $null)
  {
    "You cannot run this script inside the PowerShell ISE. Please execute it from the PowerShell Command Window."
    break # The break will bubble back up to the parent
  }

}

# Stop the user if we're in the ISE
Prevent-IseExecution

# Code will only continue if we're in command prompt
"I must not be in the ISE"

PowerShell Editors: PowerSE

I’ve seen interest really growing in PowerShell over the last year or two. The editor included with PowerShell v2 though, does leave much to be desired. I can say from experience the v3 editor is FAR better, but still has some room to grow.

There are many good editors on the market, but many people just getting into PowerShell need something cheap (i.e free) to learn on. I’ve found the PowerSE editor from PowerWF to be an excellent choice to fill this gap.

As I indicated it is indeed free. And for a free editor it’s remarkably full featured. It has a very good help system, intellisense, and the ability to see your call stack. It also has a variable pane where you can see all of the variables, and their values for the current session.

You can have multiple tabs open, each with a different script. It has some customizability, you can set the font for both the editor and the console. My favorite feature is the very easy to use debugger, complete with breakpoints and the ability to examine variables through the aforementioned variable pane.

I still think it’s important to know the PowerShell IDE, as it’s on all the latest builds of Windows. But once you have grasped it’s basics, go grab yourself a copy of PowerSE. Only then will you really be able to enjoy the happiness that is PowerShell.

Simulating SQL Server Work with PowerShell

No, I’m not talking about simulating your job so you can sit home in your PJs and play Call of Duty. (Although if you work out a way to do so, call me!) What I’m speaking of is emulating a workload on your server.

There’s many reasons for wanting to do this. For folks like me, who do a lot of demos, it can be a good way to get your server busy so you can demo things like DMVs or the SQL Provider. Another use would be stress or performance testing your applications. Ensuring you get good retrieval times when the server is loaded.

I have to give a little shout out here to my friend and fellow MVP Allen White (Blog | Twitter). I had attended one of his sessions back at a SQL Saturday, and when I downloaded his samples found a “SimulateWork” PowerShell script among the sample code. In Allen’s script he used the .Net SQLClient library to do his work. I decided to emulate the concept but to rewrite the entire thing using the SQL Provider. I borrowed a few of his queries so I could be sure my results were similar.

The script is pretty simple. I have a function that will load up the SQL Provider, if it’s not already loaded. Next is the SimulateWork function. It has one parameter, how many times you want to execute the code in the loop.

Within the function I load up a series of T-SQL queries against the AdventureWorks2012 database using “here” strings. I then execute them using the Invoke-SqlCmd cmdlet. Finally I have a little code which calls the functions.

My goal with this script was to get the server ram loaded up and simulate some I/O so I could demo a few simple things. Astute observers will notice that, because these are just executing the same T-SQL commands over and over, for the most part SQL Server will just hit its plan cache and memory each time. If those are important to you, I’d suggest altering the T-SQL commands to include a where clause of some sort then each time through the loop add your new where condition to the variable holding the T-SQL.

So without further ado, here’s the full script.

#******************************************************************************
# Simulate Work
#
# This routine will simulate work being done against a SQL Server. Ideal
# for demoing things like the SQL Profiler or DMV Stats. 
#
# The T-SQL queries were tested against the AdventureWorks2012 database. You
# may have to alter some queries if you are on previous versions of
# AdventureWorks.
#
# Author......: Robert C. Cain
# Blog........: http://arcanecode.com
# Twitter.....: http://twitter.com/arcanecode
# Last Revised: July 17, 2012
#
# Credit...: This script is based on one by SQL MVP Allen White.
#   Blog...: http://sqlblog.com/blogs/allen_white/default.aspx
#   Twitter: http://twitter.com/sqlrunr
#
# In his original script, he used the System.Data.SqlClient .Net library to 
# simulate work being done on a SQL Server. I liked the idea, but rewrote 
# using the SQL Provider. A couple of the SQL queries I borrowed from 
# his routine. 
#
# Other uses: The techniques below might also be a good way to stress test
# a system. Alter the t-sql (and probably variable names) and away you go. 
#******************************************************************************

#------------------------------------------------------------------------------
# Loads the SQL Provider into memory so we can use it. If the provider is 
# already loaded, the function does nothing. This makes it safe to call
# multiple times. 
#------------------------------------------------------------------------------
function Load-Provider
{
  # Get folder where SQL Server PS files should be
  $SqlPsRegistryPath = "HKLM:SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
  $RegValue = Get-ItemProperty $SqlPsRegistryPath
  $SqlPsPath = [System.IO.Path]::GetDirectoryName($RegValue.Path) + "\"
  
  # Check to see if the SQL provider is loaded. If not, load it. 
  [String] $IsLoaded = Get-PSProvider | Select Name | Where { $_ -match "Sql*" }

  if ($IsLoaded.Length -eq 0)
  { 
    # In this case we're only using the SQL Provider, so the code to load the
    # SMO has been commented out. Leaving it though in case you copy and paste it from somewhere
    # and need it. 
    <#
  # ----------------------------------------------------------------------------------------
    # Load the assemblies so we can use the SMO objects if we want. 
    # Note if all you need is the basic SMO functionality like was in 2005, you can get away
    # with loading only the first three assemblies. 
  # ----------------------------------------------------------------------------------------
    $assemblylist = 
    "Microsoft.SqlServer.ConnectionInfo ", 
    "Microsoft.SqlServer.SmoExtended ", 
    "Microsoft.SqlServer.Smo", 
    "Microsoft.SqlServer.Dmf ", 
    "Microsoft.SqlServer.SqlWmiManagement ", 
    "Microsoft.SqlServer.Management.RegisteredServers ", 
    "Microsoft.SqlServer.Management.Sdk.Sfc ", 
    "Microsoft.SqlServer.SqlEnum ", 
    "Microsoft.SqlServer.RegSvrEnum ", 
    "Microsoft.SqlServer.WmiEnum ", 
    "Microsoft.SqlServer.ServiceBrokerEnum ", 
    "Microsoft.SqlServer.ConnectionInfoExtended ", 
    "Microsoft.SqlServer.Management.Collector ", 
    "Microsoft.SqlServer.Management.CollectorEnum"
    foreach ($asm in $assemblylist) {[void][Reflection.Assembly]::LoadWithPartialName($asm)}
    #>

    # Set the global variables required by the SQL Provider    
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    # Load the actual providers  
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
  
    # The Types file lets the SQL Provider recognize SQL specific type data.
    # The Format file tells the SQL Provider how to format output for the 
    # Format-* cmdlets.
    # First, set a path to the folder where the Type and format data should be
    $sqlpTypes = $SqlPsPath + "SQLProvider.Types.ps1xml"
    $sqlpFormat = $sqlpsPath + "SQLProvider.Format.ps1xml"
  
    # Now update the type and format data. 
    # Updating if its already loaded won't do any harm. 
    Update-TypeData -PrependPath $sqlpTypes
    Update-FormatData -prependpath $sqlpFormat
  }
  
  # Normally I wouldn't print out a message, but since this is a demo
  # it will give us a nice 'warm fuzzy' the provider is ready
  Write-Host "SQL Server Libraries are Loaded"

}

#------------------------------------------------------------------------------
# This function simply loads a series of SQL Commands into variables, then
# executes them. The idea is to simulate work being done on the server, so
# we can demo things like SQL Profiler. 
# 
# Parameters: $iterations - The number of times to repeat the loop
#------------------------------------------------------------------------------
function SimulateWork ($iterations) 
{

  $sqlSalesOrder = @"  
  SELECT d.SalesOrderID
       , d.OrderQty
       , h.OrderDate
       , o.Description
       , o.StartDate
       , o.EndDate
    FROM Sales.SalesOrderDetail d
   INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
   INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
   WHERE d.SpecialOfferID <> 1
"@

  $sqlSalesTaxRate = @"  
  SELECT TOP 5
         sp.Name
       , st.TaxRate
    FROM Sales.SalesTaxRate st
    JOIN Person.StateProvince sp 
         ON st.StateProvinceID = sp.StateProvinceID
   WHERE sp.CountryRegionCode = 'US'
   ORDER BY st.TaxRate desc ;
"@
  
  $sqlGetEmployeeManagers = @"
  EXECUTE dbo.uspGetEmployeeManagers 1
"@

  $sqlSalesPeople = @"
  SELECT h.SalesOrderID
       , h.OrderDate
       , h.SubTotal
       , p.SalesQuota
    FROM Sales.SalesPerson p
   INNER JOIN Sales.SalesOrderHeader h 
         ON p.BusinessEntityID = h.SalesPersonID ;
"@

  $sqlProductLine = @"
  SELECT Name
       , ProductNumber
       , ListPrice AS Price
    FROM Production.Product
   WHERE ProductLine = 'R'
     AND DaysToManufacture < 4
   ORDER BY Name ASC ;
"@

  $sqlHiringTrend = @"
  WITH HiringTrendCTE(TheYear, TotalHired)
    AS
    (SELECT YEAR(e.HireDate), COUNT(e.BusinessEntityID) 
     FROM HumanResources.Employee AS e
     GROUP BY YEAR(e.HireDate)
     )
   SELECT thisYear.*, prevYear.TotalHired AS HiredPrevYear, 
    (thisYear.TotalHired - prevYear.TotalHired) AS Diff,
    ((thisYear.TotalHired - prevYear.TotalHired) * 100) / 
                 prevYear.TotalHired AS DiffPerc
   FROM HiringTrendCTE AS thisYear 
      LEFT OUTER JOIN 
        HiringTrendCTE AS prevYear
   ON thisYear.TheYear =  prevYear.TheYear + 1;
"@

  
  $mi = $env:COMPUTERNAME + "\SQL2012"  
  Set-Location SQLSERVER:\sql\$mi\databases\AdventureWorks2012

  for ($i=1; $i -lt $iterations; $i++) 
  {
    Write-Host "Loop $i"
    $outSalesOrder = Invoke-Sqlcmd -Query $sqlSalesOrder -ServerInstance $mi -SuppressProviderContextWarning
    $outSalesTaxRate = Invoke-Sqlcmd -Query $sqlSalesTaxRate -ServerInstance $mi -SuppressProviderContextWarning
    $outGetEmployeeManagers = Invoke-Sqlcmd -Query $sqlGetEmployeeManagers -ServerInstance $mi -SuppressProviderContextWarning
    $outSalesPeople = Invoke-Sqlcmd -Query $sqlSalesPeople -ServerInstance $mi -SuppressProviderContextWarning
    $outProductLine = Invoke-Sqlcmd -Query $sqlProductLine -ServerInstance $mi -SuppressProviderContextWarning
    $outHiringTrend = Invoke-Sqlcmd -Query $sqlHiringTrend -ServerInstance $mi -SuppressProviderContextWarning
  }

}

#------------------------------------------------------------------------------
# This is the code that executes the above functions. To change the workload
# simply change the number of iterations. 
#------------------------------------------------------------------------------
Load-Provider

# Number of times to repeat the work simulation. 
$iterations = 100 

Write-Host "Starting simulated work"
SimulateWork $iterations
Write-Host "Done Working"

SAPIEN PowerShell Studio 2012

Over the next few blog posts I thought I’d present some of the various PowerShell IDE’s on the market. Yes, that’s right, there’s more out there then just the IDE that ships with PowerShell. I thought I’d kick things off with the “Cadillac” of tools, SAPIEN PowerShell Studio 2012.

This by far is the most comprehensive tool of any on the market. It’s key selling point is the ability to quickly and easily create Windows Forms that can be called from, and raise events in, your PowerShell scripts.

OK, I can already hear you. “Hey Robert this is supposed to be scripts, what do we need Windows Forms for?” That’s a great question.

A very common task to do in scripting is the creation of virtual machines. You can imagine though all of the things you would need to enter in order to create the machine. What’s the name? What’s the activation key? What do you want installed? SQL Server? SharePoint?

You could, of course, have the script prompt you one question at a time. Or have a vast array of command line switches and parameters you need to enter just right in order to run the script. You could reduce complexity by having multiple scripts, but then you increase the workload. Then there’s the issue of who is going to run the script.

It would be far preferable to have a simple, single windows dialog pop up and ask all these questions at once. The person running the script could enter the information in any order, and when they were done just click an big OK button to launch the script.

This also expands the sphere of people who can run the script. Now you will be able to let an experienced PowerShell developer create the complex script, then give it to someone who may not even know PowerShell. They just run a single command, enter in some information into an easy to understand Windows Form, and they are off and running.

But Windows Forms are just the tip of the iceberg. For example, PowerShell Studio makes it easy to package your scripts into executables. Yep, you can take all your proprietary code and keep it safe from prying eyes by compiling it into an easy to distribute EXE file. 

It also has the ability to run your scripts in either 32 or 64 bit mode, all within the same editor. Very nice if you are having to support older systems as well as more modern ones.

From a development standpoint, it has the nice feature of organizing your scripts into projects. This makes management of them much easier.

It has an outstanding editor, with a great help system, and a snippet library loaded up with snippet goodness. The object browser is one of my favorite features. Using it you can drill down into not only PowerShell objects, but .Net, WMI, the file system, and even databases. I find this incredibly useful, I can quickly lookup info without having to leave my IDE.

I will admit that at $349 (as I write this), it’s not the cheapest of the PowerShell IDE’s on the market. However I’m a firm believer in “you get what you pay for”. This price is low enough that it should be a no brainer for any sized company, from a huge multinational corporation to a single person consultant. By taking advantage of the features in SAPIEN’s PowerShell Studio 2012 you’ll recap that back in a very short time.

Without a doubt this is the most feature rich PowerShell IDE I’ve seen. It seems to have everything but the kitchen sink. And I wouldn’t be surprised if that’s in there too and I just haven’t found it yet.

PowerShell v3 Auto Loading of Modules

Modules are the main way to extend functionality in PowerShell. In v2, if you wanted to access the functionality of a module you had to explicitly load it, using the Import-Module functionality. Starting with v3, if PowerShell encounters a command it doesn’t recognize it will go through the list of valid modules looking for that command, and if found load it automatically. Let’s take a look at an example.

First, let’s see what modules are already loaded.

    Get-Module                      # Show modules loaded
ModuleType Name                                ExportedCommands                                                                                            
---------- ----                                ----------------                                                                                            
Manifest   Microsoft.PowerShell.Management     {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}                                          
Manifest   Microsoft.PowerShell.Utility        {Add-Member, Add-Type, Clear-Variable, Compare-Object...}                                                   

Next, let’s see what’s available.

    Get-Module -ListAvailable       # Show what's available to load
    Directory: C:\Users\rcain\Documents\WindowsPowerShell\Modules


ModuleType Name                                ExportedCommands                     
---------- ----                                ----------------                     
Script     adoLib                              {New-Connection, new-sqlcommand, i...
Script     Agent                               {Get-SqlConnection, Get-SqlServer,...
Script     ISECreamBasic                       {Add-IseMenu, Remove-IseMenu}        
Script     mySQLLib                            {New-MySQLConnection, new-MySqlCom...
Script     OracleClient                        {new-oracle_connection, invoke-ora...
Script     OracleIse                           {Connect-Oracle, Disconnect-Oracle...
Script     PBM                                 {Get-PolicyStore, Get-TargetServer...
Script     PerfCounters                        {Invoke-Sqlcmd2, Get-ProcessPerfco...
Script     Repl                                {Get-SqlConnection, Get-ReplServer...
Script     ShowMbrs                            {New-ShowMbrs, Set-ShowMbrs, Get-G...
Script     SQLIse                              {Test-SqlScript, Out-SqlScript, In...
Script     SQLMaint                            {Invoke-DBMaint, Get-SqlConnection...
Binary     SQLParser                           {Test-SqlScript, Out-SqlScript}      
Script     SQLProfiler                         {Invoke-Sqlcmd2, Save-InfoToSQLTab...
Script     SQLPSX                                                                   
Script     SQLServer                           {Get-SqlConnection, Get-SqlServer,...
Script     SSIS                                {New-ISApplication, Copy-ISItemSQL...
Script     WPK                                 {Add-CodeGenerationRule, ConvertFr...


    Directory: C:\Windows\system32\WindowsPowerShell\v1.0\Modules


ModuleType Name                                ExportedCommands                     
---------- ----                                ----------------                     
Manifest   AppLocker                           {Set-AppLockerPolicy, Get-AppLocke...
Manifest   BitsTransfer                        {Add-BitsFile, Remove-BitsTransfer...
Manifest   CimCmdlets                          {Get-CimAssociatedInstance, Get-Ci...
Manifest   Microsoft.PowerShell.Diagnostics    {Get-WinEvent, Get-Counter, Import...
Manifest   Microsoft.PowerShell.Host           {Start-Transcript, Stop-Transcript}  
Manifest   Microsoft.PowerShell.Management     {Add-Content, Clear-Content, Clear...
Manifest   Microsoft.PowerShell.Security       {Get-Acl, Set-Acl, Get-PfxCertific...
Manifest   Microsoft.PowerShell.Utility        {Format-List, Format-Custom, Forma...
Manifest   Microsoft.WSMan.Management          {Disable-WSManCredSSP, Enable-WSMa...
Manifest   PSDiagnostics                       {Disable-PSTrace, Disable-PSWSManC...
Binary     PSScheduledJob                      {New-JobTrigger, Add-JobTrigger, R...
Manifest   PSWorkflow                          New-PSWorkflowExecutionOption        
Manifest   TroubleshootingPack                 {Get-TroubleshootingPack, Invoke-T...

The CIM cmdlets are a new set that ships with v3. Looking at the output above, you’ll note the CIM module (CimCmdlets, highlighted in second set of modules) is not loaded. Let’s run one of the cmdlets from the module.

    # Note the CimCmdlets module isn't loaded. 
    # Now run a command from that module
    Get-CimInstance win32_bios
SMBIOSBIOSVersion : 8DET50WW (1.20 )
Manufacturer      : LENOVO
Name              : Default System BIOS
SerialNumber      : XXXXXXXX
Version           : LENOVO - 1200

Now let’s run Get-Module again, and you’ll see that the module has now been loaded. Pretty slick.

    # Run again to show CimCmdlets is now loaded
    Get-Module                      
ModuleType Name                                ExportedCommands                     
---------- ----                                ----------------                     
Binary     CimCmdlets                          {Get-CimAssociatedInstance, Get-Ci...
Manifest   Microsoft.PowerShell.Management     {Add-Computer, Add-Content, Checkp...
Manifest   Microsoft.PowerShell.Utility        {Add-Member, Add-Type, Clear-Varia...

Of course this does have some drawbacks. First, it will take time for PowerShell to search through all the paths looking for the commands. Second, if the requested module isn’t in one of the predefined path, it will still fail. Finally, just for the sake of self-documentation it’s better to know what modules your script depends on. For that reason when writing scripts or modules of my own I will continue to explicitly use Import-Module, and suggest you do too.

However, when working in interactive mode, i.e. running a PowerShell console and entering commands, auto loading can be a huge timesaver. No longer do I have to take time to issue an Import-Module cmdlet before I can use the commands I need.

Warning: This post was written using PowerShell v3 BETA. Changes between now and the final release may affect the validity of this post.

Follow

Get every new post delivered to your Inbox.

Join 93 other followers