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


How To Avoid Msg 106


How To Avoid Msg 106

Author
Message
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1225
Comments posted to this topic are about the item How To Avoid Msg 106



Frank Hamersley
Frank Hamersley
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 110
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!!!
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1225
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



magarity kerns
magarity kerns
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 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.
icocks
icocks
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 628
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 Smile
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1225
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



Frank Hamersley
Frank Hamersley
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 110
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.
Frank Hamersley
Frank Hamersley
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 110
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.
ta.bu.shi.da.yu
ta.bu.shi.da.yu
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 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
OzarkMountainMan
OzarkMountainMan
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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?



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