Tuesday, April 25, 2006

I've got yer error RIGHT HERE, pal!


Some days are just harder than others. And today, is a hard day. Yesterday was also hard. And after eleven hours worth of wrangling…useless. I had to throw it all away and start over.

It isn't that what I'm doing is all that hard, really. Or at least, it shouldn't be. I'm having to use the 'openquery' routine in SQL Server to hit up an Oracle box.

But I can't see the box I'm hitting. So I'm querying blind. I can't just go look at the tables, because I don't have direct access. It's complicated – I can query them through a link server, but I can't…look at it.

This is not usually that big a deal. Honest. The actual query is a tad longer than this, but roughly something like this:

INSERT INTO MYTABLE
(ID, AMT_PAID, AMT_CRD, INV_NUM, BLAHBLAHBLAH)
(SELECT * FROM OPENQUERY (ORACLEBOX, 'SELECT ID, AMT_PAID, AMT_CRD, INV_NUM, BLAHBLAHBLAH FROM T_THISORTHAT WHERE INV_DATE > ' '01-APR-2006' ') C)

See? How hard can that be? Run!

Error: Server 1627, user not recognized.

Damn it. {Exit application, come back in} Run! (Why this works is beyond me, but it does.)

Error: Line 402, error near ')'

Damn. It. Uuuuuuuuuh, OK, extra comma. Take out comma. Run!

Error: Line 402, error near ')' Is not! RUN!

Error: Line 402, error near ')' Is not! RUN!

Error: Line 402, error near ')' Is NOT, you lying sack of - !! RUN!

Error: Line 402, error near ')'

See, now, if it didn't work the first twelve times, generally speaking, this is a subtle hint that it will not work the next twelve times, either. Undaunted, I keep hitting F5 like maybe this time, it will just sort of ignore the error and run my query.

Eventually, it dawns on me that this isn't going to happen. Combing through all lines, I find the error is not on line 402, it is on line 8. @*^&@.

Fix the error on the line 8. Run.

Error: Line 402, error near ')'

Oh, right. The comma I took out earlier. That actually belonged there. Heh heh. I knew that. Put comma back in. Run!!

Error. Line or field data would be truncated. Operation terminated.

@(*^&@O&^*@&^@*^&@(?*^?&!?^*&@?^*(&@#$^*(&!&*(^!!!!!!!!!!!!!!

Just truncate the freakin' data, I don't care!!!!!!!!!!!

**sigh**

ALTER TABLE MYTABLE {add ludicrous amounts of character space to all fields, don't give a crap about it really I'm going to delete the table right after I finish this FREAKING project} {freakin' truncating the space of the stupid PO numbers who the HECK built this piece of crap Oracle box in the first place WHY can't we just have a standardized grumble grouse kvetch}

RUN!

Error: MSDORA error 29672b2968ui20672q: Field or table does not exist.

What field? Which one didn't you find? **sigh** {Query Oracle table with dummy where parameters to get field names [because I can't just look and see them], fix the typo and move on}

RUN! Run like the WIND! RUN FOR YOUR LIFE!!!!!!!!

{…processing…}
Go make delicious Lean Pocket for breakfast.
{…processing…}
Eat Lean Pocket. Try to act cheerful and not grimace, even though it tastes like turkey-scented cardboard.
{…processing…}
Work Sudoku Pacific in newspaper, at which I suck
{…processing…}
Answer some emails.
{…processing…}
Stomp a few other fires burning elsewhere in Corporate America
{…processing…}
Do some laundry
{…processing…}
Organize the closet
{…processing…}
Knit a few rows of the scarf I meant to make myself months ago
{…processing…}
Call for refill of drugs (mmm…drugs…), read Wall Street Journal
{…processing…}…????

At this point, 9:45 p.m., after fourteen hours of inattention to detail, I realize that I am:

a) using the wrong instance of Oracle
b) returning all 35,000,000 rows instead of just a subset of them due to having forgotten the second half of the 'where' statement, and
c) not cut out for this job and should return to being a stay at home mom, where the most challenging thing presented is how to draw a castle with rainbow starfish ponies on it

This morning I came back at it and actually, it's going better. I dropped and rewrote my tables, redid my openquery (with full where statements this time, TYVM) and now I've got all my data. Yup. I've got all 250,000 lines of information. Sitting there. In my tables.

It's just that…uh…well. I can't really remember, ahem, what I was planning to do with this information, once I got it.

I'm serious.

I can't remember where I was going with it. I was doing something with the credits and the debits and was going to compare them with…some…other thing…to do a kind of rebill thing…but I can't remember where.

There are eight distinct sales programs residing on three different SQL servers, with about two dozen linked Oracle servers housing various data regarding some, all, or none of them. What I need is…somewhere out there.

At least, I'm pretty sure it is. But my notes about what I'm doing look like they were written by a drunk monkey (well, I am taking hyoscyamine) and I think I may have mixed two different phone calls on the same sheet of paper and besides – I don't see what a Fidelity IRA has to do with anything.

****sigh****

It's going to be a loooooooooong day.

4 comments:

Very Herodotus said...

This is what was going through my head as I read this post:

"openquery? Cool, I could use this."

"Uh-huh, okay I see that. Yeah"

"uhoh. Line 402 is *not* behaving."

"Why does she keep trying the same thing over and over? What, is she on drugs or something? Oh, yeah, that's right."

"Hey! I'm not the only one who cusses at her monitor! Woo hooo!"

"I love sudoku myself. I finished the samurai one on sunday."

"35 million rows??? Gad zooks. using the wrong instance. That frickin sucks."

"Oh man, I have totally been there! It's like someone walking by should be able to hear the crickets chirping in my head where my brain is supposed to be."

Oh Tama. You'll get it. You're doing really good considering how sick you've been feeling.

WonderTama!!

21st Century Mom said...

oh man - I used to do that stuff. I was notorious for just scratching at something for hours instead of stopping cold, putting it down and coming at it from a different angle. I was a head banger of the worst kind. I think you do better than that.

I'll bet you have a 'to do' somewhere where the answer to what you were 'to doing' on it. You just need to remember where it is ;-)

Stephanie said...

It could be worse. You could just not care at all.

My interaction with our databases is pretty much confined to looking stuff up with an interface they gave us that limits the harm we can do ('hrm, I need an account that was started before 2004 and that has fewer than 50 published photos' type stuff). So I have a very general idea of what is going on, but nothing too specific.

However, even I am still marveling over the latest from [co-worker to remain nameless to protect the assuredly not innocent]. While making a copy of part of our production DB so that we can use it in QA for testing purposes, he ran into so many errors that he decided it wasn't worth it to figure out why they were occurring and that the best course of action would be to just give us the copy without saying anything.

Yeah, go back and read that again.

"I am making a copy so that QA and dev can test stuff. In the making of this copy I am running into errors. Eh, let's just hand it over."

Can I cry now?

John said...

Reminiscent of the official sounding errors that Windows throws up when one of our applications goes off and visits someplace in the OS it's not supposed to go. Maybe the address that Windows prints when it says xyz.exe has caused an illegal function at address QLMNSD, but to me (and the developer) it simply says "the program blew up". Helpful, I know.