OmegaMom

(Note to regular readers:  This will bore you and/or perplex you.  Ignore this post!)

If you are a database programmer of any kind, and you are trying to run an update query but every time you get this error:

Failed to retrieve execution plan: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

and you got here by googling that particular error:

There is one cause of this error, and it is exactly what it means.  The problem is, some of the time (like in my instance today), you can’t track down where you’re getting this subquery that returns more than 1 value.

In my case, I was doing a simple update query that looked like this:

UPDATE Temp_Table as TT
SET TT.Action_Items = T.UpdateField
FROM  Temp as T INNER JOIN
Temp_Table ON T.Proj_ID = TT.Proj_ID

No matter what I did—rephrasing it, using a subquery instead of an INNER JOIN, trying to use a MERGE statement (which, alas, I couldn’t use, because we’re using SQL Server 2005)—I couldn’t find where on earth I was getting the multiple values returned.  Temp had exactly one record for every record in Temp_Table.  I was tearing my hair out.

After hours of googling, in the depths of one forum discussion on the subject, I found someone who mentioned “Hey, if you’ve got a trigger on your table, that’ll cause the error, too!”

So I promptly disabled the trigger on table TT, and voila, my query ran immediately.  The trigger was what was returning the “more than 1 value” being complained about.

I hope that someone found this by searching, and that it was useful and/or helpful.

“I’m as mad as hell, and I can’t take it anymore!”

Ahem.

What prompts this, you may ask.

This morning, after doing my exercise routine and showering, I sat down at the keyboard and started my telecommute sign-in process.  First, sign onto the VPN (virtual private network).  Next, start up the Remote Desktop Connection.  Then comes Outlook and, finally, Microsoft Communicator–the corporate kin of Microsoft Messenger, which requires a log-in to the campus network.

At which point, Remote Desktop bings and tells me it couldn’t connect.

So I try it again, and start reading my e-mail.

Then Remote Desktop bings again, telling me it can’t connect.

So I try it again, and keep it up.  This time, I get through to my “your password expires in three days” message, click on “no” to changing it, and go back to my e-mail.

Then Microsoft Communicator bings at me.

Then Remote Desktop craps out again.

Then Outlook shows a little message that says it’s “trying to connect…”

I snarl.  I send a message to my co-worker, saying there’s a problem with the network.  I finish reading an e-mail.  Microsoft Communicator politely informs me that it was unable to deliver my message.  I snarl again, and disconnect the VPN, to see if the problem is with that…I pull up my browser, try connecting to my blog, and sit and wait.  It brings up the main article, but not the sidebars.  The little whirly circle keeps whirling.

I reboot.

I pull up the browser again.  Same thing.

At which point it became obvious that I had to pull myself together and attack The Unholy Mess of Wiring behind the TV upstairs.

The Unholy Mess of Wiring is hidden behind an end table that has the TV on it, sitting in the corner of the living room.  The dotter has long since appropriated this area for her…um…let’s call it a “creativity corner”.  Every once in a while the unsightly heap of scrids and scrads of paper, various small toys, pictures, beads, markers, and what not overflowing the table, the wiring, the carpet becomes too much for me, and I corral the dotter into cleaning it up.  It invariably turns into a Horrible Chore that takes forever.

This time, I was on my own.  This time, I went through the whole area from top to bottom.  I threw out a half a garbage bag of scrids and scrads (no toys), some loose beads, string, wrappers, the backing from old stickers–you name it, it was there.  Then I pulled the table out from the corner.  I swept.  I windexed.  I re-arranged.  I got some clear tape and a Sharpie marker, located twisty-ties in the Anything Drawer, pulled out a variety of power cords from various techno-boxes, and started de-tangling, identifying, organizing, and tidying up the strands of cords and wiring.

The whole affair, from start to end (with a break in the middle for some bagels and cream cheese), took four hours and twenty minutes.

The network works again.  I was unable to figure out what the problem was, but it works again.

I now know which cord goes to which box.  All the cords are labeled.  The extension cord is secured in a nice small bundle.  The various cords are no longer a knotty maze, but easy to follow from electrical outlet to box.

But what a bloody pain in the butt it was.  Grrr.

OmegaDad, after watching a co-worker deal with the frustration of purchasing new software, sent this on to me.

1. Ask ITS for new software. ITS will ask you to fill out “The Form”.
2. Spend hours filling out The Form. You may need help answering some questions on The Form, but there is no form to get help with The Form, and no human knows the answers. (Certain questions were put on The Form as a cruel joke. There are no answers to these questions. YOU MUST ANSWER ALL THESE QUESTIONS.)
3. Route The Form for signatures. Everyone must sign The Form. There are 1.8 million people employed by the US Government. Most of these people will notice that you have made some error on The Form, thus they will return The Form to you. Correct the errors and resubmit the form.
   1. Only 7 of the 1.8 million US government employees understand how to work the postage machine.
   2. 6 of these people are at Team Building Training and cannot be contacted.
   3. The 7th person is currently recovering from injuries received while trying to repair the postage machine.
4. Once The Form has be routed for signatures, it will be returned to your ITS Representative. Your ITS Representative will notify you that The Form is now out of date. Please complete the New Form and repeat steps 2 through 4.
5. Prior to approval, the New Form will be placed in a clearly marked 8.5 x 11 file folder. The File will be stored in a secure location. Remember that scene from ‘Raiders of the Lost Ark’ where they stored the Ark of the Covenant in that huge warehouse? That it where The New Form will be stored.
6. A Transient Form Specialist at ITS will be notified that your Form has been filed. The Transient Form Specialist will be instructed to email you regarding the disposal of your New Form. Transient Form Specialists are temporary employees hired through the Americorps Program. As such, Transient Form Specialists do not have access to government computing networks. This is a Department of Homeland Security requirement. Please be patient while the Transient Form Specialist finds a local public library with Internet access.
7. Contact HR for instructions on how to transfer oversight of The New Form to the person who will replace you at retirement. If you wish to acquire new software in order to do your job more efficiently, this is the most important step. DO NOT FAIL TO CONTACT HUMAN RESOURCES FOR TRANSITIONAL FORM RETIREMENT COUNSELING.

P.S.  If you decide to pass this on, and you know our Real Names, please don’t use his, eh?

Those are the dreams that everyone has, where they are, say, giving a speech and suddenly realize that they’re standing up at the podium fully unclothed, and everyone is staring at them.

Or, as my husband related when I told him of my anxiety dream, the one where you know you have to take a final for your class, but suddenly realize you have no idea where the class is being held, or what the class was about.

These are classics.

Mine was a bit different:

I was at work in the cubicle farm (the physical venue was from waaaay back when, when I worked on the magazine in the suburbs of Chicago), tap, tap, tapping away at my keyboard, when I heard a ruckus from neighboring cubicles.  Someone was complaining about “the bug in the program!” and how it needed to be fixed.

I knew that this was a program I had written for J, in the Campus Supply department.  J had left, and someone else was taking over her work.  This meant taking over the program.  But, as someone else explained (loudly), “the bug in the program!” had been there all along.

So they called in this guy from the IT department, and he was getting the info from these other folks.  They were discussing it quite loudly, so I overheard.  I was suddenly overwhelmed with a feeling of guilt–how on earth could I have not fixed that bug yet?  But I had been putting it off forever, and now…now it was coming home to roost.  So I rushed out to intercept them, telling the IT guy that I knew exactly where the bug was, and it was easily fixable, and why didn’t we just grab a computer and I’d show him where it was and how to fix it.

We appropriated an empty cubicle, that just happened to have a computer in it.  I sat down at the computer with him sitting at my side.  He was wearing a contemptuous, sneering look.  This was a Very Important person from IT, who everyone knew had gotten his degree from A Very Prestigious University.  I started up the computer, and realized I couldn’t find the program.

I couldn’t even get the mouse working right.  The mouse had a heavy-duty industrial electric cable that attached it to the computer, there were heaps of junk around it on the desk, and the cable kept getting tangled in the junk.  Worse yet, the cable was short, so I had to yank it and yank it to try to get enough cable to get the mouse moving properly.

All this while, he was just sitting there, sneering.  Finally he muttered something about “you must be a CIS major” in a dismissive tone, and I found myself babbling about how I knew he had gone to Very Prestigious University and was very smart, but I had a degree, too, from Cal State, and it was a CS degree, not a CIS degree…

But I couldn’t find the program, and I couldn’t get the mouse to work, and I had never fixed the bug, and he was just sneering…

And I woke up from that nap in a very, very anxious mood.  Depressed.  Miserable, actually.  It was just as bad as the time I had (foolishly) decided to play Fur Elise–which I had just started learning–at a piano master class with a visiting master pianist, instead of the piece I had been practicing forever, which I knew backwards and forwards.  I had that exact same sinking feeling, the absolute and total desire to just sink down into the ground and vanish and Not Be There, a feeling of utter humiliation, the worse because it was self-inflicted.

Ugh.

Dear Very-Distant-Coworker:

When I sent you the email asking you a whole list of questions about how many copies of a particular document you received, I didn’t want a reply of “Yeah, I received a bunch.”  I asked you who you received them from, how many copies you got, and when you received them because (whaddasurprise!) I wanted answers so that I could track down the problem at our end.

Sincerely, OmegaMom-the-support-person

Dear Coworker-of-OmegaDad’s:

When he sent you the email stating that he would be in your town to do training, that he needed all people there for training, and asking what would be a good week for this, he did not need a two-page reply outlining all your difficulties, listing everyone’s schedule for two months, and a request for special trips to train Joe, Moe, and Schmoe.  Please don’t get angry when he replies quoting his original email and repeating that he needs all people there for the training.

Sincerely, OmegaMom-the-spouse-who-likes-to-see-her-husband

Dear L0we’s:

Please train your cashiers to use the L0we’s part number, rather than the manufacturer’s part number, when entering data.  That way, we won’t be told that parts that we know are in stock are out of stock and now on special order.  Oh, also, you won’t charge us for special ordering.  And we won’t have to deal with the front desk or the head cashier to get a credit.  Which we can’t use anywhere else.  Which might have been nice to have in our bank account, instead.  Hey, maybe you can start offering, say, checks to people for such overcharges?

Also, this time around, please be sure to deliver when you say you’re going to deliver.

Also, this time around, please be sure to deliver everything we ordered, which was in stock when we ordered it, rather than surprising us at delivery time by not having everything we ordered.

Does this make sense?  Good.

Thank you, OmegaMom-and-OmegaDad-about-to-embark-on-another-chicken-coop-for-smaller-birds

Dear Fruit Flies:

This is a declaration of war.  Die, die, die!

Sincerely, OmegaMom-the-lousy-housekeeper

Dear Kozmik All:

What have I done that I should deserve this ongoing itchy scalp?  The doctor’s antibiotics are not helping.

Sincerely, OmegaMom-the-itchy

Well, actually, yes we do.

A few weeks ago, I wrote about my little mystery hunt trying to figure out why the vehicle reservation system in my department of Small Mountain University was on the fritz, how I discovered that the unique identifier for each reservation was limited to four digits by Nice Young Intern, and how sometime in the past few months, we had hit 9,999 and the reservation numbers started all over again at 1, causing all sorts of funky errors.

My readers in the biz were appalled.  "Where was the code review?!" they proclaimed.

Yah, well.  (OmegaMom looks aside, shuffles her feet, coughs a bit…)

I love working at SMU.  I really do.  I started in the ITS department, got chopped in the Great Layoffs, took a (much less remunerative) position as an administrative assistant while waiting for IT jobs to re-appear, and then got my position in Current Department when the job market started warming up again.  The atmosphere on campus is collegial.  There are lots of cool folk doing lots of cool things on the academic side; there are lots of cool folk doing lots of cool things on the ITS side; there are lots of cool folk doing less cool but immensely important things on the business side, and almost everyone I’ve worked with at SMU has been intelligent, interesting, fun, and good to work with.  (I can think of two exceptions over the past nine years, that’s all.)

Anyway, in the ITS department, since we have lots of folks there, many ITS things were (and are) done by the book.  Good coders, good reviewers, good interactions between everyone.  But there are only so many ITS folk to go around, and those that are tasked with helping on more complex projects outside ITS are booked solid for a year in advance.

Out in the departments, there are IT folk who are hired as departmental support.  Itty bitty departments have to make do with no-one, or sharing someone with a few other itty-bitty departments.  Bigger departments get one or two.  Really big departments get their own geek squad.

Each of those support folk have to handle a wide variety of different issues.  There’s day-to-day support:  Figuring out why JoAnne’s email suddenly disappeared.  Helping Dr. Professor Jones stitch together a master document and sub-documents in Word (and trying to explain that master- and sub-documents in Word have a really bad reputation for getting corrupted and frying your entire 417 page manuscript).  Updating web pages.  Maintaining small databases.  Developing interfaces to bigger university systems, or maintaining old interfaces that have been just chuggin’ along for many years but need a tweak or two now and then.  Arranging repair for old equipment.  Buying new equipment.  Figuring out equipment budgets for the upcoming year.  Buying color toner when something needs to be printed in color RIGHT NOW!  Crunching numbers in spreadsheets.  Putting together pamphlets or brochures or quarterly newsletters.  Getting new people set up on systems and into the university system.  Running local servers.  Maybe running computer labs for students and staff.  Providing down-and-dirty training.

Rarely a dull moment.  Always fun to help people.  Good to get to know folks in all aspects of the higher education biz.

It’s sort of like juggling.  There’s always something that is urgent.  Typically, the urgent stuff is related to ensuring that everyone in the department is able to do their work.  Then there are the bigger departmental projects…which get done in and around the "making sure everyone can do their work" everyday stuff.  You’re catching the errors on this database while crunching the numbers for that report to the board while uploading PDFs to the website while figuring out why the network has gone down for the people in room 219 while…

What ends up happening is that whoever gets handed the bigger project just bulls in and does it. 

Like the project Nice Young Intern worked on.

It gets done, it gets tossed up, people who are chomping at the bit to get to use the project start working on it (ostensibly as testing) and then suddenly it segues into being used.  And rare is the chance to get someone to give your code a review.

I’m pretty sure Johnny and SpaceMom are shivering in their boots at that mild paragraph.  Where’s the project outline?!  Where are the specs?!  Where’s the test plan?!  Where’s the code review?!  Where’s the iterative process?!

I know, I know.  At times it bothers me, too.  But y’know what?  It’s really fun work.  You get to be a jack (or jill) of all trades.  You get to help people.  And all of these "bigger" projects are really, in the course of things, small potatoes; they’re "big" in relationship to the day-to-day stuff at the departmental level.  It’s not like the small army of coders and testers and code librarians and project managers handling a Truly Important Project like the online education program or the accounting program or the human resources program.  Those projects are vital, necessary to the lifeblood of the university as a whole.  They’re handled by ITS, they’re treated like good software projects should be, they take time and money and people and organization.  Those projects get code reviews out the wazoo.  Smaller projects that ITS takes on, handled by one team or another, also get the software project treatment.  But projects handed out to departmental support folk?  Those get dropped on the desk as a "Y’know, it would be nice if we could do x, y, or z.  D’you think you can pull something together in two weeks?" 

NYI’s project started that way, and my predecessor knew that it would probably not get done in any decent amount of time unless she borrowed a student from the CS or CIS programs.  It made a good summer project for NYI, and he did think it through, provide specs, program it, and get it up and running.  Since he specified only four digits for the reservation number, I’m thinking that people talked it over and figured that the program would be replaced long before they needed to worry about it.  Sort of like Y2K.

But code review?  What a luxury that would be!

(Technical stuff follows.  Feel free to ignore.)

Oooooo!

So there we are at work…our transportation center has an online reservation system that was written by a Nice Young Intern.  It was written back in 2002 and has worked okie doke since.  (Aside from the fact–which I discovered recently–that it has been running on the development server all this time, rather than the production server.  To those of my readers to whom that is an arcane distinction, let me just say:  production servers have paging systems.  If the production server goes down in the wee hours of the night, some poor ITS minion is paged and required to dash in to the office to Figure Out The Problem Right Now!  This does not happen with development servers.  If a development server goes down, the priority to get it going again is low on the totem pole.  Also, DBAs feel quite happy to Do Things to development servers, without worrying that they’re going to break something.)

Recently, we’ve been getting complaints from our TC that the users of the online reservation system have been getting errors.

I investigate.  Luckily (or not so much ‘luckily’ as ‘almost inevitably’, as it turns out), I immediately get an error page.

(Let’s set aside the fact that there’s no error checking, so we don’t have a “nice” error page telling our users that oops, there’s a problem, and please try again later??)

The error page says “unique constraint violated’.  What the heck?  Why would that happen?

Interestingly enough, the DBAs had just updated the development server shortly before the TC folks really pushed us about this error.  So I went down that path for a while.  But a DBA, when emailed, provided a clue–he said that we’re trying to insert new rows with a duplicate primary key.  (A ‘primary key’ is a number that uniquely identifies a row in the table.  For instance, if you’re doing a credit card transaction, the primary key might be, say, your transaction number.  No-one else is going to have that transaction number…or no-one else will have that transaction number on that date, so the primary key would be trans number plus date.)

I noodle around.

I investigate.

I discover, much to my absolute and utter horror…

The Nice Young Intern had set up the reservation numbers as the primary key.  This is okay.

The NYI had an automatic number generating doodad set up in the Oracle database to generate those reservation numbers.  This is okay.

The NYI had not used the default maximum limit for the reservation number–which would be some gawd-awful number like 999,999,999,999.

No.

The NYI created the reservation number system to have a maximum number of 9999.  This is not okay.

So…once the TC had gotten reservation number 9999, what happens?

The automatic numbering system starts all over again, at number 1.

Back when the system was being developed and tested, there were loads of jumps in the numbering system.  NYI would try a reservation, it wouldn’t work, he’d back out, that reservation number would be discarded…But as the system went online and real people started using it, the gaps in the numbering system would become fewer and fewer in number.

The first few hundred numbers worked okay.  Users would get an error once every great while, when the system tried to save a reservation that had a reservation number already used in the system.  But now…now…the gaps in the numbering system are few and far between.  Thus, as I said above, it was almost inevitable that the test reservation I made would not go through.

Why the fuck would someone create a system keyed on an automatic number that rolls over when it hits 9999?!  This is like our own little tiny version of the Y2K problem.

And I’m hopping mad about it.

Luckily, it’s an easy fix.

Grumble, grumble, grumble, bitch, moan, complain.