My Very First Scripting Guys Article: Creating Pop-ups by Using PowerShell

So this happened about a month ago, but I’ve been out with the flu. Ed Wilson with Microsoft Scripting Guys invited me to write a few articles on WPF, and I excitedly said yes! In the first article of the series, I detail how to Create Pop-ups by Using PowerShell. The example used displayed local hard drive space.

The second article will detail how to use NotifyIcons and BalloonTips to display an alert if the disk capacity dips below 20%.

I’d like to thank Ed Wilson for such an amazing opportunity; I’ve followed the Scripting Guys pretty much since they first appeared, and writing for them is a total honor.

Posted in PowerShell, Windows, WPF

Use base64 for NotifyIcon in PowerShell

In addition to be able to use base64 for Images in WPF-based PowerShell Forms, you can also use base64 for icons as well. Essentially, you take the base64, convert it to a memory stream, draw an image from that stream, then convert the image to an icon. Finally, you assign the $notify.Icon this new variable.

Just the NotifyIcon Part

Easy enough, right? So what if you want to use base64 as both an icon and as an image within the form? Check out the code below.

NotifyIcon + base64 WPF Image

To get this:

Use this (taking note of the comments):

PowerShell + WPF = <3

Posted in PowerShell, WPF

Icon Color Replacement Fun with PowerShell

I like to keep interfaces simple and tend to use icons in my GUI designs. Recently, when making a PowerShell GUI to alert myself to alarm status changes in vCenter, I decided to reuse the same icon over and over for different statuses, but I made them meaningful by dynamically changing the color.

The Goal

My ultimate goal was to have 3 icons that were colored white, yellow and red.

white yellow red

How I did it

The first thing I did was search iconfinder for a free icon, and decided on this one:


Using, I determined that the base color of iconfinder’s server icon was #444444. Now what’s really cool about iconfinder, is they also offer the base64 code. As discussed in my earlier post, base64 can easily be used as icons and images in WPF forms.

So let’s take a look at what the code below executes to accomplish this task

  1. Loads WPF assemblies and sets base64 icon variable
  2. Creates a bitmapimage object to enable streaming of the base64 image
  3. Creates a colormap. Colormap is simple and just contains .OldColor and .NewColor.
  4. Passes colormap to an imageattribute which performs a SetRemapTable()
  5. Graphics.DrawImage draws an image based on these new imageattributes
  6. Saves the icon to variable for later use within the script ($iconwhite, $bmpwhite)
  7. Saves and opens the new icon so that you can confirm it worked.
  8. Do this for all colors within the newcolor array

This is high performance, and the total conversion time is less than 5 ms, which makes this technique efficient enough to use in all of your PowerShell GUI apps, if you’re so inclined :)

Posted in PowerShell, WPF

Display WPF Popups in Lower Right Corner of Screen using PowerShell

I’ve recently changed my mind on this, but will leave the previous post up in case you want to reposition a WPF popup. If you want a big window to popup in the lower-right corner of the screen, use a WPF Window. WPF Popups are chromeless, it’s true, but they are also limiting in how you can move them around, and how they open and close. ToolTips and BalloonTips are useful, too, but have a character limit and are limiting in other ways.

Here is a super basic lower-right hand corner Window that works no matter how many monitors you have, unlike the popup solution below.

And here is what it looks like:


If you want to use the WPF Popup object

If you don’t want to use a WPF Window and you find yourself limited by notifyicon’s BalloonTips functionality, you can use WPF Popups to display information instead.


This picture is probably inaccurate ;)

Considering the nature of popups, placing them in the lower right should be easy, right? Well, Microsoft’s guide for popup placement was confusing to me, and StackExchange’s top-rated responses weren’t much more straight forward.

So after about twenty minutes of trial and error, I figured out that in order to create a popup in the lower right hand side of the screen, three things are required. First, the Popup’s Placement property must be set to AbsolutePoint. Then, the screen’s working area has to be determined and its values used to update the popup’s VerticalOffset and HorizontalOffset. This is similar to winform’s location property.

The following code will create a popup in the lower right hand corner if you have a single monitor. I don’t have a consistent solution for multiple monitors and Popups. Note that it will not create a notifyicon. That will be covered in my next post.

And here is what it looks like:


This code will work for any sized popup. Also, this form’s Window and Popup objects were stripped down to only the required properties. If you remove any of them, it won’t work as demonstrated.

I’m not a GUI pro, but my understanding of this is that an invisible form hosts a popup. You can’t see the form, and only the popup is visible. While StaysOpen=”False” usually allows the popup to close on LostFocus, it doesn’t work that way in this form. If StaysOpen is left out, the popup will never respond to the $form’s MouseDoubleClick event. You’ll just hear a ding when clicking the popup to close it. I’m assuming that’s Windows’ way of saying the action is forbidden.

Posted in PowerShell, WPF

Use base64 for Images in WPF-based PowerShell Forms

I’m currently building a notification module to let me know when VMware vCenter alerts go off in my home lab. I plan to share it, and wanted to use a non-standard icon, but didn’t want to require a separate download. I knew base64 would be the answer, and ultimately, some C# code from StackExchange helped me figure out what needed to be done.

Here’s what it looks like in my application. Note the image being used in both the popup and the notifyicon itself:


Below is the simplified code I used to display the icon within my application popup. The code contains comments that explains each step. It’s pretty straightforward: a quick conversion, then setting the image source to the converted stream.

Working with PowerShell and WPF makes me realize that *this* is what I always wanted coding to be. Simplified yet powerful!

Posted in PowerShell, WPF

Import-CsvToSql: Super Fast CSV to SQL Server Import PowerShell Module

A while back, I wrote about High-Performance Techniques for Importing CSV to SQL Server using PowerShell. Earlier today, I released a PowerShell module, CsvSqlImport.psm1, which puts that post into practice.

Using this module, it’s possible to import over 90,000 records a second in optimized environments. In addition to being speedy, it’s also straightforward to use and can be easily scheduled for ongoing imports.

Turbo charged

Check out how quick 1,000,000 rows can be imported when the -Turbo switch is used:


Why isn’t Turbo used by default? Well there’s very little error management (that slows things down) so it may error out when other methods (default and -Safe) may not.

Why not just use the Import/Export Wizard?

Because it stopped being easy for me awhile back (and it’s purely GUI, and requires additional software and it can’t import multiple files at once, and so on.)

Remember when importing CSVs into SQL Server using dtswizard didn’t require any conversions or fanciness? You’d just select your file and your delimiter, it would create the table for you using varchar(255) across the board, and plink! You’d almost always get the “Successfully copied 1 table(s) from Flat File to Microsoft SQL Server.”


Then, the Import Wizard became more powerful but also more complicated, and quick and dirty imports suddenly took more work. For instance, whether I allow the Wizard to create a new table, or try to map to an existing one, my imports in SQL Server 2005 and above nearly always go like this:


Noooo! What is all this mapping? I just want it to work then I can mess with my datatypes later. *Next*


“Well, damn. I should just write a PowerShell function to do this for me.” And this is basically how Import-CsvToSql came about.


Eventually, I’ll have support for a GUI, too. I created one, but got too caught up in best practices, and decided to put it off while until I have time to learn more about WPF (which I love, btw.)

Like many PowerShell functions, Import-CsvtoSql supports the -First parameter. As I mentioned earlier, it can even import multiple files at once, so long as they’re similarly formatted. It also allows you to query the CSV file for subsets of data (think select address, cost from csv where State = ‘Louisiana’), and it supports all the options within SqlBulkCopy like Table Lock, Check Constraints, Fire Triggers, Keep Identity, and Keep Nulls.

Most of my imports have been pretty basic, but I did add in features that I thought others would appreciate. If you have any more you’d like me to add, let me know. Oh, also, I did my best to comment the hell out of the code, so if you’re wondering how it works, it should be easy to figure out.

Give the script a try and let me know how you like it. You only need PowerShell 3.0 and above. No additional software is required. If you run into any issues, send a portion of your CSV file to me at [email protected], and I’ll take a look to see what’s going on.

Posted in PowerShell, SQL Server

Reset-SqlSaPassword: Easily regain sa/sysadmin access to your SQL Servers

It’s obvious that I love PowerShell and SQL Server. Together, they allow SQL Server DBA’s to solve just about any problem. Most of my projects have come from questions posted in Reddit’s SQL Server subreddit, including my most recent project, Reset-SqlSaPassword, which allows you to regain syadmin access to your SQL Servers, most times, in less than 20 seconds.

The “sa” in this module name is more about sysadmin access, and not the actual SQL Server login “sa”. Using this module, you can easily regain access to local or remote SQL Servers by either resetting the sa password, adding sysadmin role to existing login, or adding a new login (SQL or Windows) and granting the login sysadmin privileges.


“That seems totally insecure”, you may think. But this script will not work unless you have Windows administrator access on the server or workstation running the SQL Server instance. I’m actually running the script as a Domain Admin in the demo video, though it will also work with local admin privileges.

Initially, when I started the project, I was modifying the Startup Parameters using Microsoft.SqlServer.SqlWmiManagement but I found a huge bug that meant my script would only work using Microsoft.SqlServer.SMO using Version= If at all possible, I’d like my scripts to work across all environments, and this was a show stopper. Then I considered modifying the service itself (failed miserably) and even the registry. Then I realized I could do this all safely from the command-line, which meant the script would have no lasting impact since no permanent startup parameters are modified.

If you need to regain access to your SQL Server, just download Reset-SqlSaPassword from Microsoft Script Center and execute. It works on every Windows platform I tested, even Win XP (laugh, but sadly, some orgs still use it) and Windows Server 2016 Tech Preview. It also works on SQL Server 2005-2016, both clustered and stand-alone instances. It relies heavily on WMI calls and .NET, and does not require SMO or any admin tools. If you’re having access issues caused by unopened ports/strict firewall access, just run the script locally.

If you have any issues, please let me know. You can hit me up on email ([email protected]) or on Twitter (@cl). Or in this post’s comments.

Posted in PowerShell, Security, SQL Server

Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell

Getting the total number of inserted rows for SqlBulkCopy should be easier, but I believe the easiest (and fastest) is by using reflection and some C# code within your PowerShell script. Thanks to user601543 at stackoverflow, I was able translate his code for use within one of my own scripts.


The Code

Here’s an approximation of how I did it. First, I added the code using Add-Type. Then I called it using [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)

As the user noted: “This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON.”

Posted in PowerShell, SQL Server


We are IT pros who grew up living and loving life in Cajun Country.


Chrissy LeMaire
View Chrissy LeMaire, BSc. MCITP's profile on LinkedIn

Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn


Chrissy LeMaire
Microsoft PowerShell MVP