November 13, 2003 at 2:00 pm
In our development team, we have several developers. Each developer might make some changes to our database table design, stored procedures, user defined functions, and views. Since, each develper's change might overwrite others change. We are in a chaos now. I wonder if there is a tool that could protect SQL scripts as regular files.
Nan Li
CSC
Ray King Mu
Ray King Mu
November 13, 2003 at 2:02 pm
You should get SQLSourceSafe, the tool that integrates SQL Server and SourceSafe. For more information, please visit: http://www.bestsofttool.com
Ray Mu
Ray King Mu
Ray King Mu
November 14, 2003 at 3:15 am
Like Ray, we use SourceSafe. I even use it for any personal projects because it makes it so easy to roll back changes.
In our environment, the database schema is handled by the DBA (me) but the sprocs etc. could be written by myself or any of the component & web dev guys. Therefore we split our SQL scripts as follows:
Database generation
Logins
Tables
Default & Standing Data
Users, Roles & Permissions
Then each view, trigger, UDF or sproc has it's own script (including permissions where appropriate)
This means that if I'm working on myInputSproc another developer can still be working on hisOutputSproc at the same time.
We then have a little app that will pull all of these together into one complete script for release into text or live.
It works for us.
Regards,
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
November 14, 2003 at 9:56 am
Greg : We use the same method as you. But, we don't have an app that can combine all the scripts together from VSS into a single script.
The app you are using, is it a custom built app? If it is a third party application, could you pass on some information on that please?
Thanks.
November 14, 2003 at 10:11 am
quote:
Greg : We use the same method as you. But, we don't have an app that can combine all the scripts together from VSS into a single script.The app you are using, is it a custom built app? If it is a third party application, could you pass on some information on that please?
It's a pretty rudimentary tool written in C++ that one of the guys that used to work here put together. It doesn't actually pull stuff out of VSS - rather it works from files on the local disk.
When I've learnt enough C# I plan to re-write it - and add support for VSS and scripting from the DB too. All I need is the eighth and nineth days of the week to do it in!
. . Greg
Greg M Lucas MCDBA, MCSE
Edited by - gmlucas on 11/14/2003 10:12:00 AM
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
November 14, 2003 at 10:29 am
We also use Sourcesafe - each stored procedure/UDF/view has its own self-contained script file, which will handle dropping existing objects, creating new ones and granting appropriate permissions. We can then diff the files to see what has changed between dev/test/production. Since we keep local copies of files in Sourcesafe, even when not checked out, we can then use a DOS batch file (a custom C++ app is slight overkill!) which basically iterates through all the SQL scripts in its directory and passes the script to isql.exe to run against the required database. You can then go off and have a cup of coffee while you wait for it to finish!
If you're starting source control from scratch, you can use Enterprise Manager to create the individual files, using the Generate SQL Script option - just make sure you tell it to produce the output files as one per object, and in ANSI, not Unicode, otherwise diff programs will usually fail.
Also, if you set up SourceSafe properly, you can add keyword expansion, so by adding the line
-- Last edited by : $Author: $
the next time it gets checked in, the username of the editor is added. There's lots of info about that sort of thing at http://msdn.microsoft.com/ssafe/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply