Ninja's_RGR'us (4/8/2010)
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?
It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.
I have None. I can either call the vendors to change it or create a db besides it and maintain the new field in the new db manually. Anything else voids the contract.
Now the fun part with a new db is that we have 30 incorporations. Currently 3 of them are in that ERP system whereas the rest of them will gradually make the transition.
The really fun part here is that there is only 1 single database. Everytime you add a new company to the system a new set of ±1200 tables is created like so :
dbo.[<name for the cie here>$Invoice Header]
So any script, index, mod or whatever needs to be applied to n number of companies, ideally without changing the scripts I wrote. I've already gotten around that little PITA for indexes, but for keeping dbs in sync that'd be another little project all in itself. I know I can use 2k8 change management to accomplish this but I've never played with it and I'm not sure it's allowed either.
Anyhow if I just run a job once a day I can easily go around that one at that time.
Well not at the moment, there are only 4 cies, 3 of them really the transactional ones. The 4th one is just there to increase the buying power and discounts. You also need to account for 1 test and 1 model cie in there too... which makes it already to around 7200 tables. The final total will be 5 times that in a couple years assuming the plans don't change.
When I hit the + in SSMS to expand all the tables I have plenty of time for a bathroom break!
In VS 2008 to build reports for asp net (not the bids version, but the full pro version), the wizard drills down to table defs, keys and column types for all tables right off the bat. That one takes a couple hours to load at the moment and generates a trace file of a couple 100 MBs with tracing only batch ending and rpc calls and nothing else... loads of fun.