#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    10
    Rep Power
    0

    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....
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,594
    Rep Power
    4207
    Would you mind posting all these under a single thread please . Great stuff BTW. I've seen a blank sa password in a couple of places too and it scared me
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo

IMN logo majestic logo threadwatch logo seochat tools logo