December 8, 2008 at 10:04 pm
Comments posted to this topic are about the item How To Avoid Msg 106
December 8, 2008 at 10:15 pm
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!!!
December 8, 2008 at 11:54 pm
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
December 9, 2008 at 1:46 am
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.
December 9, 2008 at 4:09 am
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 🙂
December 9, 2008 at 4:44 am
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
December 9, 2008 at 5:05 am
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.
December 9, 2008 at 5:06 am
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.
December 9, 2008 at 5:43 am
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.
December 9, 2008 at 6:58 am
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?
December 9, 2008 at 7:09 am
Hi,
Yes, that is also a solution, as pointed out on Erlands homepage (see above).
But to my management that was a more scary solution, so it was postponed.
The table-value UDF solution performs nicely, and does not require dynamic SQL, and is non-invasive. So it was acceptable to management.
Best regards,
Henrik
December 9, 2008 at 7:14 am
OzarkMountainMan (12/9/2008)
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 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? ... They could even be updatable.
Me thinks that replacing the existing zillion tables with views and consolidating the data in the zillion tables into a few tables would do it. This noob agrees with the Man. 😀
Paul DB
December 9, 2008 at 7:15 am
Henrik, you type fast. 😉
Paul DB
December 9, 2008 at 7:24 am
Henrik, thanks for the story and solution. Good job!
December 9, 2008 at 9:20 am
That's a horrible design problem to get over, but hardly an uncommon one.
You will find similar rubbish in many commercial products!
A neat workaround, and really nice to know that the view/tables limit can be overcome in this way.
I wonder if nested views would gave been an equally good cheat?
Reading the catalogues for the matching tables would be a nice improvement too.
Ultimately, replicating the data into a 'clean' structure (and this time insist a very good DBA does the design!) using triggers or some sort of near-realtime ETL process (SSIS or something similar) to ship the data, while new code is being developed will allow you to 'slide' into the brave new world.
Thanks for the knowledge!
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply