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 Tuesday, December 9, 2008 7:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:25 AM
Points: 1,430, Visits: 1,025
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



Post #616106
Posted Tuesday, December 9, 2008 7:14 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, 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. :D


Paul DB
Post #616114
Posted Tuesday, December 9, 2008 7:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, Visits: 258
Henrik, you type fast. ;)

Paul DB
Post #616115
Posted Tuesday, December 9, 2008 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:36 AM
Points: 31,362, Visits: 15,823
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
Post #616127
Posted Tuesday, December 9, 2008 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 27, 2012 8:54 AM
Points: 6, 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!
Post #616256
Posted Tuesday, December 9, 2008 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 15, 2008 12:55 PM
Points: 14, 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.
Post #616274
Posted Tuesday, December 9, 2008 9:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 26, 2009 9:05 AM
Points: 44, Visits: 11
Clever solution to a problem due to a bad design. Thanks for sharing.


Post #616279
Posted Tuesday, December 9, 2008 11:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:11 AM
Points: 892, Visits: 1,560
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!
Post #616359
Posted Tuesday, December 9, 2008 1:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:23 AM
Points: 206, Visits: 402
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.
Post #616472
Posted Tuesday, December 9, 2008 3:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:08 PM
Points: 2, Visits: 113
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.
Post #616549
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse