SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Henrik Staun Polsen / How To Avoid Msg 106 / Latest Posts

RE: How To Avoid Msg 106

henrik staun poulsen — Tue, 05 Jan 2010 00:18:09 GMT

Dear all,According to j.summers in http://www.sqlservercentral.com/Forums/Topic618362-32-1.aspx#bm841385Books OnLine for 2008:[b]Tables per SELECT statement : Limited only by available resources[/b] More details on "Maximum Capacity Specifications for SQL Server" can be found here:http://msdn.microsoft.com/en-us/library/ms143432.aspxSo now we could skip the UDF solution, and use nested views instead.Nice to know we have more options now.Best regards,Henrik

RE: How To Avoid Msg 106

henrik staun poulsen — Wed, 10 Dec 2008 00:58:42 GMT

Dear all,First of all, thank you for the nice words. Writing up the article was a lot more work than I anticipated, but also a lot easier than I feared. My english teacher would have been surprised, but proud. Steve was really helpfull here. Phil; yes, we tried nested views. But as __Celko__ found out (after a "few" mails), views are no more than "macros", that are resolved into base tables at compile time.Yes, our solution do read the catalogue to get the list of tables needed. But it is some of our own tables that we read, so it did not make sense to show that.andreq1, michaelwang; yes, that is one of the solutions that Erland Sommarskog lists, on one of his pages; [url=http://www.sommarskog.se/dynamic_sql.html#Sales_yymm.]http://www.sommarskog.se/dynamic_sql.html#Sales_yymm.[/url] If you have not read, printed and rehearsed his entire homepage, I think you should. Here it is mandatory reading.Andrew; to have such a client is not a disaster, it is work. Albeit lots of it. ;-) This client is friendly and is focused on obtaining the best solutions, so I’m sure we will end up with something near perfect at some point. But not without loosing the main focus; providing value for the (end) customer.Best regards,Henrik

RE: How To Avoid Msg 106

michaelwang-772176 — Tue, 09 Dec 2008 15:03:00 GMT

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.

RE: How To Avoid Msg 106

Andrew Peterson-472853 — Tue, 09 Dec 2008 13:30:51 GMT

Points for finding a solution to your design problem. I hope to never have a client with such a disaster.

RE: How To Avoid Msg 106

YSLGuru — Tue, 09 Dec 2008 11:08:51 GMT

[quote][b]henrik staun poulsen (12/9/2008)[/b][hr]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[/quote]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.

RE: How To Avoid Msg 106

jpeden — Tue, 09 Dec 2008 09:45:35 GMT

Clever solution to a problem due to a bad design. Thanks for sharing.

RE: How To Avoid Msg 106

andreq1-726318 — Tue, 09 Dec 2008 09:39:45 GMT

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.

RE: How To Avoid Msg 106

Phil Morris-454316 — Tue, 09 Dec 2008 09:20:17 GMT

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!

RE: How To Avoid Msg 106

Steve Jones - SSC Editor — Tue, 09 Dec 2008 07:24:46 GMT

Henrik, thanks for the story and solution. Good job!

RE: How To Avoid Msg 106

Paul DB — Tue, 09 Dec 2008 07:15:35 GMT

Henrik, you type fast. ;)

RE: How To Avoid Msg 106

Paul DB — Tue, 09 Dec 2008 07:14:19 GMT

[quote][b]OzarkMountainMan (12/9/2008)[/b][hr]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.[/quote]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

RE: How To Avoid Msg 106

henrik staun poulsen — Tue, 09 Dec 2008 07:09:37 GMT

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

RE: How To Avoid Msg 106

OzarkMountainMan — Tue, 09 Dec 2008 06:58:20 GMT

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?

RE: How To Avoid Msg 106

ta.bu.shi.da.yu — Tue, 09 Dec 2008 05:43:14 GMT

[quote][b]henrik staun poulsen (12/9/2008)[/b][hr]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[/quote]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.

RE: How To Avoid Msg 106

Frank Hamersley — Tue, 09 Dec 2008 05:06:12 GMT

Original post mashed the code frag...[quote][b]Frank Hamersley (12/8/2008)[/b][hr]... 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.[/quote]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.

RE: How To Avoid Msg 106

Frank Hamersley — Tue, 09 Dec 2008 05:05:52 GMT

[quote][b]henrik staun poulsen (12/9/2008)[/b][hr]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. [/quote]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.

RE: How To Avoid Msg 106

henrik staun poulsen — Tue, 09 Dec 2008 04:44:53 GMT

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

RE: How To Avoid Msg 106

icocks — Tue, 09 Dec 2008 04:09:53 GMT

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 :)

RE: How To Avoid Msg 106

magarity kerns — Tue, 09 Dec 2008 01:46:32 GMT

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.

RE: How To Avoid Msg 106

henrik staun poulsen — Mon, 08 Dec 2008 23:54:39 GMT

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

RE: How To Avoid Msg 106

Frank Hamersley — Mon, 08 Dec 2008 22:15:33 GMT

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!!!

How To Avoid Msg 106

henrik staun poulsen — Mon, 08 Dec 2008 22:04:27 GMT

Comments posted to this topic are about the item [B]How To Avoid Msg 106[/B]