Friday, February 24, 2006

More username fun...

Last week, it was the error caused by the dot in the username.
This week we got a slightly different variation on the theme.

A user kept getting a pop up window with the message
ORA-00933: SQL command not properly ended

Again, we trapped the SQL being executed and discovered that it was reading in the username.

The user's surname in this case was O'Neill.

The username included the apostrophe.

Wonder what we could get next week!

Friday, February 17, 2006

Beware the dot!

This post is slightly away from the DBA side of my job, but thought I'd share it with you anyway.

I think I've alluded to this in the past, but the role of DBA is only one of the many 'hats' I get to wear here.

I'm acting as a technical resource on an application upgrade project at the moment and it's been 'interesting' to say the least.

The project has involved moving the database from 8i to 9i and then upgrading the 3rd party application from one version to the next.
We're still carrying out all of the work on the test system at the moment with an aim to Going Live at the beginning of April, and it was all going so well until yesterday morning.

The application we are upgrading is made up of the core product, plus various bespoke add-ons to give us the functionality we need.
It's one of these add-ons that has driven myself and the third party consultant nuts for 2 days.

As part of the upgrade project, the Business has also worked on improving their Business Processes, with a view to automating or introducing electonic processes where possible.
One such change meant building in functionality to incorporate some standard company forms in the system which could be produced using mail merge.

Basically , the user would be able to open up a project from the application, click a button entitled 'Generate Form', select the form they required from a pick-list, click OK, and the form would magically appear in a Word document to be printed.

Sounds simple enough, right?

Well, the consultant arrived yesterday, we installed all of the necessary pieces of code and we were ready to test.

I'd like to be able to say that there was a magic whooshing sound and the word doc appeared out of thin air, but sadly no.
It might even have been fun to say that we pressed the button and the server blew up, but still sadly no.

You want to know what happened - nothing.

Absolutely nothing, other than a nice message saying 'Form generation failed - contact your sys admin'.

Me, wearing my Sys Admin hat at this point, wondered what on earth I was supposed to tell them.

Checking the various log files proved to give us no more information, other than it had tried to create the document and failed - pretty helpful really!

We put a call into the 3rd party support desk last night, and expected to come in this morning to be told 'do a,b,c and it will work'.

We tried a,b,c and it still failed.

Out of sheer frustration, and boredom of being told, 'try this, try that' as they frantically attempted to work out the problem, I sat with the consultant and suggested a complete walkthrough.
I made him sit and talk through everything that should happen, step by step, in the right order.
As he talked I followed by checking through each script as it should have been called, and searching for the correct file path so thay we could at least try and find out at which point it may be failing.

Then I noticed something, a slight discrepancy between the logfile and the system.

Basically, the application created a file with the merge values in it, and this file was then read in by Word and the values extracted into the form.

The file the application had created and the file Word was looking for, had different naming conventions.

They checked the code on both sides and it matched, the naming convention was username_datetime.mrg

That's when I pointed out our usernames were of the standard firstname.lastname

So when the merge file was created for me it looked like lisa.dobson_170220061154.mrg

When the file was being read in, it got lisa and then stopped at the dot.

End of process.

How frustrating!

The third party is now having to re-write the code to incorporate our naming convention.

Me, I'm off down the pub.