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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3137 Visits: 1236
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



Paul DB
Paul DB
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 258
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. BigGrin

Paul DB
Paul DB
Paul DB
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 258
Henrik, you type fast. Wink

Paul DB
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224943 Visits: 19637
Henrik, thanks for the story and solution. Good job!

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
Phil Morris-454316
Phil Morris-454316
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 36
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!
andreq1-726318
andreq1-726318
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 55
It is a clever solution to the design bug. Probably like many other readers, I'm trying to think of the underlying problem.

My 3rd thought was to see if all the DMO interfaces to the tables are through stored procedures. Then in a pinch you could always do the "If the customer is contained within the new group customer table, SELECT the data from there, otherwise get it from the appropriate individual table."

That way, you could theoretically migrate the tables a few customers at a time. Do a pilot run with a few customers to vet out any potential bugs, and then plan the migration. Since its dynamic SQL, you can slowly implement it to avoid the BIG BANG, if you want.

Ok, punch holes in this one.
jpeden
jpeden
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 11
Clever solution to a problem due to a bad design. Thanks for sharing.



YSLGuru
YSLGuru
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7492 Visits: 1668
henrik staun poulsen (12/9/2008)
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


Just a thought...

This might be a great opportunity to convince management to allow some level of testing of a rewrite using the better ideas/suggestions provided not only here but everywhere your story has appeared. While the current solution works you never know when Microsoft may make a change that results in your solution no longer working. And while you always run the risk that anything can change, forcing one to make changes to their system, the chances of being in that kind of predicament is far greater for those with creative work-a-rounds in place then those systems more closely follow what is generally accepted as 'Best Practice'.

No one will meet the "Best Practices" outline %100 because there are too many variables in the Real World to always go by the book but the closer you are to meeting that ideal the less risk you have of being forced to rework your work-a-round down the road.

I mention this only because a former employer of mine found them in this exact situation. Something they had creatively put together was broken a few years later when a major software vendor (not Microsoft) made a significant change to the Requirements for using their software. The result was the company had to spend far more resources redoing the code again then they would have if they had done it right the first time around instead of looking for the work-a-round that they ended up using.

Just some food for thought. I know that like the rest of us you aren't sitting around each day waiting for something to do and so the idea of another major project is not something you'd be jumping to get.

Good luck.

Kindest Regards,

Just say No to Facebook!
Andrew Peterson
Andrew Peterson
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: 2242 Visits: 750
Points for finding a solution to your design problem.
I hope to never have a client with such a disaster.

The more you are prepared, the less you need it.
michaelwang-772176
michaelwang-772176
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 129
I think that it would be easier to add triggers to populate any changes to a new table. then the view just read from that one new table. no client codes changes.
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