SQLPSX 1.6.1 Release

Sep 2, 2009 at 1:53 AM
Edited Sep 2, 2009 at 11:26 AM
I completed a maintenance release of SQL Server PowerShell Extensions, which address all known open issues. This release is still PowerShell V1, but I plan on working on a V2 release soon. The only notable change is in LibraryShowMbrs, which is used to recursively enumerate local and AD groups I know Quest makes a nice set of free cmdlets that provide this funcitonality. The Quest cmdlets are very good, I use them, however I chose to roll my own for two reasons. First, I did not want to build in a dependency for 3rd party cmdlets. Second, I want to be able to use SQLPSX from within the SQL Server 2008 PowerShell host, which does not support additional cmdlets--only scripts like SQLSPX.
The change in LibraryShowMbrs is in the way used to obtain group members. For several years I've used the WMI class Win32_GroupUser. Until recently this had been a reliable WMI class, however for some reason the class simply stopped returning even the name of sub groups and instead only user accounts are returned. I'm not really sure why and I haven't been able to find documentation on this change. I guess not finding documentation is not too suprising since I doubt many folks use WMI to enumerate local and AD groups. The library was re-written to use WinNT Provider and I found this post from Kristopher Bash helpful in creating the code.
I want to thank Jorge Seggara (@sqlchicken on Twitter) for helping to find several of the bugs addressed in this release. One of the issues fixed was a missing assembly that I did not find because I was loading it in my profile. I load a lot of things in my profile including initializing the SQL Server 2008 cmdlets and providers in my regular PowerShell using this script that contained the assembly I needed to include in one of the SQLPSX scripts. Due to this issue, I've learned a couple of things, first I need to test using the -noprofile switch. This will best mimic a clean PowerShell installation and I would encourage all script developers to do the same with scripts they distribute. Second, I need to do a better job testing. As scripts become complex its time to look at a more displined form of testing. One tool I learned about in a recent Episode of the PowerScripting Podcast called PSUnit. I'm going to check it out.
Dec 10, 2009 at 1:07 PM

This is a wonderful way to explore what PowerShell has to offer.  Thank you.

In case it helps others, I would like to mention how I overcame a problem in the SQLPSX Part 2 article.  While I was able to retrieve data from the desired SQL Server instance using Write-SmoToCsvFile.ps1, I kept on getting cryptic errors concerning the ExecuteNonQuery call when trying to upload the csv output into the holding tables that are the source for the associated reports.  I am, by the way, using PowerShell 2 on a Windows 7 box.  I am running the script on this box and trying to save the data to a remote server.

It turns out that if the source files for a Bulk Insert can be stored locally to the SQL Server or else on a remote drive.  But if the latter, then UNC paths must be used to identify the source location.  So in the section of the SQLPSX Part 2 article where Chad says to 'Change the variable $CsvDir ... ' and gives an example using a local drive letter, I had to use a UNC path here.  While at it, I used a subfolder of that for the archive folder.  Then the script to bulk load the csv data worked beautifully.

As a small extra point, although the screenshot of the SQLPSX Report Setup shows what seems to be a VS2005 project being created, I found that the very helpful reports provided in the SQLPSX_1.6.1 distribution should be homed on VS2008 instead - they use the tablix control amongst other things.

With a thousand thanks and many best regards