October 20, 2010 at 12:15 pm
I have inherited a development database that has databases like vcData00, vcData01, etc.
The corresponding production database has database names like ipZone00, ipZone01, etc.
Poor planning.
Anyway, I have to write stored procedures that pull data from a master database (vcData00/ipZone00) into each of the other databases. The typical process is to create the stored procedures on the development database and test them, then push them out to the production database.
So far, I am doing hand editing and search/replace on the stored procedures before creating or altering them on production.
Has anyone else dealt with this in a way that enabled creating and altering of the stored procedures without editing? I can't think of anything except to throttle the devs and make them change it. But that isn't an option at this point.
Thansk!
October 20, 2010 at 12:57 pm
This forum is provided by a company that sells a tool that will do what I think you are describing.
Scroll up, click on Redgate's banner, and see if "SQL Compare" and/or "SQL Data Compare" will fit your need. I really appreciate SQL Compare for migrating dev to production.
October 20, 2010 at 1:39 pm
Throttle the devs, then see if you can set up synonyms in the dev environment that will give you what you need.
A database in Dev named "ipZone00" that has nothing in it but synonyms that point at the appropriate objects in the "vcData00" database (or whatever the relationship is really) should allow you to build procs that will run in both dev and production without editing in between.
With the right queries of the sys views, you should be able to generate single-pass scripts that will create the needed synonyms for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 1:47 pm
Never even thought of that for DB names. Will have to give it a try.
OK, I see what you mean by addressing the objects. Can't do synonyms on databases.
Thanks!
October 20, 2010 at 2:11 pm
You may also want to look into the process that caused the dev and prod databases to get out of sync.
No point in fixing the problem just to see it happen again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply