Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How To Avoid Msg 106 Expand / Collapse
Author
Message
Posted Monday, December 8, 2008 10:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:59 PM
Points: 1,354, Visits: 983
Comments posted to this topic are about the item How To Avoid Msg 106


Post #615925
Posted Monday, December 8, 2008 10:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:45 PM
Points: 26, Visits: 108
This has got to be a wind up ...

... if it isn't why not simply fix the dynamic sql to look like (after creating the CustomerMaster table) ....

IF Exists(SELECT Customer FROM CustomerMaster WHERE Customer = @Customer)

ELSE


Then fix the Customer insert to use CustomerMaster and instigate a background process to "migrate" rows from the old to new table.

Of course I expect each Customer will have an integer PK Identity column just to make matters worse!!!

Post #615928
Posted Monday, December 8, 2008 11:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:59 PM
Points: 1,354, Visits: 983
Hi Frank,

The system is quite a few years old, and there are many different .exe's running against it.
Please note that the solution supports selects, inserts, updates and deletes.

So to simply just move the data, and not support the old format for a while, would be a Big Bang solution. Big Bang solutions cause a lot of noise in the organisation.

I hate noise, so we came up with this work-a-round.

Erland Sommarskog has another couple of solutions as well, here: http://www.sommarskog.se/dynamic_sql.html#Sales_yymm.

Best regards,
Henrik



Post #615944
Posted Tuesday, December 9, 2008 1:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, Visits: 397
A truly horrific problem to deal with; you have my sympathies. Good job finding a working fix under what must be a lot of stress. You do know ahead of time you'll have to wade through all the 'you should fix the underlying problem' messages?

The closest I've ever personally witnessed was a table where new columns were added at the end of each quarter postfixed with the quarter date on the names. It was a giant matrix of NULLs except a diagonal row along the at-the-time current quarter. You can imagine, I'm sure. It was rapidly (quarterly) approaching the 1024 column limit but with no fix in sight last I heard.
Post #615967
Posted Tuesday, December 9, 2008 4:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:28 AM
Points: 46, Visits: 592
I think there's a far more interesting story to be told here about just how such a design was arrived at!

I've made my fair share of design mistakes, but I don't think I've come up with anything quite as catastrophically bad as this*. I'm genuinely interested - did the designer have any db experience? If not, how did they come to be in the position of designing the database? Did anyone try to point out the problems before or during development? As the tables mounted up was there a growing horror at the monster they'd created? Most importantly, were the lessons learnt?

Good luck with sorting the mess out.


* There's still time though :)
Post #616012
Posted Tuesday, December 9, 2008 4:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:59 PM
Points: 1,354, Visits: 983
Hi icocks,

Well, I've seen worse; like the programmer that loved to write "BEGIN TRAN", but forgot a few "COMMIT" or "ROLLBACK".

And this system actually works, and makes money for the company, as opposed to the example above. This means that this system has a future, as opposed to the perfectly designed system that was never released to production.

Best regards,
Henrik Staun Poulsen




Post #616028
Posted Tuesday, December 9, 2008 5:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:45 PM
Points: 26, Visits: 108
henrik staun poulsen (12/9/2008)
Hi icocks,

Well, I've seen worse; like the programmer that loved to write "BEGIN TRAN", but forgot a few "COMMIT" or "ROLLBACK".

And this system actually works, and makes money for the company, as opposed to the example above. This means that this system has a future, as opposed to the perfectly designed system that was never released to production.


Poor code IMO is never as bad a sin as a poor design - so I beg to differ on "worse". The difference is like poor quality concrete in the foundations for a 10 storey building measured against the door fittings being placed too low by a carpenter.

Further, citing "perfection" as impeding the possibility of a delivery of a competent/adequate solution is not justification for irresponsible deployment.

Cheers, Frank.
Post #616040
Posted Tuesday, December 9, 2008 5:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:45 PM
Points: 26, Visits: 108
Original post mashed the code frag...
Frank Hamersley (12/8/2008)


... if it isn't why not simply fix the dynamic sql to look like (after creating the CustomerMaster table) ....

IF Exists(SELECT Customer FROM CustomerMaster WHERE Customer = @Customer)
SELECT data FROM CustomerMaster WHERE Customer = @Customer
ELSE
[Build the usual dynamic SQL]

Then fix the Customer insert(s) to use CustomerMaster and instigate a background process to "migrate" rows from the old to new table.


I take your point about avoiding if possible a big bang changeover - but it may not be avoidable. If not you will just have to re-engineer from the ground up. To take my previous posts building analogy you may have to almost tear down the entire building and start again.

Cheers, Frank.
Post #616042
Posted Tuesday, December 9, 2008 5:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 258, Visits: 494
henrik staun poulsen (12/9/2008)
Hi icocks,

Well, I've seen worse; like the programmer that loved to write "BEGIN TRAN", but forgot a few "COMMIT" or "ROLLBACK".

And this system actually works, and makes money for the company, as opposed to the example above. This means that this system has a future, as opposed to the perfectly designed system that was never released to production.

Best regards,
Henrik Staun Poulsen



If you think that is bad, pity the poor slob (myself) who had to explain to his customer that the ROLLBACK statement does not actually "end" (commit) the customer's transaction.


Random Technical Stuff
Post #616053
Posted Tuesday, December 9, 2008 6:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 7:43 AM
Points: 14, Visits: 57
Couldn't you solve the design problem by creating a new table (or series of tables) that contain all of the customer data in them with a customer ID s to separate out which data belongs to whom?

Then in order to NOT have to redo all of your code everywhere, couldn't you create views to replace the MSTable1, MSTable2 tables that you have today? The views would point to your new table with a where clause that only showed the data that used to be in that MSTable1 design.

By using views in this way that are simplistic, they could even be updatable (possibly with the help of "InsteadOf" triggers), so that original code trying to add data to the views would still work.

Optimal for performance, well, no; however, it would over time allow a more smooth transition to the necessary structure to eliminate this problem instead of having the daunting task of redoing all of the code at once, the DB layout is how you need it to be going forward, this avoids the 106 error, AND you have the added benefit of not having to change lots of code right away, and you can gradually take your time to slowly migrate everything over to use the new structure.

Thoughts?



Post #616094
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse