The miscellaneous ramblings and thoughts of Dan G. Switzer, II

Search for SQL in Microsoft SQL Server Management Studio with Red Gate's SQL Search

If you do any amount of SQL Server development, at some point in time you've come across a situation where you need to search your database for all occurrences of a table, column or some specific SQL statement that might be used in a view, stored procedure, trigger etc. In the past I've ended up just dump the db schema to a SQL file and searched the file manually, but that's pretty slow and not very elegant.

This morning though I found a better way using Red Gate's SQL Search, which is a free add-in for Microsoft SQL Server Management Studio. This add-in (which is currently free, but will eventually be integrated into the commercial SQL Prompt add-in) allows you to quickly search for strings within your database schema. What's really nice is double-clicking on a matching object takes you right to the object in the Object Explorer—which can save you a lot of time when you have a really large database (I work in an application w/several hundred tables!)

If you use SQL Server Management Studio, I highly recommend you download SQL Search and try it out.

If you're using Microsoft SQL Server Management Studio Express version, or you're "The Add-in 'SQL Search 1.0' failed to load or caused an exception. (Error number: 80070002)" when trying to start Management Studio after installing the add-in, you'll need the extensibility.dll installed before the add-in will work. You'll need to un-installing SQL Search, then download and install extensibility.dll and then re-install SQL Search.

Problems with BlogCFC and new Live Writer 2011

I haven't done a ton of blogging lately, it's amazing how busy you stay with a 6 month old baby in the house. Quite frankly, I'm enjoying being a father immensely, so that's certainly my highest priority with my free time nowadays. Anyway, I did finally get around to blogging a few things lately that I've run into at work.

After one recent blog entry, Brian Ghidinelli pinged me to tell my RSS feed was messed up. After a little researched I learned the problem was some of the characters (namely the em dash) were not properly escaped into HTML entities. I use Raymond Camden's BlogCFC for my blogging platform and new that at one time the xmlrpc code did work properly—because I had made a lot of bug fixes to get it working with Live Writer. However, my last few blog entries were written with the newer version of Live Writer, so what could have changed?

So I busted out my Charles HTTP Proxy and started debugging and I found the problem is that Live Writer does not send the charset being used when it sends the HTTP request to ColdFusion. Because it doesn't send that it's using UTF-8, ColdFusion ends up not handling the content in the request properly. UTF-8 characters end up coming through as a pattern of characters—for example, the em dash ("—") ends up coming through as —.

Now that I knew what the problem was, I needed to find a way to fix it. My first logical step was to make sure that the xmlrpc.cfm template was setting the page encoding to UTF-8, so I added this to the template:

<cfprocessingdirective pageencoding="utf-8" />

However, that didn't change the behavior at all. After much digging, I found I could use the Java page context object to explicitly set the character encoding for the incoming request using:

<cfset getPageContext().getRequest().getHttpRequest().setCharacterEncoding("UTF-8") />

Thankfully, this does fix the problem. So, if you're having problems getting Live Writer to work with BlogCFC, try changing the first few lines of your xmlrpc.cfm template to:

<cfprocessingdirective pageencoding="utf-8" />

<!---// this is required for Live Writer to ensure that the request is processed as UTF-8 //--->
<cfset responseCharset = getPageContext().getRequest().getHttpRequest().getCharacterEncoding() />
<cfif not structKeyExists(variables, "responseCharset")>
  <cfset getPageContext().getRequest().getHttpRequest().setCharacterEncoding("UTF-8") />

<cfcontent type="text/xml; charset=utf-8">

<cfsetting enablecfoutputonly=true>

NOTE: I use the responseCharset to check to see if an explicit character encoding has been supplied. If the value is null, then no charset was supplied so I override the encoding to UTF-8.

jNotify – A Light-weight Notification System for jQuery

One thing that's essential for any web application to be successful, is to implement a method for providing users with feedback based on their input. People want feedback based on their actions in real time. Users want to know that their actions had the intended consequence and if not, why it failed.

In the golden age of web applications, most applications solved this problem by returning a completely new page to the user any time they took an action on your website. If a user filled out a form on your website, the form's data would be sent to the server and the server would then generate a completely new page that either told the user that their action was successful or would show them the errors that occurred.

As web applications began to advance, more applications began to rely on JavaScript to provide more instantaneous feedback to users. If a user would leave a required field empty, developers would use a JavaScript alert() functions to notify users that they left the field incomplete. Over time, more and more applications have come to rely on JavaScript to provide users with a better user experience. The problem is that while the browsers have made many great strides over the years, we're still left with these very rudimentary UI controls for providing native feedback with users.

The alert(), prompt() and confirm() functions basically haven't changed at all since they were introduced. They offer essentially no control over the presentation and they're completely obtrusive to the user's experience. They all halt execution and force user action before any more processing occurs. While there are certain times this behavior is desired, there are many times when it's less than ideal.

For example, let's say you have you have a page that allows users to update bulk records in real time. On each row there is an "Update" button that allows a user to save updates back to the server. You obviously want to provide users with feedback after each update operation has completed. A quick solution is to use the alert() function when the update is successful.

The problems with this method are:

  • It's obtrusive—every time I display the "success" message to the user, I'm required to interact w/the alert box. This can really slow down the user's workflow.
  • No design control—I have no control over how the alert() dialog looks to the user. There's no difference between a successful message and an error message.
  • Only one message at at a time—I can never display multiple alerts to the user at the same time.
  • The alert() halts all activity—including any background tasks.

What we need is a better notification system that's just as easy to use, but allows us greater control over how the messages are display. This is where the jNotify Plug-in comes in. At just about 3KB, it's a very light-weight solution that gives us lots of flexibility over how we display notifications to the user. It allows us:

  • To display multiple messages to the user at one time
  • Use an HTML in the notification
  • Specify whether a notification is "sticky" or not (sticky messages require the user's interaction to close them)
  • Displays notifications in an unobtrusive manor—messages appear on the screen for short time, then fade away
  • Complete control over the positioning and appearance of the notification

I put together a short little video that illustrates some of the typical UI problems that the use of the alert() function introduces and show how jNotify can be used to solve those problems.

The jNotify Plug-in for jQuery

Implementing jNotify on your site is extremely easy. Just include the jquery.jnotify.js and jquery.notify.css files on your page, then substitute your use of alert() with $.jnotify().

So, instead of:
alert("An e-mail notification has been sent!");

You'd use:
$.jnotify("An e-mail notification has been sent!");

We've been using this plug-in in our own applications for over a year and we've had great success with it—especially when used with conjunction of providing feedback to users when AJAX operations complete.

You can see a live example on the jNotify jQuery Plug-in page.