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


Version Control your Stored Procedures


Version Control your Stored Procedures

Author
Message
Jon McGhee
Jon McGhee
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 597
We had a similar solution to the problem of developers changing stored procedures without any version control. The only version control we had was limited to the previous backup.

The solution: I built a .net console app, used SMO to script each object to a text file, then checked in each file into visual source safe. The app runs every night, does a check-in for multiple servers/databases. We setup a source safe project for every database. When we do a release, we apply a label on the whole project/database.

Now when a developer makes a change to a stored procedure, that change is checked into source safe that night. Using source safe we can track the change history on each procedure, table, trigger, etc.
mike.renwick-894639
mike.renwick-894639
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 190
I personally haven't found a great deal of info on how to script objects using SMO which is why I think people end up doing slightly scary things like querying syscomments and other arcane solutions. From the snippets on the net we've been able to sort of work SMO out but it always seems to require a few extra steps of understanding to make use of, e.g. smo is a c# library seemingly, accessible from powershell, but could equally be rolled into a CLR storedproc.

I've hated having to make uninformed choices as to where run this sort of stuff from or how to architect it to "best practice" quality standards.

Does anyone have a book to recommend that delves into the powershell scripting side of sql server more?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19446 Visits: 32324
mike.renwick-894639 (5/6/2010)
I personally haven't found a great deal of info on how to script objects using SMO which is why I think people end up doing slightly scary things like querying syscomments and other arcane solutions. From the snippets on the net we've been able to sort of work SMO out but it always seems to require a few extra steps of understanding to make use of, e.g. smo is a c# library seemingly, accessible from powershell, but could equally be rolled into a CLR storedproc.

I've hated having to make uninformed choices as to where run this sort of stuff from or how to architect it to "best practice" quality standards.

Does anyone have a book to recommend that delves into the powershell scripting side of sql server more?


Yep, "SQL Server 2008 Administration with PowerShell" by Anathakumar Muthusamy & Yan Pan.

I'm sure I mispelled one of those names.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Phil Factor
Phil Factor
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 2953
anything that involves a table schema change for example kind of makes the whole approach a bit complex and prone to problems


Right on the nail. If you can crack the problem of source control for tables, then you're probably home and dry. This is a problem that is ridiculously hard. Tables don't, and most certainly can't, have a source of the same type as a C# source, or a stored procedure. You can't compare tables merely from scripts. I can generate the same table using scripts that are really quite different in the way that they represent the table and its associated components, and I can reverse-engineer a table-build script in a number of different ways. The only effective way to detect a difference between two databases is to compare the metadata. Tables have a whole lot of dependent columns, indexes, constraints and triggers. How do you relate this lot together in the source-control model? Is a table, together with all its dependent components, one single object? If so, how come you can change it without requiring to check out, or 'reverse-engineer', the 'source'? How do you deal with something as potentially complex as a trigger or check constraint, which surely needs a separate identity in source control? (..and so on.. and so on ...) So often, folks move over from traditional programming and wonder why source-control is all so different with SQL Databases. It can be done, but it takes a great deal of head-scratching to get it right.


Best wishes,

Phil Factor
Simple Talk
BIMind
BIMind
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 135
I agree with a lot of users here that Powershell and SMO are much much better choices.

Mike.Renwick..i will look into your Powershell code...its a great start.

Jon Mcghee...is it possible you could share your code?

For other folks who are not inclined on spending too much time on PS/SMO, SQL 2008 has a built in task which can generate SP script file. A bunch of clicks but it gets the job done for light usage DBs

It is also available as a free utility for previous versions.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 1843
irozenberg (5/6/2010)
Just a small suggestion - please consider EXTENDED properties for DB objects (including Stored Procedures), by using them in a key-value fashion you could avoid a lot of text processing.

Regards from DownUnder.


I'm not sure what you mean?
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 1843
jcaradocdavies (5/6/2010)
Just a small concern...

As far as experience and memory serves, sp_helptext is not a reliable source for recreating SQL server objects. SQL management objects are the preferred source.

Changes to an object and/or metadata may render sp_helptext outdated - the script used to create the original object may not reflect the object in its current state. I seem to remember a case where the sp_helptext record was blatantly incorrect after a (?) designer edit.

Does anyone have a concise view of when this may occur, and whether stored procedures are for practical purposes immune to these cases?


The only problem I've found is that on rare occasions it does odd things with line breaks.
These become obviously evident when you try to run the script as the script will fail.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 1843
mike.renwick-894639 (5/6/2010)
I also had similar issues with sp_helptext and eventually found a way of scripting objects using
Microsoft.SqlServer.Management.Smo.Scripter in Powershell.. this has the advantage that it handles all types of objects including everything from logins to linked-servers, mail profiles, etc, and can take advantage of easy iteration possible in powershell.

I've written a skeleton (and, disclaimer; is not warranted in any way) ps script that captures relevant source/schema stuff in SQL Server, if anyone's interested. Obviously it can be adjusted to taste for extended properties etc (which I turn off by default) and should be tweaked to expose specific properties relevant to only certain objects.

Link here
http://wp.me/pje2P-l

I also have a wrapper script that iteratively runs it for each database on the server, and a server-objects script that captures mail profiles, accounts etc, for anyone interested.

Regards,
Mike


The advantage of the method I've used is that it also creates a standard header for the script and in the version I use I add a call to another stored proceddure that writes to a ChangeCatalogue table with details of when the stored procedure was installed, the version and who ran the script.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 1843
mike.renwick-894639 (5/6/2010)
I personally haven't found a great deal of info on how to script objects using SMO which is why I think people end up doing slightly scary things like querying syscomments and other arcane solutions. From the snippets on the net we've been able to sort of work SMO out but it always seems to require a few extra steps of understanding to make use of, e.g. smo is a c# library seemingly, accessible from powershell, but could equally be rolled into a CLR storedproc.

I've hated having to make uninformed choices as to where run this sort of stuff from or how to architect it to "best practice" quality standards.

Does anyone have a book to recommend that delves into the powershell scripting side of sql server more?

I don;t know much about SMO either. I think it's something I'll look into though. :-)
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 1843
Phil Factor (5/6/2010)
anything that involves a table schema change for example kind of makes the whole approach a bit complex and prone to problems


Right on the nail. If you can crack the problem of source control for tables, then you're probably home and dry. This is a problem that is ridiculously hard. Tables don't, and most certainly can't, have a source of the same type as a C# source, or a stored procedure. You can't compare tables merely from scripts. I can generate the same table using scripts that are really quite different in the way that they represent the table and its associated components, and I can reverse-engineer a table-build script in a number of different ways. The only effective way to detect a difference between two databases is to compare the metadata. Tables have a whole lot of dependent columns, indexes, constraints and triggers. How do you relate this lot together in the source-control model? Is a table, together with all its dependent components, one single object? If so, how come you can change it without requiring to check out, or 'reverse-engineer', the 'source'? How do you deal with something as potentially complex as a trigger or check constraint, which surely needs a separate identity in source control? (..and so on.. and so on ...) So often, folks move over from traditional programming and wonder why source-control is all so different with SQL Databases. It can be done, but it takes a great deal of head-scratching to get it right.


Yes that's one of the tougher areas of deployment of changes. We have a sequentially numbered set of 'incremental' scripts for DDL changes. All scripts are rerunable so for example if a new column is to be added they check for existance before trying to add the column, this means that script scn be rerun without causing errors e.g.:

IF NOT EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'
AND COLUMN_NAME = 'mycol1')
BEGIN
ALTER TABLE dbo.myTable
ADD mycol1 int NULL
...



This means that if the incremental files are all run in the right order the correct changes to the database should be made.
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