A Note About cliconfg.exe on x64 Machines

If you don’t know (and that’s a good thing in this instance), cliconfg.exe is one of those older-than-dirt parts of Windows that is still around because it serves a specific purpose. In this case, it is used to configure SQL Server connection aliases.

Aliases are basically an abstraction between a SQL Server Instance’s actual connection string information (DNS name, Instance name, port, etc) and the name used by a client to connect to it. In this case, “client” can be either code/an application or a user. I know aliases are mostly evil, however some people do use them extensively. I don’t know that they’re all bad, but that’s a different post for a different time.

x86 cliconfg.exe vs x64 cliconfg.exe

Sit down at any remotely-modern Windows machine (it’s been there since 2000) and you can bring up a Run box, type “cliconfg”, press ENTER, and the utility will launch. If you do this on a 64-bit build of Windows, the 64-bit version of the utility will launch, as you might expect. This sounds good at first, but it might not be.

The first problem this presents is there is absolutely zero way to tell from within the utility itself which version of it is running. Therefore, if you’ve just moved from a 32 to 64-bit machine, there isn’t anything that might get your attention and suggest that anything unexpected is going on. This has probably happened a long time ago for some, but if you’re a corporate monkey like I am, you might have only recently been able to make the jump to a 64-bit OS.

The ultimate issue that crops up stems from why there are two versions of cliconfg.exe in the first place. In short, Windows keeps separate lists of aliases, one used by 32-bit applications and the other by 64-bit. These lists are maintained by their respective versions of cliconfg.exe. These lists are completely independent, and are only accessible by applications compiled with the same bit width.

In order to launch the 32-bit version of cliconfg, it has to be run from the folder it’s in. The path to this file is %SystemDrive%\Windows\sysWOW64\cliconfg.exe. As mentioned, it looks (and acts) just like the 64-bit version. I’d think that the folder should be named “sysWOW32” instead, but what do I know?

Which one to use?

Setting up aliases needs to be slightly planned out, although the same aliases can just be set up on both “sides”, and call it done. If one chooses not to do that, it’s a matter of thinking about what apps are going to
be utilizing any given alias, and then setting that alias up on the same bit width as the app. For example, to use an alias from within SQL Server Management Studio, the alias needs to be set up on the 32-bit side, as SSMS is still a 32-bit application.

Plan B (or Plan A?)

Here’s a different idea: Don’t use cliconfg.exe!

It’s old. I have a feeling it will go away some day. It isn’t very clear on what you are doing (32 vs 64-bit). All problems. Instead, consider using the SQL Server Configuration Manager. It’s new(er). It’s part of SQL Server, so it will probably be around for at least a bit. It is also very clear about the difference between 32 and 64-bit aliases.

For the current topic of conversation, that last point is the most important one. On a 64-bit machine, the Config Manager will have two Native Client items in the tree view in the left pane, one for 64-bit and the other for 32. Aliases configured in one do not show up in the other, making it perfectly clear what is going on.

I know that aliases are bad; using cliconfg.exe to manage them is arguably worse. However, if the situation exists that they need to be used, care needs to be taken in 64-bit client environments. The best way to deal with this situation is to use Configuration Manager.

T-SQL Tuesday #21: “This Ugly Hack is Only Temporary”

…Unless “nothing is temporary around here” also applies to your shop. Then…well…good luck with that (I feel your pain).

Wednesday is better than Tuesday anyway; everyone knows that

It’s been a while since I’ve participated in a T-SQL Tuesday; haven’t been writing at all, really, as you can see. Summer apparently does that to me. If you’re reading this, then it means I got it shoehorned in.

T-SQL Tuesday is the brainchild of Adam Machanic (blog | @AdamMachanic) that started, apparently, 21 months ago. Its purpose is for bloggers to get together and cover a particular topic every month. This is good for readers and also is an easy way to get a blog topic, if one has a hard time coming up with them (like me). This month’s topic is hosted by Adam, and has been billed as “revealing your crap to the world.” There will be lots of good stuff to read this month, calling it now.

RI Enforced by aTrigger? Check.

I’m going to preface this one by saying that it never actually saw the light of day. Sometime between Design & Development, the project that it was part of got canned, so the Development instance was the only place this was ever deployed to. But, it was still crap, and it was crap that I came up with, at that, so here we go.

There once was a big ugly “Location” table. This table was way wider than it should have been, mainly due to the fact that “normalization” was a term missing from the historical evolution of the system it was a part of. As you can imagine, this table contained a lot of character columns of varying widths (up to and including TEXT). To make matters worse, in lots of cases, the UI allows free text entry, which makes much of the contents suspect, at best.

A project came along that basically would require normalizing out a couple of columns related to a certain location attribute. For the sake of discussion, this could be just about anything—we’ll use “Preferred Shipping Carrier” as an example.

At the beginning of design, this “PreferredShipCarrier” column is full of shady entries. There are some “UPS”es, “FedEx”es, and “None”s, but there’s also a lot of NULLs (which shouldn’t really happen), “N/A”s, and “UPPS”es, all of which are incorrect for one reason or another. Obviously as part of normalizing this column out, there would need  to be some corresponding data cleanup, but that’s beside the point.

Where things go south is how this was to be implemented. Since the column in the table is VARCHAR, a real Foreign Key relationship can’t be set up to the INT Surrogate Key in the new “PreferredShipper” table. This is the first sign of trouble. Second sign is that we can’t change the UI—it has to stay in its free-text entry state. This leaves the database exposed to bad data, as there can’t be RI to enforce valid entries.

Enter a Trigger. This can be used to, upon an attempted row insert into the Location table, look up the value entered for Location.PreferredShipCarrier, and throw an error if it doesn’t find a matching row. I hated it, but it got the job done.

Auditing with Triggers, too? What is up with you and Triggers?

SQL 2000 was terrible for auditing—it didn’t have all of the new fancy stuff that we have now for this sort of thing. That means, when you’re dealing with 2000, and the business wants an audit trail of who changed what when, your options are/were pretty limited.

At one point, a requirement came along to implement audit trails on some tables. This means I needed to duplicate tables, adding some metadata columns to them (audit timestamp, who dunnit, what the action was, etc), and then put a trigger on each of the source tables. Some of these particular tables were busy, so the triggers & log tables got a lot of action and I hated having to do this to get what was needed.

If the trigger was fired for a DELETE, it would log the row that was deleted. If an UPDATE was happening, it would first check to see if any column contents were actually changing, and if so, log the previous state of the row to the log table. These triggers will grow relative to the width of the table (because they contain three explicit lists of the tables’ columns), so if the tables being audited are wide, the trigger will get pretty long. Additionally, since triggers in SQL Server are set-based operations, running on all rows that are being DELETEd or INSERTed, special care needs to be taken so they can operate when more than one row is operated on. This can make them a bit ticklish to write.

I know this isn’t necessarily crap, as when you’ve got to audit, you’ve got to audit. I don’t like it if for no other reason than the extra clutter it puts in to the DB and just the general idea of triggers all over the place. All manner of things can happen in triggers, and if you are operating in an unfamiliar DB or troubleshooting a goofy problem, things could be going on in there behind the curtain making troubleshooting harder.

In short: Friends don’t let friends mash F5 on CREATE TRIGGER.

Here Be Dragons: New Cell Phone

Cell phones in my family go way back…Pretty much as far back as is possible in the US:

Ameritech Phone

1984 called...

I don’t remember anymore if that was my dad’s first or second phone. I have no idea what the deal with the ammo box is. I do remember that the handset clip would get bolted to that little bracket on the lid & then could leave the box sit in the middle floor of his pickup and the phone would be right there. Yeah. AMPS analog. 3 watt transmitter. Good thing the antenna was on the roof. Those were the early Ameritech days. The phone wouldn’t even get a signal at the house—he had to get up towards Rensselaer (Indiana, look at a map, see the boondocks where I’m from) before he’d be able to use the thing.

‘Course, back then everyone called them “car phones.” It stayed in the car, and at one point was even hardwired to the vehicle so that if he wasn’t in the truck at the time, it would honk the horn if someone rang. That seems almost mind-blowing to me now. Once the whole 3 watt thing started to go away, things got smaller, and they started to come in little nylon bags (“bag phones”) that were more self-contained, and at least had little rubber duck antennas directly attached to them. It was on a BNC connector, so you could plug in the roof antenna if you didn’t like having what was probably still a whole watt radio broadcasting from the general vicinity of your right knee.

Anyway, in the mid-90s, I was assigned a Motorola DPC 650 by my parents when I started to drive (found out that Tammy had a 650 at about the same time I did). That thing was still a beast, and pretty much never left the truck. That phone began my love affair with Motorola flip phones served by our old friend GTE Communications. Through high school I went through a number of StarTAC 7860 and -68s (wore the phone, so broke a lot of antennas and other bad things happened). To this day, I sometimes wish for the simplicity of a StarTAC.

In summer of 2000, while I was in college, I got my own account and number with GTE. That brought the horrid piece of crap that was the T720. I don’t even want to talk about it. Still have the phone though; it’s pretty funny to fire it up and look at it now. It had a one-line display on the outside so you could see who was calling (OOOOO!). I also seem to recall you could install 3rd party applications on it, but I don’t remember much about that. Somewhere in here GTE became Verizon, of course. I can still hear James Earl Jones say, “Welcome to Verizon Wirelesssss…”

Things looked up after that: Motorola V710. This thing is honestly probably my favorite phone of all time. It was solid, its radio was great, and had ridiculously awesome call quality. It had a big display on the inside, a decent-sized one on the outside, and really good battery life. My first one of those met an untimely end when I wound up running over it with my pickup. While it was open. Face down. Did I mention that it was just at the right place that the front right tire of the pickup sat ON the phone for a few hours? The fun thing about that is that the phone still worked (!), and I used it like that for a few weeks before it decided it was done. Yay phone insurance!

The Smartphone Years

Because I was addicted to my job at the time, and liked the idea of getting mail in my pocket, I bought a Samsung i760 in December 2007. That is a Windows Mobile 6.1 device with a slide-out keyboard. I bought that phone because I wanted my phone to be just another Outlook client. I didn’t want to have to fight with some third-party Exchange connector and Blackberries were right out from the get-go. I have a giant rant about RIM and how I cannot comprehend how so many companies rely on their system for mail, but that’s a different story.

This was a big departure for me for a couple reasons: It was the first non-Motorola flip phone that I had ever owned, as was it my first smartphone. I was pretty worried about this at the time. As it turned out, though, everything was great. OK, except for the call quality. This was definitely a smartphone first. The radio wasn’t all that good, either. However, within about 10 minutes of having it home, I was scrolling (with its stylus, hahahaha) through my Exchange mailbox, which I thought was the coolest thing since sliced bread at the time. You know what else I liked about the phone? Windows Mobile 6.1. There, I said it.

Three years later, I jump off a cliff…

After ten years with the same Verizon account, phone number, and I’m quite certain, voicemail message… I switched to the company that carries Ma Bell’s bastardized name and a completely unproven phone OS on a device that doesn’t have a hardware keyboard. This could go terribly.

Yes, I bought a Samsung Focus with Windows Phone 7. Tammy and I both got one (ATT BOGO Black Friday deal). This was brought on by our house (The Osburn Hideaway) being in a bizzaro black hole where there is no Verizon coverage, Tammy and I wanting to combine to a family plan, and, well, ATT having WP7. Because at the end of the day, I’m just that big of a fanboi.

Everyone on Twitter knows that BrentO just loves Windows Phone 7. I do agree with just about everything that he says about it. We’re taking huge gambles that someone at Microsoft has this dev team’s throttle on the floor and all of the shortcomings the device has will be taken care of soon (I’m not going to talk about anything specific, because I have the same grumps that everyone else has, and all of those people are better writers than I am). The rumored sales numbers don’t look all that good so far, but I don’t know that anyone is all that surprised by that. If, after the first major update comes out and possibly another round of hardware, those numbers don’t start to go up…then I’m going to be pretty worried. I don’t expect iPhone-like numbers, as that device changed the freaking world, and it’s the likely-not-to-be-beaten incumbent.

So, that’s my cell phone story. I don’t expect to write about this much more, because like I said above, I’m really not smart enough to come up with anything new here on my own, so you’ll be able to see what I’m going through by everyone else writing about the OS and poo-pooing on its poor adoption rate. I mean, unless something really bad happens and I bail early.

…and if this doesn’t work out and that happens, I’ll… <deep breath>…probably get an iPhone.