Results 1 to 12 of 12

Thread: [RESOLVED] Will you do a post mortum with me?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] Will you do a post mortum with me?

    I want to know how I could've avoided this bug.
    We have a function that goes: SELECT @Result = sum(MRSaleAmount) FROM Jobs WHERE (CustNo = @cust_no) AND (MRSaleDate BETWEEN dateadd(yy,-1,GETDATE()) AND GETDATE())
    You see that it is selecting MRSaleAmount FROM Jobs.
    Over a year and a half ago, I "moved" MRSaleAmount to its own table, xtblFONumbers, so that we could have a 1:many relationship between Jobs and xtblFONumbers. At that time, the function should've been updated to
    Code:
    	select @Result = sum(fo.MRSaleAmount)
    	from xtblFONumbers fo
    	join jobs j on j.Control = fo.JobControl
    	where CustNo = @cust_no
    	AND (fo.MRSaleDate BETWEEN dateadd(yy,-1,GETDATE()) AND GETDATE())
    Realize when I say "moved" that Jobs.MRSaleAmount was still a valid column. I updated xtblFONumbers from Jobs in a bulk UPDATE query, but the column remained in both tables. Maybe that was my first mistake.
    If I run a dependencies list on Jobs, I see this function listed along with 20 other dependencies. Was I supposed to look at all of them to see if I was breaking any?
    So my question is, when you make a change like this - discontinuing use of a column and putting it in a different table - what is the best way to find out what you're breaking? It'd be nice if you could do a dependency list of a column instead of just a table.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Will you do a post mortum with me?

    Usually, depending on the rules I have to play by, if I no longer need a column, I drop it. There's no point in keeping it around. Doing so then leads me to all the places (with adequate testing by both myself, QA, and inevitably the users) where said column is used and needs to be altered. If I can't permanently drop the column, then I'll at least take a backup of my local db, drop it from there and run what tests I need to to make sure I've gotten all my bases covered. Even if the field never gets dropped from Production, dropping it from my dev copy and running it will usually break enough things that I can find where it was used and needs to be altered.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Will you do a post mortum with me?

    Thanks, tg. Good advice for next time!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Will you do a post mortum with me?

    the column remained in both tables. Maybe that was my first mistake.
    Yeah, that's where you fell. It's a fundamental principle of database theory that any piece of information exists in one place and one place only. You don't even store it if it can be calculated from other places. As soon as you allow it to exist in two places you allow it to be inconsistent between those two places.

    (N.b. we actually sometimes do allow information to exist in two places for performance reasons. This is called "denormalization" and is kinda like knowingly cheating the system. By default you shouldn't do it but once you have a certain level of expertise you might decide to.)

    Anyway, the question really is, why didn't you drop the extra column at the time? Sorry if that sounds like a challenge, it's not meant to. There are all sorts of reasons why you might decide it's risky to drop a column, particularly in a production system (e.g. not wanting to break existing queries etc.), and I'm wondering if any of those reasons were in play for you. If they were then there's probably some tricks and tips we can offer that would have let you drop it more safely. On the other hand, if you just didn't realise that dropping the column would be sensible then, no worries, lesson learned, you'll know better next time.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Will you do a post mortum with me?

    All arguments pro and con droping the column not withstanding:
    To avoid breaking any SELECT-Queries (for reports and what not) you could define a Trigger on your new table, checking for your xtblFONumbers column (INSERT, UPDATE, DELETE), which then updates your Jobs column, so (in theory) it should enforce consistency between both columns.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Will you do a post mortum with me?

    Zvoni, interesting idea, thanks. FD - not sure why I didn't think to drop the columns. But I could've/should've and it's pretty chill here so even if I caused errors in the live system, users would've reported them to me and that would've been that.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Will you do a post mortum with me?

    Fair enough. Take it as a lesson learned and drop it next time.

    The sorts of measures I was going to put out include things like the trigger Zvoni suggested (although be a little cautious with that your code only ever updates the new location) or using views to mimic the table as your want them to be. These sort of measure buy you some time to go through your code and debug anything that's referencing the old fields.

    Of course, the right thing to do is drop the column in dev, update your code and unit tests, run it through your continuous integration process, roll out to your QA department, have a thorough systems text, put it in beta for acceptance testing... but most of us don't get to live in that world
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Will you do a post mortum with me?

    We're way too chill for even a QA department, LOL. My boss usually just tells me, if worse comes to worst the users can just use paper for a while.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Will you do a post mortum with me?

    Quote Originally Posted by MMock View Post
    We're way too chill for even a QA department, LOL. My boss usually just tells me, if worse comes to worst the users can just use paper for a while.
    I think i could like your boss
    I have more often than i'd like people complaining about "IT this" and "IT that" and
    "why does this not work? --> because in 95% of cases the problem is in front of the keyboard.... --> > You're a Meanie!"
    Gah, just letting my colleagues go back to being "paperpushers" just for 1 day..... i'd pay good money to see that
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Will you do a post mortum with me?

    I usually do something like this - renaming the column to add an "x" to the end of the name, for instance.

    Exec SP_Rename 'Table.ColName','NewColName,'COLUMN'

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Will you do a post mortum with me?

    Quote Originally Posted by zvoni View Post
    because in 95% of cases the problem is in front of the keyboard....
    lol!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Will you do a post mortum with me?

    Quote Originally Posted by szlamany View Post
    I usually do something like this - renaming the column to add an "x" to the end of the name, for instance.

    Exec SP_Rename 'Table.ColName','NewColName,'COLUMN'
    Also a great tip, thanks!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width