SQL: Adding and populating an order column in an existing table

Found this post in my drafts folder. I don't remember doing this, but I might as well put it out there.

I'm sure I'm going the long way around the tree here, but recently I had to add an OrderId column to a table where I was going to implement ordering that hadn't been there before. In my brain I was sure I could just temporarily change the column to an identity column so that SQL could do the initial ordering for me. I'd then remove the identity from the column and allow my users to change the OrderId. SSIS didn't agree. Maybe it was the lack of sleep or caffeine. So, here's how I ended automating this process. If this was SQL 2005+, I'd be able to use some nifty functions, but in SQL 2000 they don't exist.

UPDATE [mytable] set orderid = rank
FROM (

SELECT t1.RecId, t1.CategoryId, t1.RecName, COUNT(*) AS rank
FROM [mytable] t1, [mytable] t2
where t1.CategoryId = t2.CategoryId and t1.RecName <= t2.RecName
GROUP BY t1.CategoryId, t1.RecId, t1.RecName

)
o where [mytable].RecId = o.RecId

When Shockwave/Flash Breaks in Chrome

Just a tip, if Shockwave/Flash breaks in Chrome, you can restart just that plugin and refresh your pages (no need to restart all instances of Chrome).

When Chrome is in focus, press shift-esc, find the plugin "Shockwave Flash", click on it and press "End Process". Reload your page and you should be good to go.

SMTP Development Solutions

I needed to send mail for a user control I was writing and since the move to Windows 7, I hadn't yet figured out what to do about W7's lack of a SMTP server.

Here are two solutions that I found and I'm kind of on the fence about:

The first is a full blown local SMTP server called, Free SMTP Server and I've used it in the past. Not much to say here. It just works.

The second, smtp4dev,  is interesting in that it doesn't actually send email messages. This is great, for when you are just testing the format of a message and would typically go through multiple revisions that you'd then have to delete. Sure, it's a minor irritation to find those emails peppered among your other daily messages, but another benefit is that smtp4dev sits in your system tray and can be configured to display the "email" as soon as it is received instead of you having to browse to your email client and open the message. My only wish is that there could be a "we'll do it live" switch that would actually send the email for those cases when your email needs to be processed by another system. I don't like the idea of installing two SMTP servers.

Vim Non-Greedy Search

Just a self-post here to help serve as a reminder that the non-greedy search operator for vim is .\{-} instead of the .*? in most languages. This was useful when attempting to replace bad image paths for a template file that contained lots of images that were hosted on various servers and I wanted to consolidate them to get around security warnings. The search command string in vim then ends up as:

:/src=\".\{-}\.\(gif\|png\|jpg\)\"/

This can be combined with a search and replace command as:

:%s/src=\".*\(\/.\{-}\.\(gif\|png\|jpg\)\)\"/src=\"myimagelocation\1\"/gc

Oh, and a super nice thing to know is that you can copy and paste from the cmd line in vim by hitting ctrl-f to open a new cmd window. This is great for saving thost extremely wordy vim regexes that you need to type over and over.

I need to learn Vim

Today I stripped out lines not containing a certain string, removed all text but the codes I cared about from every line, sorted the remaining lines while removing duplicate lines and it only took 3 commands.

In short, I need to learn Vim.

Outlook 2007 Search and RDS Ctrl-Alt-End

I don't know why this is so hard for me to remember, but maybe blogging about it will help.

Searching a date range in Outlook 2007 can be accomplished with the following syntax:
received d:(>=08/01/2008) received:(<=08/31/2008)

Sending a ctrl-alt-delete key combination to the machine you are remoting into is accomplished by pressing ctrl-alt-end.

Quiz me later!

Vista WS4 Bug

I ran into a bug in Vista this weekend. This is really the first major bug I've seen (if you don't count the EXTREME slowness in IE 8b with the Google toolbar loaded) and after hours of troubleshooting I was tempted to go buy another license of XP. The problem was only cropping up when I tried to create a folder, move a file or delete a file. Only. In any of those instances, the Explorer window involved would hang. The file operation would complete, but the prompt or window would stay unusable. If I killed the Explorer process and ran a new instance, then the computer ran just fine until the next rename, move or delete. Then I'd have to kill Explorer and kick off a new process. In short...this really stunk up a weekend where I had several hours of development that needed to be done.

I spent several hours revering to various restore points, but the problem always remained. It wasn't until early this morning that I found a forum post that indicated that Window Search might be the problem. In the case of the forum poster it was an upgrade that solved his problem. For me, I downgraded by uninstalling Windows Search 4. My initial test delete worked like a champ so I went to bed. I really hope that fixed the problem and I can get back to work this evening.

SQL: Replace strings for TEXT datatype

There might be a simpler way to do a replace of all occurences of a string in a SQL TEXT field, but this is the solution I came up with way back when. I needed it again tonight so I figured I might as well pin it to the interwebs.

-- UPDATE [TableToUpdate]
-- SET [Column] = cast(replace(cast([Column] as varchar(max)),'[StringToReplace]','[ReplacementText]') as text)
-- WHERE [Column] like '%[StringToReplace]%'


Google Chrome

Downloading and installing Google's new web browser, Chrome.

http://www.google.com/chrome/

Fast Food Internet

Sitting in McD's doing client work instead of blinding people on the beach with my no-tan tan. Doh!!!

And now the explanation:

Well, long story very short (edited: not really)... I was waiting approval for a change on Friday, but it didn't come until after we were already on the road to OC. So... we were looking for open wireless connections on the way over but the lag on the iPhone's wireless selection screen was a bit too much for the speed I was driving. It was already after midnight by the time we arrived and my client's contract with another company bound them to posting content by midnight on the West coast.

So, I had to buy the wireless that OC provided to the condo and it was absolutely terrible. I actually had to leave the condo and sit on the front steps at 1:30 AM all the while watching my ever-draining battery indicator slip down to below 30%. My connection was so bad that I wasn't able to fully test the work on the production server before the laptop died and I had to crash.

The next morning I got up early and went in search of a better internet connection near an outlet and the waiter from breakfast said that McDonald's internet was the best on the 100+ side of OC. From my experience, I'd have to say he was correct. Also, since my client asked me to post a 20+ MB quicktime file to their homepage and I wasn't able to test the site under so poor conditions that I realized I needed to looking into some sort of streaming host. After a little research I discovered that for $30+ I could use QuickTime Pro to create a reference movie that allows QuickTime players to begin playing without having to download the entire .mov file. That's pretty much the definition of streaming. Another bonus of purchasing QTP was that I could finally resize the movie to make it fit on their home page properly.

My MySQL Experience

I've been doing a bit of development with MySQL for a client and I've had to do a bit of work under the hood since the original developer had made quite a mess of the database. One of the biggest issues/roadblocks I had to deal with was the fact that two database were using two different collations and were therefore not available for table joins. Not only that, but they were duplicating effort of managing two sets of records between the two databases. One table contained an important id value while the other didn't.

I don't have time now, but I will blog the solution that I had to implement in order to configure both databases (and their columns) to use the same collation. Perhaps someone who manages to find that post can direct me towards a better solution in the future as the one I came up with is slightly less than ideal due to the need to manually run each MySQL MODIFY statement. I know that there is a better solution.

GMail UI 2.0 and Firebug

One of my gmail accounts has been upgraded to a newer version of their UI. When the page loaded up I saw a new message like the one below which linked out to a support page:

I think it says something that the Google UI team detected Firebug (a Firefox plugin that is extremely useful for web developers) and made a special effort to notify their web developer users on how to optimize the new GMail experience.

Copying Directory Structure But Not Files

Just filing this under the interesting tidbits that I know I should remember how to do, but always forget how easy it is...

From a Windows command prompt type:

xcopy /T /E "c:\directory to copy" "c:\location to copy to"

If you don't want empty subdirectories, then omit the /E.

If you want to copy all files, the substitute /S for /T and the same rules apply for the /E.

Expresso 3.0

"Softly, Softly Catchee Monkey."
I did a manual check today and discovered that Expresso 3.0 (Regular Expression Development Tool) was released on July 29th, 2007. Why they didn't just add a "Check for Updates" link under the help menu still escapes me...

It's a Safari out there

It's likely not surprising news to you, but a beta Safari browser has been released for Windows. Finally developers can (crosses fingers) test cross-browser functionality for Mac users who use Safari. But before I can get to that point, my OCD (not really, but kinda) forced me to notice that the extra buttons on my trackball optical don't allow me to navigate back and forward in the browser history. That's because the default of ctrl-left arrow and ctrl-right arrow don't work in Safari. Instead you have to application specifically map your mouse to ctrl-[ and ctrl-].

So far I like the Safari browser, but I don't think I've fallen for the trap that Apple laid.

Javascript setInterval() and setTimeout() argument passing

Update: I've modified the post in order to rant about IE not supporting anything after the 2nd argument. Also, I've fixed the example to remind myself of the cross-browser supported syntax.

Just a quick posting to remind myself...

setInterval( function() { fxn(arg[,args]...)} , TimeBetweenIntervals)
setTimeout( function() { fxn(arg[,args]...)} , DelayTimeBeforeTrigger)

If you have a function that doesn't need to reference elements in the DOM (IE it's not a generic function that isn't hardcoded to reference DOM elements or variables) then you can pass in text as a string.

Ex.) myInterval = window.setInterval("displayTime()",1000);

If however, you need to pass variables into an interval or timout that could vary, then use the following:

Ex.) myTimeout = window.setTimeout( function() {displayTime(document.getElementById('clockDiv'))},1000);

Your displayTime() function would accept a DOM element that it would target in order to swap out the time value. This DOM element can then vary between distinct instances of intervals. For instance, you could have a page that is timing different processes and displays the run time for each process in a different div - all of which are using the same displayTime() method to change the HTML.

TinyMCE WYSIWYG Control

Update: And then there's FCKEditor which seems surprisingly like TinyMCE but which has been picked up by Adobe and will be embedded in the next version of ColdFusion. It might not be a bad time to adopt FCKEditor as a standard HTML control if, like me, you'll more than likely still be doing ColdFusion in a few years. Ah, the main difference between the two is that FCKEditor requires that you pay money to license it.

I have a client whose site is built using classic ASP and their content management pieces use an older version of the ActivEdit software. When my client upgraded to IE7, the ActivEdit became extremely unpredictable and I recommended that we switch to a different utility. After 30-45 minutes of research I landed on TinyMCE.

TinyMCE is a platform independent web based Javascript HTML WYSIWYG editor control released as Open Source under LGPL by Moxiecode Systems AB. It has the ability to convert HTML TEXTAREA fields or other HTML elements to editor instances. TinyMCE is very easy to integrate into other Content Management Systems.

[TinyMCE]

If you aren't able to use FreeTextBox for ASP.NET and you don't want to pay for a WYSIWYG control for your PHP, ColdFusion or Classic ASP, then you must check this out.

Dynamically Appended Form Elements in Firefox

Today I spent about 30 minutes trying to figure out why my dynamically appended form elements were no longer being passed to my receiving page in FireFox when they were clearly there in IE. The problem started happening when I implemented an old trick to hide the form padding by nesting it inside of a table tag improperly. It's really simple to modify the padding of a form tag with style sheets, but old (and lame) habits are hard to break.

Of course, I did this before leaving for the night and didn't remember that edit when I resumed my development this morning. However, once I did a little research I quickly cursed myself, implemented the style sheet fix, returned my form tags to their proper (and W3C approved positions) position and shazaam!

Javascript Frameworks

Update: At my temporary day job, I'm told that the standard Javascript framework that they are trying to switch to is JQuery so I'll be taking a look at that as well.

I've been doing a lot of DOM and AJAX work lately and have run into issues with adding event handlers to DOM elements using the same line of code for IE and FireFox. There are several heavily adopted Javascript frameworks in use on the nets that would allow me to scrap writing my own version of these common functions and I've spent a little time trying them out in IE6 and FireFox 1.5 (older browsers...I know).

Here's my initial thoughts on the ones I've tried out:

1. MooTools - Looks nice, but severely lacking in beginner samples. I couldn't even reference the fully loaded MooTools.js (v1.1) file without throwing errors. Needless to say, this did not go over well with me. It's a real shame as I like this tool set over Rico.
2. Rico - Speaking of which... Rico was the first framework I ever saw and it just blew me away. I've never had a chance to implement it and, to be honest, I had initially shied away from it because I wanted to pick something that had a higher user adoption rate. I've still not tried it as I'm told that it is an extremely bloated framework and there are much lighter frameworks (MooTools for instance) that put Rico to shame. That makes sense as, to my knowledge, Rico is essentially an effects package that sits atop the Prototype framework.
3. Prototype - This is, to my knowledge, the (most well known) framework that started it all. It provides all sorts of hooks and utility methods that make common functionality a breeze and it also has the benefit of eliminating the need for knowing the intricacies of cross-browser scripting. Not only that, but it's the first one that I've looked at that actually worked. Granted, the examples I was looking at were still too meaty to be easily digested by a n00b like myself, but I was able to supplement their examples with examples found in the blogosphere. Once again, another reason to make wide user acceptance a criteria for picking your framework.
4. Adobe Spry - This is toted by Adobe as being the framework for designers and I can say by the lack of technical documentation that this would appear to be true. While it would be nice to pick a framework that is backed by a heavy-hitter like Adobe, I just can't get into a framework that doesn't have the documentation to make my learning process that much quicker.
5. Yahoo! YUI - I haven't looked at it as Prototype just started working for me, but it's on my list of things to check out. Perhaps Andrew has taken a peek at it?

MooTools

Update: I take it back. Apparently I missed the part about the DOCTYPE needing to be set to XHTML. Once I did that, MooTools loaded like a champ. I'm rockin' the Fx and Transitions package now to mimic a slide in and fading background color effect after an AJAX request has been sent and the newly inserted content is posted back to the page ala GMail.

Apparently Doug1 thinks he sent me a link to MooTools. I don't recall that being the case. Either way it is a slammin', lightweight DHTML/AJAX library that I fully intend to spend more time looking at. I think I like its examples more than any other library I've seen and now that it's been blogged, I have a better chance of being able to follow up on that intention.