Poor SQL Server Programming, Part 3

Since I have time to write another story -- here goes.

We have another SQL Server box in our shop that is a DSS (Decision Support
System). This system is loaded daily from mainframe extracts that we run.
I have two problems with the entire application and system that is used.

PROBLEM #1:
The windows application that we use is terribly written. It was designed
for a single customer to load their mainframe data manually. Of course, we
also run the same application internally within a service bureau environment
for multiple customers that do not want to run the application inhouse.
Unfortunately, since this application is single user, everything from
selecting the files, options to load, and the customer file that you want to
load is done manually.

Since the vendor mostly sells this product to inhouse customers and not to
service bureau customers, they're not likely to change anything. The
thoughts were since we mostly select the same options all the time and the
program cannot accept any command line options to avoid the point-and-click,
we thought'd we'd use vbscript and the SendKeys object to send keyboard
commands to the active application.

The only problem with this is... once you select all the menu options and
list box selections, etc., to do the load, you have to hit a "LOAD" button
on the form. Unfortunately, you cannot TAB or ALT to the button. There
apparently is no tab index or hotkey option to do it. The only way you can
select the button is by using the mouse!!!

Therefor the whole idea of trying to better automate the redundant
selections was totally blown out of the water. We are still waiting on
vendor to modify program to make it more automated and/or scriptable.

They also apparently SHELL out to DOS to run the BCP.EXE program. Because
every table they load a blank DOS prompt comes up and the screen literally
freezes and won't refresh until control and returned back to the
application. Means you basically can't do anything on the system during
this time... They're are clearly doing BCP because all of the .log files
that are generated per table which shows the number of rows loaded and the
time it took.

I guess they couldn't figure out a better way to bulk insert data than
this... Plus -- they never heard of DoEvents() either... apparently.

PROBLEM #2:
All our clients use Cognos Report Writer to access the SQL Server to run
their reports. Unfortunately, some moron created their catalogs that Cognos
uses to us the "SA" user account. That's right! Every one of our customers
and all their clients at each customer that uses Cognos is using "SA"! Go
figure!!! As I said in my last message, anyone can sniff the network and
gain the SA password and blow away my entire system. Heck... if they did
this, we wouldn't even know what happened. We'd probably think we had a
software/hardware failure or it was just a fluke. Lamers!


The only other thing I'd like to say that sucks about this entire system is
that some stupid sales person promised our customers 12 months worth of DSS
information. So every monthend, we have to cut a new database for each of
our customers.

Sooo we have 35 customers that access a daily database. 35 (customers) x 12
(databases) = 420 databases will reside on this server! Back from my old
company I worked for -- we only offered Daily, EOM, and EOY. Of course, we
had every EOM database backed up and could easily restore (at a charge to
the customer) if they needed it for something.

Has anyone tried to manage a SQL Server box with 420+ databases? It's
insane! It takes forever to enumerate the data... it takes forever to
maintain and backup.

In fact, the last time I did SQL Server 2000 SP4 upgrade, it aborted in the
middle. I had to call Microsoft and the reason was that the SP upgrade
stops/restarts SQL Server in the middle of the upgrade and then continues
on... well, apparently, the upgrade only waits a certain amount of time
before continuing. In my case, the SQL Server hadn't brought all the
databases back online yet before the upgrade continued.

Microsoft then helped me restart the SP upgrade in debug mode and got things
fixed.

As you can see, I have a number of issues with how this DSS server is used
in a day to day environment. Fixing these problems are not as easy as just
adding a new database user and setting role to "readonly". Each of the
catalogs must be created for each month database that we have and for each
customer. This is lots of work. It won't be fixed overnight.

Well, I'm off the soap box for now.

And yes, I'm not done! I have a couple more horror stores to share with you
guys -- but I'll save that for later, too!

comments, feedbacks, and flames welcome....

Wow.

You should put some of this into a slightly less ranty article and submit to the site. The db security in particular might prove helpful to some Windows programmers out there. I enjoyed reading through all three posts.