SimMetrics – A Better SoundEx in SQL Server 2005/2008

Tasos Yalanopoulos has a great post about a replacement for the intrinsic SoundEx function, called SimMetrics, implemented as a CLR library that can be loaded into SQL Server.  The library has a number of methods for determining similarity metrics.

Tasos’ post is Beyond SoundEx – Functions for Fuzzy Searching in MS SQL Server, and contains a tutorial of how to load the library into SQL Server, and how to use it in queries.

DotNetKicks Image

Rectangles, Temporary Tables and Table Variables

This week, we’ve been working with SQL DBA Kate Luxemburg, who has 20 years of DBA experience going all the way back to the early days of Ingres.  Kate had a 90-minute braindump yesterday with all sorts of useful tidbits—it’s a great opportunity anytime you can learn from someone with that much experience.  Here are some take-away messages.

Think of DB programming as visualizing rectangles

Application programming and DB programming are two different skills.  All DB results are in the shape of a rectangle, whether it’s one scalar result or a large recordset.  DB programming is strictly about managing rectangles.  The smaller the rectangles you work with, the faster your query will execute.

Restrict, then project

Large SQL statements produce large rectangles, and then filter out data which doesn’t need to be there.  It’s sometimes better to start with three or four small rectangles (restrict), and join them all together at the end of a query to return to the calling command (project).

The very top of your query should define the primary recordset you want, and place that into your intermittant table structure (ITS—either a temp table or table variable).  Use this recordset in subsequent joins and queries to reduce the amount of filtering necessary.

When restricting, note that the WHERE clause is processed before the joins are.  Make smaller rectangles by using the WHERE clause, and the joins will perform better.

Table Variables or Temp Tables?

Think about the area of your rectangles.  Temp variables are great for smaller rectangles, but at some point, your memory will fill up, causing your table variables to be paged to the disk via the OS.  This point is different on every system, so some testing will be involved.  Using temp tables and a properly sized tempdb is more efficient than OS memory paging.

What’s the easiest way to measure the performance of two queries?  Use “set statistics io on”, which provides a great deal of information regarding the amount of IO it takes for a query to complete.  The less IO required, the better your query will perform.  Follow the link for more info on this setting.

Don’t use either unless you have to

In some cases, you can replace an ITS with a select case statement, and doing so will increase performance.

DotNetKicks Image

Pittsburgh MSDN Event: Azure, Debugging and Mobility

Full details and registration at https://msevents.microsoft.com/cui/EventDetail.aspx?culture=en-US&eventid=1032402405&flag=1

Tuesday, March 24, 2009 1:00 PM – Tuesday, March 24, 2009 5:00 PM Eastern Time (US & Canada)
Welcome Time: 12:30 PM

Theater – Loews Waterfront 22

300 Waterfront Drive W.
WEST HOMESTEAD Pennsylvania 15120
United States

Language(s):
English.

Product(s):
Microsoft Visual Studio, Mobility and Web Development.

Audience(s):
Academic/Student Developer, Developer, Developer Generalists, Hobbyist Dev and Professional Developer/Coder.

Event Overview

Enhance your coding capabilities with new tools, tips, and inside secrets from MSDN Events. We’ll start with an overview of the Azure Services Platform, which can help you build cloud-based applications and solutions that offer the best of online and on-premises functionality. Next, you’ll learn how to take full advantage of the Visual Studio debugger. We’ll offer some great tips and tricks to help you debug faster and more efficiently, while applying fresh techniques to ramp up your problem solving abilities. Finally, you’ll see how developing for a Windows Mobile phone leverages your current coding skills and can make it simple to build, deploy and debug cool new devices.

Session 1:  Demystifying Azure – An Overview of the Azure Services Platform for Developers

The Azure™ Services Platform (Azure) is an Internet-scale cloud services platform with an operating system and developer services set that can be used individually or together. Hosted in Microsoft data centers, Azure’s flexible and interoperable platform can help you build new applications to run from the cloud or to enhance existing applications with cloud-based capabilities. With open architecture, Azure gives developers the ability to build Web applications, and applications running on connected devices, PCs, servers, and hybrid solutions that offer the best of online and on-premises functionality. Confused about the Azure hype? Come discover and demystify the Azure concepts, and see demos of Azure in action with a variety of services

Session 2: Deepen your Debugging – Tips and Tricks for the Visual Studio 2008 Debugger
The Visual Studio debugger is a highly underutilized tool for many developers. In this session, you’ll learn how to use it like a pro, while picking up new techniques to fast-forward your problem solving and debugging abilities. We’ll show you how to use advanced breakpoints, advanced watch window / Expression evaluator tricks, modifiers, assertions on the fly, remote debugging, and more. Whether you’re writing C#, VB, WPF, ASP.NET, Windows Forms, or services, we’ll provide tips and tricks that will have you debugging faster and much more efficiently. The debugger is your primary tool for finding bugs, so join us and learn how to make the most of it.

Session 3: Developing for Windows Mobile Devices
Mobile development is growing fast, and Windows Mobile is at the forefront with over 18 million phones shipped last year and many more cutting-edge devices on the way. Visual Studio developers have tremendous opportunities in this space. Why? Developing for a Windows Mobile phone leverages your existing coding experience and takes it to new heights. In this session, we’ll look at some of the coolest new devices, you’ll learn how to set up Visual Studio with the latest SDK and device emulators, and you’ll see how to build, deploy and debug Windows Mobile applications. We’ll also explore how Internet Explorer Mobile 6 provides new AJAX capabilities that offer the richness of the desktop with pan and zoom features tuned for mobile devices.
For more details, please visit www.msdnevents.com.

Registration Options

Event ID:
1032402405

Register by Phone
1-877-673-8638

DotNetKicks Image

Converted from dasBlog to BlogEngine.NET

If you see some weirdness with the blog, like missing images or broken links, let me know.  I’ve switched from dasBlog to BlogEngine.NET.  I started this blog in 2005, and after four years, it was time for a different look and some different software.

The conversion from dasBlog to BlogEngine.NET was pretty easy.  Here’s what I did:

  1. Installed BlogEngine.NET as a website on my local machine, and tweaked the config (blog name, admin user, theme, etc)
  2. Downloaded my dasBlog installation to a temporary folder.  No need to install as a web app.
  3. Converted my blog entries to BlogML with the dasBlog to BlogML converter.  I saved the output XML to the temp folder.  This conversion took a minute or two.
  4. Using the BlogEngine.NET import tool, imported the BlogML output.  This process took seconds.

There was one small problem during conversion—the BlogML originally did not validate (there is a validation check in the import tool).  I tried opening up the BlogML file in XML Notepad 2007, which pointed to some weird character in a specific line of the XML.  I then opened the BlogML in Notepad++, navigated to the line indicated by XML Notepad 2007, and instantly saw the invalid characters.  After I deleted two of something, the BlogML validated and I could import the posts and comments.

DotNetKicks Image

Cheap offsite version control

If you’re any sort of developer, one of the most important tasks you can do is secure your code–not just keeping a good version history, but also keeping the code in a safe place in case your computer fails or house is destroyed.

Most of us have a hosting plan which we use to tinker and host our own personal stuff.  Mine has far more disk space than I’m likely to use, plus the drives are backed up and securely located–just the kind of thing you need for your source code.

There are a number of inexpensive options to maintain your code on your own hosting plan.  One is FTPVC, which I’ve covered before.  FTPVC combines revision control and FTP upload to your hosting plan in one convenient package.

If you want to use a more popular  source control option, such as Subversion/TortoiseSVN, you can.  TortoiseSVN has a local folder mode, which means you can turn ordinary folders into repositories without installing a full Subversion server.  You can map an FTP folder as a local folder with a tool like NetDrive.

There are all sorts of other options available that will accomplish the same thing, and it doesn’t really matter what you use, just so long as you’re securing your code.

Undoing a Windows 7 Dual Boot

If you want your full hard drive back, or are ready to go whole hog like Shaun did, there are two steps to recovering your partition.

1. Remove the Windows 7 partition.  You can do this simply with the free Easus Partition Manager Home, or GParted.  Make sure you have any documents or files you want to save backed up.

2. Edit the bootloader to remove the choice for Windows 7.  Windows XP uses a boot.ini file, but Windows Vista and 7 use a new boot resource called Boot Configuration Data.  If you use Windows XP and dual boot with Vista or 7, the bootloader is upgraded to the new BCD.  There is a command line tool called BCDEdit that you can use to edit the BCD if you’re brave, but there are also GUI editors.  I used the free EasyBCD, and simply deleted the entry for Windows 7.  Although EasyBCD is meant for Vista, it also runs on XP (because that’s what I have).

You now have your space back!

DotNetKicks Image

Setting Up a Windows 7 Dual Boot

A nice, safe way to mess around with the Windows 7 beta is to set up a dual boot system.

1. Create an image of your hard drive.  Just in case.  For this, you’ll need an external drive with enough free space to hold your image (at least the same size as the drive you’re cloning, and a LiveCD of PING – PartImage Is Not Ghost.  Boot into the LiveCD, and start the imaging process.  This can take a couple hours, depending on the size of your drive.  You’ll have a sector-by-sector backup

2. Create a new partition.  You can do this with the free Easeus Partition Manager Home Edition, or the live CD for GParted.  I’ve used both tools without any problems, but be careful on your own.

3. Download the Windows 7 Beta, and get a key, too.  The beta program has been extended until Feb 10.  Burn the ISO to a DVD (it won’t fit on a CD), then run the installer.  You can either boot from the DVD, or start the installer directly from within Windows XP or Vista.  When it comes time to choose where you want to install Windows 7, make sure you choose the new partition, not your current one!

The downloads of PING, GParted and Windows 7 are all ISO images.  If you need a free tool to burn ISOs to CDs or DVDs, I strongly recommend Imgburn.

If you don’t have a DVD burner for Windows 7, you can mount the ISO as a virtual drive using Pismo File Mount, and then running the installer from the virtual drive.  This works because the Windows 7 installer can be run from within Win XP or Vista.  You can’t do this for Easeus, GParted or PING, since you need to boot out of Windows to make the changes.

DotNetKicks Image

Newly Installed SideQuik Document Holder

I installed the coolest thing on my laptop today, and it’s not a software tool.  My laptop is now sporting a SideQuik document holder.  There’s not a tremendous amount that needs to be said to describe a document holder.  It attaches to your laptop monitor and holds papers–pretty simple concept.  But what’s really cool about the SideQuik is its design.  The SideQuik slides both left and right, to hold documents on whichever side is most convenient (handy in tight places), and if you have space, you can set it on the table, too.  It’s very thin, so it doesn’t add a lot of bulk, and still allows my laptop to slide into and out of my case easily.  Click the photo for more images and details (Note: that’s not me in the photo).

 

If clear isn’t your gig, you can also add skins and a little whiteboard insert, too.

If you’ve been around me, you know I’m pretty adamant about not adorning my laptop, so I have to explain just how momentous this is for me to attach anything to it.  I have a Dell Vostro 1500 laptop, and I like its sleek look.  It has a stealthy appearance, in an F117-A kind of way.  I’ve had plenty of opportunity to decorate–Dani tried at Installfest to get a Visual Studio 2008 sticker on it, but even the free VS 2008 license wasn’t enough incentive.  ASP Alliance sent stickers to us authors, and they’ve paid me well through the years.  Paul Nielsen from SQL Server Bible handed out the cool Euro-style SQL stickers at DevTeach (see them on his front page).  As you can see from the photo below, undecorated after over a year.

DSC_8315 (Small)

But that’s changed.  Although the appearance is different, the profile hasn’t been significantly altered.

DSC_8316 (Small)

Now that you’ve seen it, quit being a dork and fumbling papers everywhere.  Go get a SideQuik.

DotNetKicks Image

Compiler Error Message: BC2017: could not find library

One of my web apps uses SQL Server Reporting Services, and began throwing this error the other day:

Compiler Error Message: BC2017: could not find library ‘c:\WINNT\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\…’

The file name in the path refers to a temporary file with a random name.

Last time I saw a similar error message, the permissions on the Temporary ASP.NET Files folder needed to be modified.  I was using impersonation, and the impersonated user needed MODIFY permissions.  It didn’t make sense in this case, since we hadn’t made any changes and weren’t using impersonation.

Just to be sure, we added the ASPNET user with Modify permissions, and the report ran!  As an experiment, we removed the ASPNET user, and the report sill ran!  This made no sense, so I hunted down the network manager to see what MS update may have been installed on my web server.  Instead, they had decommissioned the old primary domain controller, and promoted a newer BDC to become the PDC.  This web server is Windows 2000 (and is to be replaced this year), and our guess is that IIS was using a token cached from the old PDC, which wasn’t validating against the new PDC.

Whatever the case, tweaking the permissions worked.  In other cases, you may need to leave the impersonated user assigned to the folder.  This is usually the case when you try and access the report for the first time.

DotNetKicks Image

Resources for Exporting to Excel in ASP.NET without Excel

If you have to create Excel spreadsheets for your users, don’t use Excel on the server.  You will have problems.  Instead, use one of the alternatives below:

CarlosAg’s ExcelXmlWriter

I have gotten a lot of mileage out of this free library.  This generates Excel 2003 compatible XML Workbooks.  You have complete programmatic control over every attribute of every cell—you can set appearance, formatting, formulas, etc.  If you have a workbook template to start from, Carlos has a code generator tool that will reverse engineer your template into the classes you need.  I have made some very complicated multi page workbooks with this library.  It can take some effort if your workbook is very complicated, but the end results have been great.  Did I mention it’s free?

SpreadsheetML

All of Office 2007 files are natively a new form of XML, which conforms to the OpenXML standard.  Excel’s implementation is called SpreadsheetML.  There is a new set of libraries to create Office 2007 documents called the Open XML SDK.  Download the free SDK and have a look at Brian Jones’ example for creating an Excel workbook with a chart.  Double bonus here—OpenOffice is also natively compatible with OpenXML.

Aspose

Aspose makes commercial libraries for Office automation and so much more.  I have recently acquired their Total package for .NET, so I don’t have a lot of experience with it yet, but it is awesome.  If the free tools don’t get you where you need to go, look here.  Aspose is a supporter of the .NET community, which carries a lot of weight when I make purchasing decisions (in fact, I won this package at DevTeach via the Montreal .NET UG).

Farpoint Spread

Another commercial component.  I don’t have any experience with this library, but they advertise like crazy and support the .NET community, so they are worth checking out.

DotNetKicks Image