Cow-selling Notes: The Batch Job

There was still a batch job outstanding. S was pulled out to work on the next hot potato, so I was on my own.

It was a monthly batch job to extract data into Excel. Sounds simple. It could have been, if not for the usual complications and unexpected roadblocks.

Again, it was the slipshod specifications. Data from 3 tables were to be extracted into 3 separate Excel files. Looked at the sample files and shook head - the formatting of the headers were different for all 3 files. The design of the columns were unsuitable for certain types of data. I think it's a 5 min work. And obviously none of the users had bothered to open the sample files for a look. If I were to give this for UAT, sure kena firing squad.

But what if... that was what the users truly wanted? They siao siao one sometimes.

After consulting the TL, we were going to change the columns. However, I would leave the headers intact.

I had to use a tool that I had never used before - SQL Server 2005 Integration Services to create a SSIS package. I was not the first to do this, so I had a sample program for reference. Using VB scripting, it runs a query and writes the results line by line into Excel. Sounds simple.

But of course, how could the Sotong be let off lightly? No development phase can get through without a struggle.

I quickly realized that my batch job was going to be far more complex than the sample. The sample was full of hardcodes. If I didn't want to be writing and maintaining 7 different programs, I had better figure out how to use variables. Man, the development tool's UI could use some redesign. It was a struggle to pass variables. Not very intuitive! I had to scour the internet for a very detailed guide. The kind for dummies. Not a friendly learning curve.

I found that passing blank as an input parameter to the batch job wasn't allowed. That's deviating from the usual way we work. Potentially, it might pose a problem if somebody other than me were meddling with it. Which meant that I MUST write the monitoring guide to cover my backside already.

File exists check before generating the file... later, if I have the time! (usually means never)

When I found the Excel Connection Manager, I thought it was a totally cool idea. It could spool data directly into an Excel file. No need for VB script afterall!

I tested using the debug mode. Printed 1 line successfully! Ok continue!

But when I tried to run the proper way by using the SQL Server Agent, it didn't work :( Some cryptic Excel error. Don't know how to add more logs to help myself XD

Ok nevermind. Why don't I try fixing it on SIT instead?

WORSE. The moment Excel was launched, it asked for the installation CD. The prompt will go away if you click Cancel, but I couldn't be incorporating the click into my batch job?? Asked R for access to the CD, but the SIT server couldn't locate the file it wanted.

Great. So this batch job shall join the dll-based ones, cannot be run on SIT. I informed my TL that the batch job was going straight to UAT since there was no way we could test on SIT. He wanted me to ask for help to fix the problem as he didn't want his things to end up like the rest, but I refused flatly. I have tried already, and I didn't have time to try harder. The environment setup has been left hanging for don't know how long already, so I don't think I should even believe it will be fixed for me.

万事有 UAT environment~ Totally wrong, but bo bian. Actually, it saves time because it is almost the same as Production. If it works in UAT, chances are, it will work in Production. And that's all we wish for!

I ran into the same problem :( SHIT. Don't tell me our system cannot support and I got to redo the whole shit in some other way?

Googled the bloody cryptic error message again. There HAS to be a solution. This is not the time to get stuck!

FOUND. Instead of using Integration Services package job step, I should use the command line job step to call the 64–bit version of dtexec.

DIAO!!

I wasn't interested in finding out why. As long as it's working!

Time for the next bomb. The extraction worked fine for narrow date ranges, but failed upon expansion to 1mth's data. Query wasn't slow... I soon discovered the ultimate, stupidest, mother of all limitations -

You cannot spool more than 255 characters into an Excel cell.

I mean, what era is this already??

I want to 跳楼 now.

Blame it on the technical analysis. The users should never have been allowed to enter more than 255 characters.

Ha. Ha. Over the users' dead bodies!

I know. I can truncate and hopes nobody finds out.

Yeah... Right...

Propose to give a flat file instead.

Ok, but only as a last resort.

There was absolutely no problem with spooling into a flat file, but I couldn't figure out how to convert the flat file into Excel. This transplantation is a very simple copy and paste function when performed manually.

You know you are going insane when you start thinking:

You know, I think I would rather do it manually than to go on struggling. It's only once a month.

Shout 救命 also no use. Nobody can help me. Not even Google.

The fucking 3G signal didn't cooperate during office hours. Or colleagues have a greater right to it because they were doing investigations. And I was multi-tasking.

Desperate times called for desperate measures. I could split long data into 255-characters long segments and spool into separate cells first, then concatenate them line by line using VB script. Sounds stupid and slow, but WHATEVER THAT WORKS!

Ok try out now! I struggled with the bloody syntax - it's similar to Excel macros but the summation button is the chimmest thing I can do on Excel >_< And it did work!!! Tried with lots of data, not slow also! Finally!!

Emboldened by my new bag of tricks, I threw in decoration for the headers. Just bold the header only lah. Something that wasn't mentioned in the specifications, but I knew the users would surely ask for.

Breezed through SIT by giving the files generated from UAT. Again, TL asked me to deploy to SIT, but I called it a waste of time. Attitude liao haha.

For UAT, I wanted to give the users the same files for them to verify, but they asked for the data from Production. Hey... that means I need to deploy to Production although we haven't passed UAT? Where got like that one? NO!! Block! Why was AiHiss supporting such a request anyway?

Actually, I was in favor of it lah, because I'm not so comfortable with the unconventional way the batch job was done. Testing with real-life data on the actual server will allow me to prevent failures upon rollout. We have been having issues for every release and I dread the day I'm responsible for one!

Also, the batch job would not affect anything else if deployed to production.

But the PM succumbed. O well... Now I had to deal with the DBA because I'm gonna mess with his server... and he didn't know SSIS as well as I did yet.

Surrrprise! Gargantuan volume of data for No Use Hospital, couldn't fit into 1 Excel worksheet at all! Each worksheet could only hold 2¹⁶=65,536 rows but that was not even enough for 1 week's data.

See, never do impact analysis! How? Need to send out the files tonight already.

Don't care, I shall take the liberty to split into more Excel files. Got to do it manually since I needed time to figure out how to automate it. Actually, I'm not going to entrap myself by suggesting that. Splitting into different worksheets in the same file was a no-no too. Any form of splitting is bad! Probably shall propose increasing the job frequency or just spool into flat file (dream come true!).

Surrrprise #2! No other institution's data volume even came close. Only Took Their Savings Hospital might have a problem if they suddenly decide to increase their volume and of course they won't think they were obliged to warn us. How to convince them to accept my proposals above?

I don't know. The TL and PM can think about it. I'm going home, bye.

So in the end, No Use Hospital agreed to the flat file with the job frequency unchanged. because the user is savvy enough to import into his own database. But the rest of the pampered guys still wanted Excel, but job frequency increased to weekly.

Great, now I have more things to maintain!! ARGH!!! STAB ME PLS!!!

Since things were going to be kept within a single worksheet, we could merge all 3 files into 3 worksheets in a single file. Less clutter!

The batch job entered production officially. I had to manually generate April's files :( While doing so, I found that sometimes, the batch job simply refused to start, throwing some weird Excel error again. I found numerous Excel processes still running although there were no active applications. If I killed the processes, the batch job was back to normal. Odd... Shall see if it happened when it ran automatically.

Yeah... It happened. Luckily I anticipated it and was able to recover the files before the users found out XD

And so began the long and painful investigation and fixing. I swear I have .Close and .Quit each and every Excel object I created. Want to give up, but... :((

Can I just kill every Excel process I find?

Sure, if I'm quitting really soon, like, now!

As it I didn't have enough problems, my SQL Server Management Studio expired. Reinstalled, but now my Visual Studio Tools for Applications was unable to find my imports. Hey wtf, it was all ok just now! After fumbling through tonnes of websites and forums (many with why-are-you-asking-a-noob-question-pls-go-read-up kind of reply), I found that some system files have gone missing. No idea how it happened. Together with the DBA, we copied them from whereever they were found, to the GAC folder. And you can bet I have never heard of GAC till today.

The discovery - in the VB script, each time an Excel element is manipulated, an Excel process will be created. Each worksheet, row, column, blah blah, right down to the cell level. And they will linger until you release each them explicitly in the correct order.

OMFG why does something like this still exist?? 我要上吊!!

You drive me crazy!

FINE. I FIX. *grumbles*

And the batch job was finally running successfully on its own.

But it's not the end of the saga yet.

Disk space in the server was running out fast with this new report! I never developed for archival - I thought of it, but usually not part of the batch job mah, and basic requirements not done yet, so this can wait! Aiyah zipping is also a temporary solution only. Users want them in the shared drive mah, so the only true solution is to increase storage. Out of my hands!

Monitoring guide not done yet. LATER!!

Coming up next, more problems.

Favorite Letter of the Year

It has to be the $$$ Bonus cum Increment $$$ letter!

2.9 months! Whee! Personal best!

No, don't talk to me about how much people working in banks get. Tsk.

Ya, back to the letter.

Increment of 6.5625%!

When there was no increment last year...

So, I can tell my boss about the bomb already, right?

Nah, I think I will tell her after I sign the consent... confirmed already then say. For what I know, I might chicken out of it?

Sotong Captures the Lion Again

Due to the overwhelming response, SinkHell decided to offer discounted Lion King tickets again. Having missed it the last time round, N wanted to try for it this time. Yup, she didn't keep the tickets for herself quietly. Or she wasn't kiasu enough nor open enough about it to discuss strategies. Well anyway, I agreed to help her out. What to do, people always help me mah. 有 Sotong 在，confirm 拿到票!

The link to the booking site was to be released at 12 noon again, so we've gonna have a late lunch. I stood by with my laptop, ready to help her launch a DDoS attack perform the booking. This time, the link was sent on time. A National Cockup Schedules URL. On N's terminal, it was asking for OAID login, but on my laptop, it seemed to have automatically detected my identity and logged in as me. This would not do as I wasn't entitled to the show again. I didn't know how to logoff, so, sorry, couldn't help!

So, they have now upgraded the system by limiting it to staff! *Applause*

On her own terminal, N keyed in her credentials, but upon submitting, the system threw a cryptic error message. Retried, happened again. Could hear the others feeling confused too. No idea what the problem was. System didn't seem slow. Not ready? No idea :( PM was asking the TL if he knew the meaning, but he didn't.

I noticed the URL changing to a Sistic one for a split second before reverting to National Cockup Schedules upon submitting. Hmm. I tried to capture that Sistic URL by hitting Stop button and accessed the URL directly. Guess what, IT WORKED! N could now book her tickets. Everything was super smooth once we got past that stupid login screen!

Which brought me to the conclusion that Sistic must be very mad with us for causing slowness at their website the last time, so they made us put up this pseudo-login screen which was nothing but a... not even a load balancer ley, dunno what to call it, maybe Toto results generator lah, lucky can get in lor. So after you managed to access Sistic, everything was back to the usual Sistic workflow, where you needed a Sistic account at the end, without further verification of your identity. Dumb hor. What were they thinking?

After N has secured her booking, I broadcasted the trick to the people who I knew were trying, such as my PM. Too bad appraisal over already haha.

Don't mess with programmers! Even blur and lazy ones. Still good at useless things!

Last Lake Fish

NOoooOOooOO!!!!

Lion King - The Musical

I still couldn't believe how lucky I was to snag 3 tickets on a Saturday. Can sleep till whenever I wanted, go shopping, watch Lion King, get home late and still be in no hurry to sleep. Sadly, Brother had to work, so it's just Sister and me for the day programme.

Sister was thinking of eating Todai for lunchner (lunch + dinner), but I'm really not confident of eating buffets anymore :( 1.5hrs might not be enough for slow, adult eaters.

We dropped off at Suntec City. Wasn't really hungry, so lunch was just Soup Spoon's clam chowder (craving for MSG but stopping myself from eating ramen again) and some of Sister's doughnuts. Then we grabbed a cuppa from Joe & Dough, which was actually quite good. I think I'm outgrowing Starbucks and Coffee Bean already?

Window-shopped. Couldn't buy anything although it was GSS since I didn't know if I could still use it 4mths later...

Dinner was Thai Express at Esplanade because Sister had never tried it before. Don't ever eat at this outlet. The green curry laksa was way too salty. So salty that I couldn't bear drinking the soup and was actually straining it from the noodles. Bleah. Were they trying to cater to the tourists, which might have a higher proportion of people from country C? The tomyam seafood glass noodles' soup was souless. Plain sourish and spicy, no seafood taste. I didn't recall Bishan being that bad.

Run to MBS! Because Sister didn't realize that we had to enter 15min earlier and thought we still had a lot of time. Got to clear our bladders before the show, but the toilet was so so so full! Ran into Brother outside the toilet, what a coincidence!

Then we ran to the theatre. There, Sister parted from us as she had to go sit at the single seat at the Dress Circle. Brother carried me to our seats as it was all steps. The ushers made sure we settled down comfortably before leaving. A markedly better experience than the non-existent cinema ones. I'm not sure where the wheelchair-bound normally had to sit. Maybe special bay in front? But I didn't want to risk having to sit alone.

People streaming in slowly. Didn't see anybody familiar.

Ah neh couple in front of me! The smell was seriously strong XD As you can see, the woman's head was blocking my view :\

Before we could start, we had to bear with a speech from... forgot who.

And the show started! Impressive songs, music, dance and movement. It could get quite loud. Not suitable for babies. Everybody seemed to be engrossed in the musical, except for the lady next to me who seemed more interested in texting on her mobile. She left midway. Partying was probably more for her. Pity!

The view from my seat was surprisingly ok afterall, in spite of it being quite far back and off-centre.

I think I had watched too much motion pictures. Not able to catch very well the messages conveyed in a non-in-your-face way, such as in a stage setting. It took me quite a while to understand a particular scene was about lionesses hunting... and you might have guessed, it hit me only when the lionesses were happily mutilating the zebra. O... so that was what all the prancing was about...! It took me even longer to realize that the fat auntie with a huge board hanging on her ass was the baboon... S-L-O-W!

Intermission! Haha like so high-class hor, watching something with an intermission. Actually, I think it's just something to ensure the audience didn't get DVT.

Look at the crowd!

Look at the overpriced drinks! Toilet tap water for us then. Grr so thirsty after Thai Express!

I had seen the movie before, but I'm glad I had forgotten the story, thus enabling me to enjoy the musical in anticipation. Sure happy ending lah, but the process? The theatre burst into applause on curtains down. Wa cheapo SinkHell staff also can behave high-classly lor. I thoroughly enjoyed it, especially when it cost only 20 bucks wahaha. It's amazing how many backgrounds can be accommodated on a single stage and the speed at it could be changed. How songs had to be perfect, acting had to be executed without NG. The cast did the same thing everyday, but considering how gymnasts can slip up during Olympics, *kowtow*

Now to get home. The taxi queue was being itself. Sent Sister to join the queue while Brother and I waited at the front. Observed how everybody would rather give the Chrysler a miss. In the end, an angmoh did. Rich tourist! I spotted a lady who had a makeover done by S. She looked like somebody who didn't need a makeover...

The guys managing the taxi queue gave us priority. Yay!

Lion King, The Admin Nightmare

No thanks to the uncustomised Sistic website and creative but poor workers, I expected the people in charge of this Lion King promotion to have hell of a time. In a negative way, of course. I have no idea how they were going to verify the identities of each transaction. Let's see what they would do if he's a foreign particle, like my account, which actually bore my father's name. Fear not, tickets' in my hands already!

I was getting worried that the HR wouldn't accept country C's staff's name for the tickets. Better borrow another name... but WHO?

Eyes roaming the area...ah L! Why didn't I think of her sooner? She wasn't interested in Lion King and was willing to lend me her name. Yay! I will definitely pay you back when they dock your pay.

Parents were away in KL for the past weekend. When they returned, Father forwarded me a shock sms received on his phone - the HR had noticed that he wasn't a National Cockup Schedules staff, so if he didn't reply with his name and RO by 31^st May, they would cancel the tickets.

Argh! Now then I know! Nononono I'll explain! Using my own phone, I replied the guy and volunteered the information about lending my father's account to another colleague. Please accept my explanation. And don't blur please. There must be a gazillion other cases like mine out there. *Crosses fingers*

Thankfully, he replied positively. *phew*

But it was still not the end. I think the poor guy was badly confused by the circus out there. He followed up with a couple of phone calls which I patiently (had to) explained the whole story. Yeah, my colleague didn't have a Sistic account mah, so I lent her mine lor. *teethy smile*

This should teach them to implement some sort of login procedure next time lah. Don't mess with employees, we get creative when it comes to non-work stuff!