Introduction

Working with SQL Server databases in a huge group or enterprise environments with developers stepping out of line once in a while to make ad-hoc changes to tables, stored procedures or adding jobs and DTS will be a nightmare for admins and developers alike. Tracking changes to database structure is one of the most important piece of database development. Although sincere efforts are made in team development, crunching timelines and lack of resources tend to catch up with the developers once in a while to make an ad-hoc change to the database. Visual Studio and other client tools offer integration of SQL Server database with Visual SourceSafe. But many projects may not be able to afford the license or training time on these new tools. The attached DB Script Safe tool scripts and stores changes into Visual SourceSafe using SQL DMO and Visual SourceSafe automation. (Important note: This tool cannot be a substitute for regular database backups which include data.) This article attempts to demo the process of scripting and storing in SourceSafe. The attached code can be used by the developer community to cater their needs.

Demo application

On startup, the application displays the above dialog box to configure parameters.

Give "blank" for database login and password to log into SQL Server using Windows login.

Press <Connect> to connect to the database. On successful connection, the list of non-system databases are displayed and the <Script and Checkin Selected Databases> button is enabled.

Browse and select a srcsafe.ini file to connect to Visual SourceSafe and give credentials and a project under which the scripts should be stored. Scripts will be stored according to the database and database object type, under <project>/<database>/<object type>/ directory inside SourceSafe.

It is required that the <project> directory exists in SourceSafe. Complete source code is included with the demo.

Using the code

SQLDMO and Visual SourceSafe automation library are added to the references of the project before using these objects. These are found under the COM tab of 'Add Reference' dialog box as 'Microsoft Source Safe 6.0 Type Library' and 'Microsoft SQLDMO Object Library'. This will create 2 namespaces SourceSafeTypeLib and SQLDMO which are used in the application. The SQLDMO.SQLServerClass is the top most class which is used to connect to a database instance, and once successfully connected is retained in the application as a member variable. The SQLServerClass.Databases collection is used to populate the list box.

The TransferClass contains what objects are to be scripted and options when generating the script, like including headers or not. Most of the options are from SQLDMO_SCRIPT_TYPE enumeration. In the demo application code, this class is populated in ScriptEngine.ScriptDatabase(Database db) method. The TransferClass also has ScriptMessage and ScriptTransferPercentComplete events which are subscribed to by the DBScriptSafe class to display status messages.

The checkout, checkin and other operations on the SourceSafe are deliberately kept at the end of the sequence. Just in case the user tries to abort the scripting, we will have a chance to terminate the whole operation without touching the SourceSafe. Watch the flags and the sequence in the following code segment for the SourceSafe:

All the operations are done recursively from the project item. That is why the VSSFLAG_RECURYES is used in all the calls.

First, the existing scripts are checked out without getting the files.

All the changed scripts are checked in. This will check-in only new scripts into the SourceSafe.

The UndoCheckout call next will undo checkout of any script which is missing from the last check-in.

This leaves with adding all the new scripts which is done by the Add call at the end.

This simple 4 line code does all the work for us and demonstrates the power of Visual SourceSafe automation.

Enhancements suggested to demo project

Some of the enhancements to the demo project to make it more effective can be:

Making the program command line driven so that it can be scheduled to run under Windows Scheduler for periodic backups

Scripting the database itself

Scripts for Jobs and DTS at server level.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

This tool seems to work for the most part, but one major concern is that the Views folder tends to have scripts generated for any other database objects referenced within the views themselves. If I have a view called vw_Users and inside it I reference a table called Users I will get a script for both the view and for the table referenced. For each additional table or udf or sp referenced I will get a script added for it.

Any ideas on how to fix this other than deleting the scripts once they are created? We'd like to have this fixed on the front end so the scripts aren't generated incorrectly in the first place.

a) the connection parameters. Make sure if you are using an instance name or a user name when SQL Server is in windows authentication mode only.

b) I was using SQL Server 2000 SQL-DMO to generate my Interop reference. If you are using SQL Server 2005 remove the existing SQLDMO reference from the project, add it again from your COM library, recompile and run.

I've seen several tools for integrating SQL code (SPs, UDFs, triggers...) with SourceSafe. I am looking for something alike but for SourceGear's Vault[^] source control software. Vault is a software born to be a SourceSafe replacement, but likewise, does not implement this kind of feature. And other party tools, as SQLSourceSafe[^], are just focused on SourceSafe.

Does anybody know if such tool does exist?
Is there any open source project trying to implement this functionality?
Is anybody out there with the same need? (I'd really like to contribute) Maybe we can do something quick and dirty to have versioning control about SQL development on a Vault Repository.

When I tried it created duplicated file in VSS. For example in VSS *.TAB files were found under VIEWS as well as under TABLES folder and this behaviour was not consistant. But similar kind of issue happens anytime I run this tool.

I'm a development manager/DBA that handles deployment of an enterprise database application. Along with myself, there are 4 other developers on my team that are routinely in SQL Analyzer building and tweaking stored procedures, functions, etc. Since even SQL Server 2005 seems to have not even an afterthought with regards to object source control (and I don't count that crude "Database Project" real source control), I've been looking for a tool that can simply scan a database, generate DDL scripts, and slam them into SourceSafe. I figure if I do this for each build just prior to deployment, even if I don't have the names of the developers making the changes, or every little change they make between builds at least I'll have something that I can roll back to if things go wrong.

I looked at a number of commercial products, including SQLSourceSafe, SQLDBControl, and a couple others, and none do what this simple tool does... They all seem to get lost in enhanced Query Analyzers and other frilly nonsense to realize that the database tools that come with SQL server don't respect the custom tool's notion of object check-in/check-out exclusivity, and changes they make outside their tool wind up getting lost.

Anyway, great work! And thanks especially for providing your code to codeproject.

This is a product named SQLSourceSafe is available on the market. It integrated both SQL Server and SourceSafe. It have more functionalities than it is described there. For more information, please visit:

older SQL replication stored procedures like sp_replsync (which as flaged as user objects but in reality are system ones) cannot be scripted and break the code.... I am trying to find a way to exclude these objects when the GenerateScript function is being executed for all objects of certain type.... any ideas?

a) If you try to script more than one database, an exception is generated when the temp directory is deleted. Solved this by moving the Directory Create & Delete outside of the for loop (Script() method).

b) If the VSS sub project for the DB doesn't exist the following exception is thrown

Nick Horrocks wrote:a) If you try to script more than one database, an exception is generated when the temp directory is deleted. Solved this by moving the Directory Create & Delete outside of the for loop (Script() method).

Nick, this is a problem with the operating system holding up the directory and file handles. I can see it on Win2K, but not on Windows Advanced Server (my development platform).
Just remove the CreateDirectory() call in the ScriptEngine() constructor and try. It should solve the problem, otherwise you already have a fix

Nick Horrocks wrote:b) If the VSS sub project for the DB doesn't exist the following exception is thrown ......

This problem was with Visual Source Safe 5.0 which takes only the last directory name in the path. In VSS 6.0 this is fixed and we can give the complete path.
If you are using VSS 5.0 use the following code snippet in the exception block in place of NewSubproject(..) line

Sriram Chitturi wrote:Nick Horrocks wrote:
b) If the VSS sub project for the DB doesn't exist the following exception is thrown ......

This problem was with Visual Source Safe 5.0 which takes only the last directory name in the path. In VSS 6.0 this is fixed and we can give the complete path.
If you are using VSS 5.0 use the following code snippet in the exception block in place of NewSubproject(..) line

I have VSS 6d (build 9848)

Tried your code but got exceptions where the project existed:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in DBScriptSafe.exe
Additional information: An item with the name $/Database/aladdinstats already exists