Source control for stored procedures in SQL Server 2005

Posted by in Sql Server category on for Beginner level | Views : 18452 red flag

.NET Developers working with Visual Studio 2005 IDE are well aware of the fact that source control for all the classes, pages, dlls can be integrated just by configuring source control in VS 2005 IDE.
However, DBA's need to track the modifications made to the stored procedures and/or schema changes. For this, SQL Server Management Studio (Developer and later versions) support source control not only for stored procedures but also for schema definitions. Having averred that only T-SQL can be saved in .sql files and no graphical user interface can be made available.

This article aims at providing the max information to the readers on how this can be achieved from the initial database design. Meaning, right from the first stored procedure created in the database.

1. Open the SQL Server Management Studio (Developer or later versions).
2. Connect to the intended SQL Server.

3. Choose 'SQL Server scripts' from the “New Project” dialog box. Forget not to choose 'Add to Source Control'

4. Click on OK button.

5. Provide VSS credentials in the Visual SourceSafe Login popup.

6. Choose the location in the VSS where the stored procedures will be saved.

7. From the View menu select 'Solution Explorer'

8. One should view 'Queries' folder under the current solution. Right click and select 'New Query'. SQL Server would prompt to enter the appropriate credentials to connect to the intended server. Provide appropriate credentials.

9. A blank query editor window will be displayed with the default filename, SqlQuery1.sql

10. Develop the source code for intended sp and save the file. For the reasons of demonstration the default filename has been used. Try to save the file using the stored procedure name.

11. Right click on the .sql file created just now, and choose check-in option from the list. This will display the following pop up.

12. Click 'Check In' button. We are up and going.... :)

13. Next time we open the solution all the files available in the VSS will be displayed and its just again an usual check-out and check-in process. The objects (stored procedures) will be saved in the database when F5 is pressed. But for the changes to be available in the VSS we need to save the file which has the stored procedure and check-in the file back to VSS.

14. In my next article, I will demonstrate how source control can be made available for a database which already has some stored procedures but needs source control from the current point in time.

Happy development and safe source control.
Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Deeraj Chakravarthy
Member Level: Starter
Member Status: Member
Member Since: 10/29/2007 1:00:04 AM
Country: India

Qualification: BCA, MScIS, MDSE, PMI-ACP

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)