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 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