SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Uncontrolled Code


Uncontrolled Code

Author
Message
chris.smith 91049
chris.smith 91049
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
OCTom (1/21/2014)
Excel does not suck; people do. It's the people putting together the solution, be it a "proper" one or an Excel one, that determine if it's good.
Tom


"Guns don't kill people: People kill people" - However, like a person with a gun can do a lot more damage, a user with Excel can suck a lot harder.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148897 Visits: 19446
Jeff Moden (1/20/2014)
It's not Excel, spreadsheets, or anything else in particular. It's crap code and human error in general. I just saw an article yesterday where a couple got a snail-mail address to "Daughter died in car crash or current occupant". Obviously, someone typed something into the wrong field on some data entry screen somewhere.



That's different. Code should be checked to prevent human error, since it will run many, many more times.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4418 Visits: 5923

but we can try to make a difference by producing software quicker

lol why didn't I think of that!

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148897 Visits: 19446
Yet Another DBA (1/21/2014)
I have seen Excel being used by an insurance actuary and then someone promised that the same equations could be done in a "proper" language and a "proper" client rich application. Over 2 Weeks later 2 developers managed to get the sub 1 second accurate excel spreadsheet calculation to a staggering 10 seconds with bugs in the calculation.

This I say would be typical of many companies where IT tries to gold plate a (*&* and tries to impress the company to justify their budget. It is little surprise that the many business units dont want IT development involved.


One reason we're stuck with Excel (and similar solutions).

However, if the spreadsheet gets changed, or altered, as can easily happy in a fluid situation, there's no check on that. Risk in increased, though perhaps negligibly.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148897 Visits: 19446
chris.smith 91049 (1/21/2014)
...
Yet, every company I've worked at has known the problem, and yet still produces them - the problem is that the manager is looking for a short term solution and doesn't want to waste his time specifying the problem and helping with the design of a good system. It's hard to get them to see the big picture - and if we're telling her that we can give her a solution in 8 weeks time and she needs some figures for Friday, she'll do it anyway, then after a few weeks it's become part of their business process and they can gradually add new parts, and she's not interested in our permanent replacement. Like the frog in the pan of hot water, she's not noticing the incremental additions and manual work going into that EUC.



Good points and I'd agree with this. However I'd also say this is partly an IT problem that we need a way to get apps done quicker, and easier migrations for users.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rod
Rod
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7228 Visits: 2182
Good article, Steve. I don't see how to get around this one. Where I work there aren't a lot of people who even know how to open up Excel, let alone use it. However, our upper management does, and they write their own spreadsheets, develop their own formulas and I'm certain make some unknown amount of business decisions based upon them. And I've never seen the formulas they write, to verify if those formulas are valid or not. The power of Excel is both it's beauty and it's potential danger. If my boss can write her own calculates in Excel (she can) to do something trivial, then I'd rather she did it rather than wait for me to write an app to do it for her. However, you article does really give me pause - perhaps I should pay more attention to what she's calculating.

BTW, as an aside, thank you for writing about the "race to the bottom". I've never heard of that, but can definitely see that in action.

Kindest Regards,Rod
Connect with me on LinkedIn.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148897 Visits: 19446
Yet Another DBA (1/21/2014)

Chris, all applications will have errors, I haven't found one app that hasn't but still live in hope.

I have heard that MS Access:
* has been used for years to provide the fuel calculations for an air fleet of a world leading airline.
* used to track the sales of $1 billion because it was more flexible than Oracle financials

Yes, neither of these is best practice and I know that a lot of IT pressure was made to get these changed. But the different IT departments could only promise a deadline that was months/year away which was unrealistic for the business operation and profit margin.



Absolutely all apps have errors, but if you run through a cycle, and you have version control, you can more easily find or prevent errors on changes. If you're adhoc in improving your Excel/Access solution, you could easily cause issues.

The fact that you track sales or fuel with anything for years doesn't mean it's correct. The airlines could be improperly calculating fuel, and it be on a scale that they consider to be waste or loss.

Does it matter then? Perhaps not. However, a wrong copy/paste of a cell or change or a formula could affect bonuses, sales reporting, etc. and easily covered up by a single person.

I'm not saying we shouldn't use Excel, but be aware of problems, and perhaps look for other solutions. Not throw up our hands and accept this. The solution certainly isn't an 8 week/month development cycle, but perhaps we can hook into Excel, write tests/checks, maybe even build auditing or warnings into add-ins. Possibly something else.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29475 Visits: 11542
I learned the hard way that, sometimes the accounting department will hide or strikethrough records when they really intend for them to be deleted. To a non-technical user, it seems logical that, if they strike-through an entire row or a cell within a column, then that means it's deleted.

Of course, these "deleted" records still exists when the Excel sheet is imported into a SQL Server table by a scheduled SSIS process. Exclamation

Dammit, why did the ETL process import the record; can't the guys in IT plainly see that the rows were "deleted"? Angry

Oh, well, I guess it's time to "fix" the ETL process. :-P


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4418 Visits: 5923

I think Steve's final sentence was telling. We need to deliver solutions faster than the business unit can develop their own

I think its awesome how sqlservercentral has solved a problem that the rest of the industry has been struggling with for quite a while now, who knew it was something as simple as "program faster"!!!! Props to you all, I would have never expected that this little website would produce the mythical silver bullet that we've all been looking for all these years!!!!

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148897 Visits: 19446
patrickmcginnis59 10839 (1/21/2014)

but we can try to make a difference by producing software quicker

lol why didn't I think of that!



I'm sure you did. The problem is neither of us knows how to do it.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search