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.