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: 2190 Visits: 1225
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
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 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
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

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

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

Group: Administrators
Points: 81485 Visits: 19207
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 Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

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



YSLGuru
YSLGuru
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2352 Visits: 1665
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
SSC Eights!
SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)

Group: General Forum Members
Points: 913 Visits: 729
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
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

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