SQL Server


I’ve never run into the situation where I was worried about a table being accidentally dropped (maybe I should have been?) as I usually let a good security model restrict that.  But, accidents do happen.

So, if I ever need to make sure a table isn’t dropped I can use this technique by Roman Rehak.  He creates a view using the SCHEMABINDING option, which binds the view to the table/schema so that the table (or other object) can’t be dropped without the view being dropped or modified.  Taking a peek at BOL it looks like the same technique can also be used to make sure a table structure is not manipulated, as an ALTER statement will fail on a table that is dependent on a view, if that ALTER statement will affect the view definition.

In a dev environment with multiple developers possibly affecting a DB structure, I can see the ALTER check being more beneficial than the delete.  But, if you get both for the same price…beautiful!


Zipcode/Postalcode Validation

My current client has customers that are located all throughout the world, but the majority of which reside or work in the US, Canada or the UK.  We recently had a situation where a user was inputing a postal code for a Canadian residence and they did not use the expected format, which expects a space following the third character.

As an example, the postal code ‘A9F C3D’ would have been just fine, but ‘A9FC3D’ would fail the validation.

So, a bug was entered and the whole issue of postal code validation was raised.  The first option was to only perform validation on US codes, and just allow free text for any other country.  Not a bad option, since it would allow for some flexibility as new clients came on board who lived or worked in different counties.  But, it leaves the door wide open to get an invalid code and then not be able to reach a client through mailings.  The next option was to perform some validation to make sure the postal code was either a US, UK, or Canadian postal code.  Since that covers the majority of the situations, that was the decision.

There was already a reg ex on the field that did US and Canadian (with the space) validation, so it just needed to be altered a bit to accept UK codes, as well as account for no spaces or even dashes used in a Canadian code.  I did some research on UK codes and discovered there really is no rhyme or reason to them.  They could be anywhere from 6 to 12 characters long depending on the information that the code was conveying.  Wonderful.

Well, a bit more analysis and some time spent on RegexLib.com and I finally got an acceptable pattern that would handle the majority of the cases, if not all.  I’m still not sure if I tested all of the UK possibilities just because there seems to be so much of a, well, cluster-fuck of ways to build a code.  I’ll keep my fingers crossed.  I guess I’m just a stupid American used to being able to validate with a simple d{5}-d{4}|d{5} validation.

For future reference, here’s what I ended up with:
^(d{5}-d{4}|d{5})$|([A-Za-z]d[A-Za-z][s.-]?(|-)d[A-Za-z]d)|[A-Za-z]{1,2}d{1,2}[A-Za-z]? d[A-Za-z]{2}$


On a separate note, but related, I came across a posting by Chris McKenzie where he asks for a free listing of US codes and their associated city/state info.  There have been many times when I’ve desired this info as well, so it’s good to see some options out there.  A couple options mentioned by folks in his comments are here and here, with a 1999 listing here.  Good to know for the next time I need this stuff.


Research Your House

I’m terrible with names.  So much so that if I don’t use it on a daily basis I’ll more than likely forget it within a couple of days.  Such is the case with my neighbors.  I met them when I first moved in, didn’t talk to them for a couple of days, and promptly forgot their names.  So now whenever I talk to the husband, I can’t call him by name.  The only reason I know his wife’s name is because he uses it in the context of our conversations.

Anyway, they have been really nice and, well, neighborly since I moved in.  The most notable event was when I mentioned I was going to build a dog room for the mutt in my basement so she can come in out of the cold whenever she wants.  His response, whatever his name is, was to mention that he had some left over chain-link fencing and I could use it if I thought it would help.  What a great idea!  A quick and easy way to create a dog room, at least temporarily, that will get CJ out of the cold.  So, I said yes and proceeded to bolt the fencing into my unfinished basement wall studs.  Perfect.  He even had a gate!

Looking back at the top of this post I’m reminded of the reason for this post.  I wanted to get my neighbors something for Christmas (or “The Holidays”, to be PC) to thank them for their generosity, but I have no idea what their names are!  Government to the rescue!

A few minutes on the Internet and I found that my city government website has a tool that allows you to enter an address and view a satellite picture of that address.  Perfect for me since I didn’t happen to look to see what my neighbors address was.  Then I went over to my county’s website and found a cool tool that allows you to enter an address (or a range of addresses) and it will show you the history of the house.  Things like previous sales, past permits, various details on the house structure, and…the current owner.  Just what I wanted.

So I set out to kill one bird (get my neighbors names) and ended up killing two birds.  I looked up my address while I was there and learned a bit about my homes history.  Although the info is somewhat limited, it’s more than what I knew before today!

Personal, Politics

What’s the Population of Madagascar?

Beats me.

But I found a really cool site that will tell you.  And the best part is, it’s paid for by our tax dollars.

It’s called The World Factbook and is put together by our friends at the CIA.

Oh, the population is 17,501,871.


Is Your Fourth Grader a Criminal

The whole “school violence” and political correctness of this world is really getting to me.  As a father of two it’s getting close to being frightening.  Not that I’m worried my kids will be the victim of some crazy kid…I’m more worried that they will be the victim of some crazy school administration policy.

Here are the latest examples of school administrations “protecting” our kids:

Philadelphia A fourth grader going to school to get an education ends up being dragged away to the police station in handcuffs.  Did she threaten someone?  Did she beat someone up?  Did she get caught with drugs?  No, much worse…She brought a pair of scissors to school.  That’s right, a pair of scissors.  She didn’t threaten anyone with them.  She didn’t use them in a threatening way.  She just brought a pair of scissors.  Probably to help with an art project.  I don’t blame her.  Those school scissors are crap.  But, apparently it’s not just against school policy but it’s so looked down upon that the kid needed to be dragged away in handcuffs.  Ridiculous!

Louisiana A fourth grade girl and her mother decide it would be nice to make some Jello and bring it to school for her classmates.  They decide to use little individual cups, like what you would use for Jello shots at a bar (turns out her mom works at a bar, which is probably where the idea came from.)  Turns out that was the wrong choice.  The girl was suspended nine days for “possession of materials that appear to contain alcohol.”  Yes, you read that right.  Possession of materials that appear to contain alcohol.  Not “possession of material that contains alcohol”, but that “appears to contain alcohol”.  Ever seen those root beer bottles that look like beer bottles?  Better not send any to school with your kid!

Policies like this are what’s leading so many parents to take their kids out of public schools and either put them into a private school or home-school.  I don’t know if the school districts don’t understand that, or they just don’t care because they’re too scared of being sued by a parent who says their kid is emotionally damaged because they saw a real pair of scissors at school. 

I think I’ll just do what all the democrats did after losing this last Presidential election…I’m moving to Canada.

(Update 12/14: The police chief apologized for the arrest of the fourth grader, but still no apology from the school district. Just a statement saying that they wasted “vital law enforcement resources.”)


Code Snippets in Visual Studio

There’s a lot to be said for working solo, but working in an environment with other developers definitely has its advantages.  Earlier, I posted about a co-worker who asked me a question about SQL and I had to dig through my brain to re-discover a way to do conditional joins.  Well, another co-worker offered up a tip that I probably would’ve not learned about had I been working solo.

In Visual Studio you can highlight a piece of code and drag it to the toolbox.  Rename it to something useful, and you have yourself an easy way to include code later that you may have had to retype or take time to find it then copy-paste it where you want it.  All you have to do is drag and drop that snippet from the toolbox to where you want it in your code.  Pretty cool!

SQL Server

Conditional JOIN’s

A co-worker asked me earlier today if there was a way to do a conditional join. Meaning he had two tables and he wanted to join to one or the other within his query, depending on the value of a variable. The two simplest ways to accomplish this are to use an IF statement based on the variable and just repeat the query with the small modification to the JOIN, or to use dynamic SQL.

I’m not a large fan of dynamic SQL and I’m definitely not a fan of duplicating queries that have such a small difference, like in the IF statement solution. I’ve run across this in the past and have come up with at least one solution that allowed me to accomplish the desired result using CASE statements. After I re-worked this example, it didn’t look familiar to me so I may have done something different in the past. Damn memory!

The idea is to use LEFT JOIN’s to either bring in the data or not, based on the variable, using CASE statements. Then, essentially the same CASE statement is used in a WHERE clause to filter out the unnecessary results.

In this example, there are three tables. Person, which is a parent table, and Member and Admin, which are child tables of Person. So a Person can either be a Member or an Admin, but not both. Here’s what the query looks like:

DECLARE @UserType varchar(25)
–SET @UserType = ‘Member’
SET @UserType = ‘Admin’

SELECT p.PersonId, m.MemberId, a.AdminId
FROM erdbo.Person p LEFT JOIN erdbo.Member m ON
  CASE @UserType
   WHEN ‘Member’ THEN p.PersonId
  END = m.MemberId
 LEFT JOIN erdbo.Admin a ON
  CASE @UserType
   WHEN ‘Admin’ THEN p.PersonId
  END = a.AdminId
  WHEN ‘Member’ THEN m.MemberId
  ELSE a.AdminId

When @UserType is ‘Member’, only those records where p.PersonId = m.MemberId are returned and all of the Admin records are excluded. When @UserType is ‘Admin’, only those records where p.PersonId = a.AdminId are returned and all of the Member records are excluded.

I don’t know how this would hold up in a performance test, so one of these days I’ll get around to checking that out. In the mean time, it’s a third option to the problem at hand.


Company Holiday Party(ies)

Ok, let’s try this again.  I’ll try not to lose this post before it makes it to the warm and cozy Home away from Home, otherwise known as SQL Server.

So I had two parties to go to in one night.  One is for my company, Statera, and the other is for my current client.  This was unfortunate as I pretty much had an obligation to attend both, which means that at best I could accomplish only half of each main goal I had for a given party.  For my company party my goal was to mingle with folks that I hadn’t met yet, as well as build on some existing relationships.  For my clients party my goal was to build on some existing relationships…with cute girls. 

Yeah, I’m a professional. 

Let’s face it, when a client wants you out of their company there comes a point when there’s nothing you can do about it and you need to start falling back on the primal instincts that got the human race to where it is today.  Don’t get me wrong…I’m sure they would love to keep me around.  It just comes down to a budget game.  In this particular budget game, the Consultant is one of the first to lose.  I’m actually pretty happy about that as a change is welcome.  I’ve been there since February working on pretty much the same application and it will be nice to move on to something new.

Oh yeah, the parties.  So at the Statera party things were a bit different than what I had imagined.  The company had invited a bunch of their clients.  This meant two things to me (as I would realize later):  First, there would be a lot of people there that I didn’t know.  Second, most of the people I did know would be hob-nobbing with the clients.  This pretty much eliminated any opportunity I had to accomplish my main goal as most of the folks I wanted to talk to I would either never see or would see but only be able to discuss things relating to whichever client they were standing next to at the time.  I suppose this is an ideal situation for the company as they get to build relationships with our clients, and that’s great.  I just don’t think a company party is where you try to do that.  A company party should be used to celebrate the holiday’s with folks you go to battle with and reward the employees in the company.

Don’t get me wrong, it wasn’t all bad.  The food was excellent, the drinks plentiful, the music entertaining, and the service terrific.  I would have been more than happy to stay there all night and just mingle, but I did have another engagement.

After a 15 minute walk and 5 minute drive, I arrived at party number 2 for my client.  This one was being held at Ocean Journey, a local aquarium that has had some financial issues.  They were actually recently bought by Landry’s, which is a seafood restaurant chain.  Kind of ironic (or convenient?).  Anyway, this party was more of what my idea of a company party should be.  All employees socializing with other employees that they don’t normally hang out with and with the opportunity to brown nose with the higher-ups.  The company has quite a few attractive young ladies working in various positions, some of which I get to interact with on a daily basis and others I don’t.  I was hoping this would be an opportunity to mingle with those that fall in the later category.  Especially since my “Little Red-Haired Girl” is in this category. 

If you’re not familiar with the “Little Red-Haired Girl” from Charlie Brown, she’s essentially his dream girl.  He always shy’s away and never really gets a chance to talk to her.  Oh, and she has red hair.  Well, my version doesn’t have red hair and I don’t really have a hard time talking to her (does that essentially destroy the analogy?), but she is never the less my dream girl.

As luck would have it, I did get the opportunity to talk with her.  I guess I’ll make a long story short here and just say that it didn’t get much past that.  I was hoping to meet up with her at the after-party-party, but that didn’t happen.  She went her separate way.  I’m sure I could be a bit more direct with her, but in the whole co-worker situation it can get a bit hairy so I’m not going to force the issue.

That’s about it.  A few drinks at a bar afterwards with a couple of the co-workers and off to bed I went.  All in all it was an entertaining evening, and well worth the time spent.


Posting Frustrations

I spent quite a bit of time writing a post on the recent company holiday parties I attended (I’ll rewrite it later).  Unfortunately, in the middle of writing that post, I left the computer for a while. 

Being an experienced web developer, you would think I would realize that the admin section of .Text is based on forms authentication and it more than likely has a auth cookie that expires.  I guess in the back of my head I did realize it, but I did nothing about it.

So I click submit on the post and…kablam!!  The next page I see is the login page.  I immediately realize what has happened and begin cursing.  I hit my back button hoping the text has been saved…no luck.

Oh well.  I’m new to this blogging thing and expect to screw a few things up.  I’ve read a bit about w.bloggar, so it may be time to look into it a bit further.