Using New-Object -ArgumentList when the constructor takes one parameter that is an array

PowerShell is a great scripting language. One of its greatest features is that you have the full power of the .NET framework at your disposal when you use it. However, PowerShell is a dynamically language, and the .NET CLR was designed for static languages. Because of this, sometimes when you call CLR assemblies in Powershell, the types do not get resolved correctly.

I ran into this recently while calling New-Object to invoke the MemoryStream constructor that takes an array of bytes as a parameter. PowerShell did not automatically turn byte[] into object[] {byte[]}. Instead I got an error.

Windows PowerShell New-Object MemoryStream error

The same error appeared if I tried New-Object System.IO.MemoryStream @($bytes).

I initially solved my problem the hard way. I created a List<byte>, add the the array to the list and called List.ToArray(). Eventually, I figured out how to do it correctly thanks to this MSDN PowerShell Tip of the Week. To make an array of one item you precede it with a comma (I tried placing the comma afterwards during my initial experiments).  Since the argument list is not the first parameter of New-Object, I needed to wrap it in parenthesis. So I ended up with New-Object System.IO.MemoryStream (,$bytes) which worked as illustrated below.

Windows PowerShell New-Object MemoryStream success

How many ways can we ‘script’ a stored proc or UDF from the command line?

Update: Added link to the scripts on github.

If you’ve worked with SQL Server for any length of time, you’ve probably written some stored procedures and user defined functions. If you’re like me, you’ve written many. Usually, when you edit a stored proc or UDF. you do it from Visual Studio or Sql Server Management Studio (SSMS). However, what if you want to script the sproc or udf from the command line? Well, there are quite a few ways to skin this cat, and I’ll go through a few.

Using sqlcmd

The simplest way to do this is the command line client that ships with SQL server 2005 and up, sqlcmd, and a system stored proc called sp_helptext. For our example lets have sp_helptext script itself.

sqlcmd -S .sqlexpress2k8r2  -d master -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext 'sp_help'" -o sp_helptext.sql

Lets breakdown the arguments:

  • -S .sqlexpress2k8 this is the named instance of sql server I am connecting to. If you want to connect to the local default instance, you can omit this
  • -d master This is the database we want to connect to. This can be omitted if you want to script an object from the users default catalog.
  • -h-1 -s”” -W -w 1024 These format the output of sqlcmd in a way that makes sense for this task. If you want to understand these options, look at the sqlcmd msdn page, or this stackoverflow question.
  • -Q “EXEC sp_helptext ‘sp_helptext'” -Q means “execute this T-SQL and exit.
  • -o sp_helptext.sql This writes the command output to the text file sp_helptext.sql

Using a batch file

Now we could easily wrap this in a batch file as I demonstrate here:

C:\Users\zippy\Documents\deleteme>type scriptProcOrUdf.cmd
@echo off
sqlcmd -S %1  -d %2 -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext '%3'" -o "%3.sql"<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 13px; line-height: 19px; white-space: normal;">
</span>
So we saved ourselves a few keystrokes with a 2 line batch file.

Using Powershell

Batch files work, but they’re so 2002. Powershell is the CLI of choice these days on windows. So lets see how we would do this in powershell:

So apparently it took me 26 lines of Powershell (if you remove the BSD License) to accomplish what I could have done in a 2 line batch file. Whats the point you might be asking? Well, honestly if you just want the quickest way to script stored procedures to a file from the command line, I’d use the batch file. However, if you wanted to make changes to this script, for example, make it script all stored procedures and udfs in the database. You’d have an easier job with Powershell. You could turn the body of the script into a Poweshell function that takes a SqlConnection object and object name as a parameter. You could then get a list of all the stored procedures and functions with the T-SQL statement SELECT [ROUTINE_NAME], [ROUTINE_TYPE], [ROUTINE_DEFINITION] FROM [INFORMATION_SCHEMA].[ROUTINES]. I’ll leave writing that script as an exercise to the reader.

Using Powershell and “Original Thinking” to do it in less than 26 LOC

In the fictional Star Trek universe, Denny Crane, James T. Kirk cheated on the Kobayashi Maru by reprogramming the no-win scenario to allow him to win. He was awarded for his original thinking. Our scenario is less dire. However, like Kirk, we can change the rules. I chose to use a the open source .NET assemble Atlantis.SchemaEngine.dll. This dll is used for synchronizing schema between two SQL server databases. We don’t want to synchronize or compare anything. However, the library synchronizes database schema’s by generating SQL scripts. Therefore, it can generate DDL for any script-able object in a database. So lets take a look at our leaner, meaner script.

So we went from 26 to 15 lines. That’s a savings of 42%. Some of those are pretty long lines since we have to fully qualify our namespaces in Powershell. However, its a net simplification compared to making the ADO.NET calls directly.

Conclusion

I showed you 4 ways to script a stored proc. I also left plenty of room for you the reader to explore on your own. The code for ScriptProc.ps1 and Script-Object.ps1 lives in the justaprogrammer powershell github repo. If you have any improvements, feel free to leave a comment or pingback below. Also, pull requests are always welcome. Happy Scripting!

Cleaning up your path in PowerShell, and adding stuff to it.

A while back, I demonstrated some PowerShell one-liner-fu for path management. I also pointed out that my machine had duplicate entries in its path. Most people would not care about this at all. Most of the few people that do care would cleanup their path by hand. However, there are a mentally deranged few who realize the world needs a PowerShell script to clean up our paths for us. Luckily for you, I am that kind of crazy. However, I don’t stop there. I also show you how to search the registry to add items to your path.

Warning: I don’t know much about PowerShell and I’ve not tested this script nearly enough. This thing messes with your system path, and things can go really bad if you run it and it messes up your path. Be very careful running it. If you run it your the fool who follows a fool, which makes you Han Solo, not Ben Kenobi. However, your shooting your %PATH% not Greedo.

The script makes use of multi dimensional arrays and foreach loops. I’d be lying if i said that it was efficiently written or bug free. However, it works and does a few clever things with PowerShell.

So with that out of the way, the script is located in a github repository in our newly created github organization I mentioned before. The script is called Path-Fixer.ps1. It is available under the MIT license. I hope to improve it as time goes on. If you fork it and improve the script, please send us a pull request.

Notes about this script

Your path is a combination of two Environment variables. Those are the machine %PATH% and the user %PATH%. My script only concerns itself with the machine level path. Therefore I don’t use $ENV:PATH, like I do in my one-liner example to get the original path. Instead I use [Environment]::GetEnvironmentVariable(‘PATH’, ‘machine’).

Another thing I do is trim off trailing slashes at the end of the path. This means C:\windows\ becomes C:\windows. This may seem like excess, but it also allows me to remove duplicates that only differ because one has a trailing path, and one lacks it.

Two other caveats exist with regard to comparison. One is differences in case. Luckily, String.Replace() is case insensitive by default so my script handles it. The other is environment variable expansion. On windows if you stick %SYSTEMROOT%\system32 in your path, the OS will expand that to c:\windows\system32. My script does not handle this at the moment. Hopefully it will in the future.

I have some very simple registry detection to detect if a few programs are installed. This will probably remain a hard coded list of programs, and registry entries that determine the installation path. I’m hoping to also add detection through windows services. This would be good for something like mongodb that lacks an installer, but can self install as a windows service.

Finally, I’d like to point out that the script is UTF-16 encoded. This is simply because I wrote it script in PowerShell ISE, which decided to save it in UTF-16 format. Rather than change it to UTF-8 so git could easily diff it, I decided to fix git. This stackoverflow question provided the guidance I needed.

Conclusion

Once again, I’d like to reiterate that you must be careful when using this script. There might be some serious bugs, and messing with your path can lead to bad things happening. Also, while I will probably blog about updates to the script, the fastest way to keep track of its changes is to look at the github commit notes, and the source code itself. Finally, if you find a bug in my script or make and improvement, patches will be accepted, and due credit given.

Two Powershell One-Liners: Appending to my %PATH% and Whats in my %PATH%

To say I’ve fallen in love with PowerShell is an understatement. PowerShell is what perl would be; if perl was object based instead of stream based, and lacked all the “culture” of perl. I use PowerShell for a lot of things lately. Recently, I’ve been using it to manage my path, thanks to this PowerShell tip of the week. This has inspired me to share two one-liners related to path management.

The first allows you to add an item to your path. Few things annoy me more than windows programs that don’t include installers, and few installers annoy me more than those that install console programs and don’t offer to update your system path. Correcting this used to be a matter of going into nested levels of dialog windows. However, I can now do it from the PowerShell console. The command to append to your path is as follows:

[Environment]::SetEnvironmentVariable("PATH", [Environment]::GetEnvironmentVariable("PATH", 'Machine') + ';c:Program Filefoobin',"Machine")

A brief dissection:

  • [Environment]::SetEnvironmentVariable calls the static .NET method Environment.SetEnvironmentVariable()
  • “PATH” is the variable we are setting
  • $ENV: is a powershell drive for environment variables.
  • $ENV:PATH + ‘;c:Program Filefoobin’ concats your current path with a string.
  • “Machine” makes this environment variable apply to the entire machine, as opposed to the current user.

So there you have it, instead of digging three levels deep into menus to edit your path, you simply dig up this blog article and copy and paste that one line into PowerShell 🙂

The next one-liner is to determine what’s in your path. Simply typing $Env:PATH will print out your path in the same manner as the cmd.exe command path does. However, you probably want something more human readable. How about if each folder on your path was on a new line and the paths were sorted? Well thats quite easy:

$ENV:Path.Replace('"', '').Split(';') | Sort-Object -unique

On one of my machines this outputs:

%SystemRoot%system32WindowsPowerShellv1.0
C:\Program Files\Common Files\DivX Shared
C:\Program Files\Common Files\Microsoft Shared\Windows Live
C:\Program Files\Cppcheck
C:\Program Files\Gitcmd
C:\Program Files\GNU\GnuPG\pub
C:\Program Files\GnuWin\32\bin
C:\Program Files\Javajdk1.6.0_18bin
C:\Program Files\KDiff3
C:\Program Files\Microsoft SQL Server100\DTS\Binn
C:\Program Files\Microsoft SQL Server100\Tools\Binn
C:\Program Files\Microsoft SQL Server100\Tools\Binn\VSShellCommon7\IDE
C:\Program Files\Microsoft SQL Server80\Tools\Binn
C:\Program Files\Microsoft SQL Server90\DTS\Binn
C:\Program Files\Microsoft SQL Server90\Tools\binn
C:\Program Files\mongodb-win32-i386-1.6.1\bin
C:\Program Files\mtasc-1.14
C:\Program Files\Subversion\bin
C:\Program Files\TortoiseGit\bin
C:\Program Files\TortoiseHg
C:\Program Files\TortoiseSVN\bin
C:\Program Files\Vim\vim72
C:\Program Files\WinMerge
C:\Program Files\Zend\MySQL51\bin
C:\Program Files\Zend\ZendServer\bin
C:\Program Files\Zend\ZendServer\share\ZendFramework\bin
C:\Python26\Scripts
C:\Windows
C:\Windows\system32
C:\Windows\System32\Wbem
C:\Windows\System32\WindowsPowerShell\v1.0
D:\apps\apache-ant-1.8.0bin
D:\apps\jboss-4.2.2.GAbin

So lets break this down:

  • $ENV:Path is our path as before.
  • .Replace() is string.Replace(). Some of your path folders might be quoted, removing the quotes will cause them to be properly sorted.
  • .Split(‘;’) is String.Split(). We want to turn our path into an array of strings, one folder per string.
  • | Is a pipe. This works like cmd.exe and unix piping, excet its object based, not stream based.
  • Sort-Object -unique does exactly what you think it does. It sorts the folders in your path and removed duplicates. Sometimes paths contain a folder multiple times. Cleaning up the duplicates will be addressed in a future blog post.

This one liner is good to run when you inherit someone else’s workstation, or a server setup by someone else. One limitation it has is it does not expand environment variables. Ideally I’d like it to expand %SystemRoot% to C:Windows. However, Sort-Object is case insensitive by default, like the NTFS file system, so casing issues are not a problem.

Thats all for now folks. Happy scripting!

Impressions of PowerShell: Taking Buck Woody’s EventLog example further.

This is not quite a PowerShell first impressions article. I’ve toyed with PowerShell a few times before previously. Most notably, I toyed with the PowerShell TFS cmdlets that come with Team Foundation Server Power Toys a little under a year ago. However, I never stuck with it long enough to retain any of the syntax. Recently, I discovered a one liner by chance in Buck Woody’s blog. This lead me to do some serious PowerShell tinkering today. I’m not quite a seasoned PowerShell novice, but I believe I am now on my way there.

So here is the script in question:

Get-EventLog System | Where-Object { $_.EntryType -eq “Error” }

It is a simple one liner to get all the errors in your event log. The main work horse is the Get-EventLog cmdlet. I spent a good chunk of time playing with it.  I came up with a few iterations:

  • Get-EventLog -Log System -EntryType ‘Error’ # Skip the step of piping through the event logs
  • Get-EventLog -Log System -EntryType ‘Error’ -After (Get-Date).Date.AddDays(-5) # Errors From the past 5 days
  • Get-EventLog -Log System | Where-Object { $_.EntryType -eq ‘Error’ -and $_.TimeGenerated -gt (Get-Date).Date.AddDays(-5) } # Alternate one liner to get errors from the past 5 days with Where-Object

I tried several other permutations, most of which did nothing. I will make note of one thing here, which I will delve into more in a followup post. That something is the pipe “|” character. It works almost exactly as any windows or unix command line guru would expect. Namely, it “pipes” the output from the program on the left into the input of the program on the right. However, PowerShell is object based, unlike unix, dos and windows shells, which are based on streams of text. Therefore, you can pipe objects as well as strings with PowerShell. When piping to and from cmdlets, you are piping objects. The examples above that use the Where-Object cmdlet makes the implications of this clear.

One thing to note here is speed of return. One would think that the event log is indexed by date, and that I could reduce my query time by only returning recent entries. Searching the whole event log should be expected when using the Where-Object cmdlet,  since all log entries are being queried and piped to another program. However, one would think that the Event Log would be indexed and the Get-EventLog command written in such a way that only a subset of the log entries would have to be traversed when you specified the -After parameter. However, when you run both examples, the command “hangs” for a bit between the last row output and the command prompt being displayed.

In my next article we will throw grep and less into the mix, and see what happens when we mix object piping with text piping.

Further Reading

Links that will be useful to play around with this stuff.