Just A Programmer

Today I was asked to do something that seemed simple, until I actually had to do it. A coworker had a database with two fields he wanted renamed in a specific way. For our example, lets call them ProductNumber and ProductName. He wanted ProductNumber to be sequential (1, 2, 3 . . .) and the ProductName fields to be called “Product A”, “Product B” . . . “Product Z”, “Product AA” etc. So this suddenly became a non-trivial problem if you had more than 26 rows, which of course I did.

So I rolled up my sleeve, got a fresh cup of coffee, and got to work. Populating ProductNumber was easy enough using a Common Table Expression (CTE) with a ROW_NUMBER(). Then I realized I could think of the English alphabet as symbols for a base 26 number system, with AA following Z and so on. The only problem was I couldn’t express that in a set based way for a clean T-SQL implementation. No problem, I’d just generate the T-SQL to make a giant mapping table in PowerShell!

I am ashamed to admit I had to look up the algorithim for converting from base 10 to another number. I was also surprised to discover that the first result google returned me was this tripod page.

The algorithm is as follows.

1. Start with an empty string which becomes the return value
2. While the value is greater than the base get the remainder of the value divided by the base. Convert that to its letter and prepend that to the return value
3. Repeat step 2 with the quotient of the value over the base.
4. When the quotient is less than the base, prepend that to the string instead.

It seemed simple enough, but there were some headaches.

The first thing I discovered was that when you divide integers in PowerShell, you get a float as a result. Also casting it back to an int rounds instead of truncating the results. I was expecting the opposite in both cases, because that is how C# behaves. I ended up using the unwieldy combination of Math.Floor() and a cast in the form [int][math]::Floor($currVal / 26) to resolve this. The MSDN technet has an article that recommends the more unwieldy [Math]::floor([int] $currVal / [int] 26), but I proved that my terser method gives the same results.

Then I had problems with how to display powers of 26. The way it was supposed to work was that 1 = A, 24 = X, 25 = Y, 26 = Z and 27 = AA. However, depending on how I did it I ended up with 26 = AZ or 27 = BA. I could not account for this edge case, nor compensate for it with special conditions.

Then it dawned on me, A needed to be equal to zero not one. A base 10 system deals with the digits 0-9. Base 2 deals with 0 and 1. Base 16 deals with 0-F and F is 15. Once I rewrote my script to work that way, edge cases disappeared, and things just worked.

The script function Convert-ToLetters ([parameter(Mandatory=$true,ValueFromPipeline=$true)][int] $value) { $currVal = $value; $returnVal = ''; while ($currVal -ge 26) { $returnVal = [char](($currVal) % 26 + 65) + $returnVal; $currVal = [int][math]::Floor($currVal / 26) } $returnVal = [char](($currVal) + 64) + $returnVal; return $returnVal }

If its not clear how I generated upper case letters, the ASCII codes for A through Z are 65 through 90, and casting an integer to a char converts it to its ASCII code. Ergo, the expression [char]65 evaluates to “A”.

So now here’s the function in action:

1 .. 100 | ForEach-Object { $_ | Convert-ToLetters }

Happy Scripting!

It’s an all to familiar story. You  need to reboot a server, and then you need to start a remote desktop connection into it. So what do you do? You open up a command prompt, type ping -t <HOSTNAME> and wait until the server responds to pings. When that happens, you keep trying to connect via remote desktop, until it works. There’s got to be an easier way. You should write a PowerShell script to automate the process. However, its one of those things that not quite annoying enough to get you to actually take action and write the script. Luckily, thanks to the power of twitter, I reached a tipping point this week, and wrote the script. It all started out with some innocent whining:

.ditto155068373691150337{background: #C0DEED url(http://a0.twimg.com/images/themes/theme1/bg.png) no-repeat;padding: 20px;} .ditto155068373691150337 a { color: #0084B4;} p.dittoTweet{background: #fff;padding: 10px 12px 10px 50px;margin: 0;min-height: 48px;color: #000;font-size: 18px !important;line-height: 22px;-moz-border-radius: 5px;-webkit-border-radius: 5px;} p.dittoTweet span.metadata {display: block;width: 100%;clear: both;margin-top: 8px;padding-top: 12px;height: 65px;} p.dittoTweet span.metadata span.author {line-height: 22px;color: #666;font-family: Arial, Helvetica, sans-serif;} .mainlink {font-family: Arial, Helvetica, sans-serif;font-size: 26px;color: #1F98C7;text-decoration: none;} .mainlink: hover {color: #1F98C7;text-decoration: underline;} .tweet {font-size: 24px;} p.dittoTweet span.metadata span.author img {float: left; margin: 0px 7px 0px 0px;} p.dittoTweet a:hover {text-decoration: underline;} p.dittoTweet span.timestamp {font-size: 12px;display: block;color: #999;} p.dittoTweet span.timestamp a {color: #999;text-decoration: none;}

@zippy1981
Justin DearingYou know what I need in an RDP client. I need an “”I just rebooted so ping it for me and autoreconnect”. 2 days ago via web · powered by @socialditto

Then d0tk0m and Yanni Robel retweeted my whining. They say necessity is the mother of invention. In this case the commiseration off two tweeps was the father. So I spent a Saturday with PowerGUI, and came up with a script to solve the problem.

Planing stage

I wanted my script to automate what I already did. From the perspective a system administrator that wants to reboot a server and then remote desktop into it, the following happens.

1. All the processes, including the remote desktop service (termsrv.dll) are shut down. When that happens the remote desktop port (default 3389) no longer has anything listening on it.
2. Eventually the network adapter is shutdown and it will stop responding to ICMP echo requests, or pings.
3. The server will finish shutting down, the BIOS will POST, and Windows will begin booting
4. Eventually the network adapter will come up and start responding to pings.
5. The remote desktop service will start and bind to the remote desktop port.

Therefore, I made my script to do the following.

1. Ping the server until it answered five successive ping requests. Yes this might be naive and optimistic in many cases. However, it worked in my use case. I used the Send() method of the .NET System.Net.NetworkInformation.Ping class to do this.
2. Once I was sure the host was up, I’d try to connect to it on port 3389, or another port if I passed a different port as a parameter. To do this I used the System.Net.Sockets.TcpClient class.
3. After this it was simply a matter of passing the right parameters to the remote desktop client, mstsc.exe. I initially attempted to use the simple & mstsc <Arguments>. However, that didn’t work to well so I ended up resorting to Invoke-Expression.

The script

Below is the current version of the script, hosted on poshcode.org. The current version is stored in gist repository. While you are free to post changes to poshcode.org (or anywhere), in accordance with the license, I’d prefer if you notified me of any changes so that they may be placed in the gist git repo.

Warning folks, this is a non-technical post.

Recently, a post on the official SQL Server blog stated that for each of the first 400 “what #SQLFamily means to me” stories submitted to sqlfamilysubmission@live.com, $50 would be donated to The Pragmatic Works Foundation to train veterans in IT skills. I just submitted my entry, which I have reproduced below.

My name is Justin Dearing, and I have worked in various capacities in the IT industry since December 2002. This is my #SQLFamily story. it is a story of learning my manners and receiving forgiveness.

Paul Randal and Kimberly Tripp recently decided to increase the community marketing of their SQLSkills consulting company. They offered to send anyone a SQLSkills sticker that sent them a mailing address. I decided to sign up for this. I was a bit surprised there was no automated form, and that I had to send this request in prose via email. Note that there is now a web form for this.. Well I did not say please or thank you, or generally acknowledge that a fellow homo sapien would be receiving the request at the end. Well, a very unique and talented homo sapien that I respect very much (Paul Randal himself) did indeed read my response, and sent the following twitter message: Amazing number of people that sign up for our mailing list and ask for sticker without please or thank you at all. Internet = no manners I hung my head in shame for about a week while fretting over what to do because I knew right away I was the offender. Eventually I decided to simply send an email to Paul saying I realized I was the one that offended him and apologizing. He accepted the apology and sent me the sticker. Like a functional family, when forgiveness was sought it was quickly given, the incident was forgotten, and I’d like to think I’ve been a little more polite and considerate both on and off the internet as a result.

On October 29th 2011, I entered an EZ-Pass lane of the Brooklyn Battery Tunnel toll plaza and, to make a long story short, learned my EZ-Pass was broken. On November 19th 2011, I went to the Newark, NJ EZ-Pass Customer Service Center to rectify the matter. I was given another external EZ-Pass tag, and told it would [...]

Recently, a coworker asked me about a problem he was having with a PowerShell script that edited an app.config file. It was a simple enough fix for an experienced PowerShell programmer, but worth sharing the solution for those not as experienced. For this article, I’ll use a very small example web.config that demonstrates the problem. [...]

After I created an account at Cytanium, I quickly discovered the glory of One-Click Publish. After setting up my source control in TFS. All I could think of, was getting TFS to do the same thing for me, but even I knew that wouldn’t be enough. I wanted TFS to deploy my database changes as [...]

The other day, I couldn’t figure out how to Synchronously wait for an Async workflow to complete. I was pretty sure I needed to use Observable.guard, I just couldn’t wrap my head around how to use it. This morning I was on a mission to figure it out. This bit of code produces the most [...]

So I am trying to deal with an API that says I cannot make more than 4 requests per second. This is a really good use for F# since it has such great support for Asynchronous Workflows. I took a little inspiration from this post by Luca Bolognese. RateLimitedAgent Messages are passed in through a [...]

There are many uses for the New-Object cmdlet. One of the more obvious uses is to transform a HashTable to a PSObject, perhaps to be pipelined into Format-Table. That is a convenient output formatting trick because you can then pass it to an Out-* or Format-* cmdlet. However, this trick has uses beyond formatting. For example, lets say you [...]

While trying to integrate YUI Compressor as a build task in my current project… http://yuicompressor.codeplex.com/documentation Post Build Event: I got the error: It seems that the spaces in the folder path are preventing me from enjoying YUI Compressor. Quite the same problem this person is having: MSDN Forums: Unable to correctly pass parameters to MS [...]

Problem 4 Find the largest palindrome that results from the multiplication of two numbers under 1000 I feel like I have a strange obsession with the Seq.unfold function. That function does most of the heavy lifting here. Although the wise Euler pointed out to me that I can avoid duplicate multiplication factors, and keep track [...]

Problem 1 Find the sum of all the multiples of 3 or 5 below 1000. Brute force is the first thing that comes to mind But the wise Euler shows Math to be a better tactic, as his answer is unquestionably faster. Problem 2 Sum of all even Fibonacci numbers under 4 million No real [...]

While job hunting this time around, a recruiter pointed me to a website called Project Euler, as a great place to practice. Project Euler is a series of challenging mathematical/computer programming problems that will require more than just mathematical insights to solve. Although mathematics will help you arrive at elegant and efficient methods, the use [...]

On October 29th 2011, I entered an EZ-Pass lane of the Brooklyn Battery Tunnel toll plaza and, to make a long story short, learned my EZ-Pass was broken. On November 19th 2011, I went to the Newark, NJ EZ-Pass Customer Service Center to rectify the matter. I was given another external EZ-Pass tag, and told it would cost me $33 dollars. I was not asked for payment, and assumed it would come  out of my account. I went out, mounted the new EZ-Pass tag to my front licence plate (with the old screws, because the new screws were one way security screws) and went on my merry way (through several tollbooths). That’s when the real fun began.

My old EZ-Pass would not be detected by a toll lane at all. It had developed a crack, and I assume the circuitry malfunctioned. The new one on the other hand caused the toll booths to say  ”toll unpaid call EZ-Pass.” I assumed at the time it would take a while for the tag to be associated with my account. This assumption was supported by the fact that he woman at the service center wrote the serial number of my tag on a paper form. This form incidently stated it was for accounting of the internal tags only. I assumed that the process of associating tags with accounts was not instantaneous.

Then November 24th came around, and I needed to travel to participate in not one, but two feasts of slaughtered turkeys for an American Holiday called Thanksgiving. My tag failed to work. Obviously something was amiss, and I took action on Black Friday. I logged into my account and only saw the old tag number. So I went to my car, got my new tag number off my tag, and called EZ-Pass. While I was waiting to talk to someone I made a horrible discovery.

So let’s backup here a little, because this is my second horrible EZ-Pass discovery. Before I got my new EZ-Pass tag, I only interacted with EZ-Pass via the EZ-Pass NJ website. To log into the site, you need either your account number or your tag number, and a pin. The pin is an all numeric password. I will come out and make the confession that at the time I made it, its was the same as my ATM pin. That is no longer my ATM pin, because this was years ago.

So I shared passwords across accounts, but that’s not a discovery. I always knew this. The discovery was this. When I was at the customer service center, I was asked for my pin to verify my account. I gave it to the woman, and she did not type it into her computer. She looked at her screen to make sure what I said matched what was displayed. So pins are stored unencrypted. Mind you, an EZ-Pass account contains a lot of personally identifiable information (PII). In addition to credit card numbers and your address, it contains license plate numbers, and places you’ve been. With this discovery, it seems that a lot of low paid clerks, living in the same state as the tag holder can access this information with just a persons name. Remind me never to anger an EZ-Pass employee.

Ok so that’s terrible, but hardly worth writing a blog post about. I mean I’ve seen plenty of data security sins of that caliber, none of which have inspired a written tirade. However, something sent me over the top here.

So what sent me over the top you might ask? Well when I first logged in I used my old tag number, because that’s the number I know and use to log in to my account. However, while waiting on hold, I said to myself, “let me use the new tag number and my pin to log in.” I did this and I saw not my account,  but the account of a complete stranger. I explain the situation to the person on the phone and the outcome is I have to mail in both tags (at my own cost), and a new tag will be sent to me (at which point I will be charged $33). There is no record of my original visit to the EZ-Pass center. I have a feeling if they look hard enough in the other person’s account, they will find it. I’d like to know what transpired while the guy put me on hold.

I will likely have a lot of headaches to sort out once this is all said and done. I regret not writing down the name of the person whose account my second tag was assigned to, or the name of the clerk in Newark or phone operator I dealt with.  If there are any further developments, I’ll report them here.