Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Save Your Stored Procedures


Save Your Stored Procedures

Author
Message
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/saveyourstoredprocedures.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
smclarnon
smclarnon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 245
Andy,

Why not treat database code in the same manner that application source code is treated and work from a real version control system? We are using Visual Studio 2003 and although I'm not particularly fond of the editor, it allows us to set up database projects and have all source code in VSS. I had one of our developers create a Visual Studio add-in that creates a database build script from the source files in a project. It is working out very well thus far.



ErikEJ
ErikEJ
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 42

You could add a date/time stamp to the file name, to allow developers to go "back in time"

Great article





jwainz
jwainz
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 40
I agree, I've always put each SProc in a file and checked it in and out of VSS. Great way to maintain history. Query Ananlyzer's ability to edit SProcs directly, instead of loading a file and running it in, just gets folks in trouble.



gerry anderson
gerry anderson
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 7
This is great. I just finished making some table and proc changes, and have been putting off making the changes to source safe, since I knew that the source safe files were not up to date and I really needed to do all the objects again. I took your code and created a VB app, added a combo box to select which objects to script and bam project complete.
richteel
richteel
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 23

Great Article, definitely a keeper.

I would like to reply to ErikEJ's comment about adding a date/time stamp to the file name. This is not a good idea as was mentioned in the article. The purpose of this script is to produce files which can be used with a version control tool such as StarTeam or Rational ClearCase. By adding the date to the file name or in the content of the file would defeat this purpose. If you are not using version control software and wish to keep separate snapshots of the database then the best thing would be to create a folder by date of better yet zip the files and add the date to the name of the zip file.

I plan to modify this script to script other objects as well.





Malcolm Leach
Malcolm Leach
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 71

Storing the drop/create scripts for stored procedures and, indeed, every other type of database object in a source control/configuration management system should be standard practice for any development shop. You do it without question for application code, why not for database code?

And there's the rub...

Database code isn't like normal application code. You can't just build a brand new database and replace production with it.

But what if you had a magic black box that could analyse the individual drop/create scripts under source control for changes and apply those intelligently to a production database with no loss of data?

This was the question we aimed to solve three years ago and the 'black box solution' is now a product called DB Ghost (www.dbghost.com).

To release your latest set of code simply label it all in your SCS and extract it to disk. DB Ghost will then:

1. Build a brand new database from the source scripts. This ensures that a) no syntax errors exist and b) no dependency errors have been introduced. This is the equivalent of a daily build but for databases.

2. Compare a target database to the newly built source database and modify the target to match it EXACTLY, no more, no less.

What you are effectively left with is a target database that matches a known, labelled set of source.

The benefits of this approach are many but the main one is that developers work with check out/check in for ALL databases objects just as they do for other application code. This means that our approach is perfectly aligned with configuration management best principles as it deals with source code.

There is no other tool available today that does this.

The only downside? We need to feed our families so we charge for it.

How much is your time worth and how much of it do you spend on troubleshooting deployment and script collisions?

DB Ghost could make those issues go away forever.



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
Nice, Steve! That's very helpful, actually; and it keeps all the formatting, too. Great way to rip a database. Once it's in SCC you can deploy like normal, but this is great to get started, or to make sure you SCC repository is up to date.

Seems like with a little SQLDMO you could create simple scripts for backing up your functions and views as well. Probably backing up tables AND their indexes/constraints would be the hardest. I'll have to look into this more...

PS: It's great to see that you can offer free code like this and still feed your family.

Signature is NULL
Jim Underwood
Jim Underwood
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1
What is the best way to execute this script?
Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
Anything that runs VB; or you can compile it to run on it's own.

Personally, I used DTS (ActiveX step); that was the fastest.

cl

Signature is NULL
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search