Click here to Skip to main content
15,861,172 members
Articles / Programming Languages / SQL
Article

Tool to script and store SQL Server objects in SourceSafe using SQL DMO and Visual SourceSafe automation

Rate me:
Please Sign up or sign in to vote.
4.17/5 (27 votes)
5 May 20044 min read 168.7K   2.3K   67   28
This useful tool demonstrates the power of SQL DMO to generate scripts for tables, stored procedures and other database objects, and how to integrate with Visual SourceSafe using automation.

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

DBScriptSafe Demo application

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

  1. Press <Get Servers List> button to fetch the list of SQL Server database instances.
  2. Give "blank" for database login and password to log into SQL Server using Windows login.
  3. 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.
  4. 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.

C#
DBListView.Items.Clear();
foreach (SQLDMO.Database db in m_sqlServer.Databases)
    if (! db.SystemObject)
        DBListView.Items.Add(db.Name);

The SQLDMO.Database class has a method called ScriptTransfer() which is at the heart of the script generation scheme. The signature of this method is:

C#
ScriptTransfer(TransferClass transfer, 
    SQLDMO_XFRSCRIPTMODE_TYPE type, string path);

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.

C#
ScriptEngine.s_transfer.StatusMessage +=
    new TransferSink_StatusMessageEventHandler(s_transfer_StatusMessage);
ScriptEngine.s_transfer.ScriptTransferPercentComplete +=
    new TransferSink_ScriptTransferPercentCompleteEventHandler(
                s_transfer_ScriptTransferPercentComplete);

Coming to the Visual SourceSafe part, the project item which is root/project is either fetched, and if not found, is created by the following code:

C#
VSSItem VssDbItem = GetVssItem(vssPath, VSSItemType.VSSITEM_PROJECT);

Once this is established, the working folder for the SourceSafe project is determined by the type of objects being scripted which can be seen by the switch statement in the code.

C#
for (Db_Types dt = Db_Types.Defaults; dt<= Db_Types.Views; dt++)
{
    workingFolder = m_workingFolder + @"\" + db.Name + @"\" + dt.ToString();
    System.IO.Directory.CreateDirectory(workingFolder);
    switch(dt)
    {
    case Db_Types.Defaults:
        s_transfer.CopyAllDefaults = true;
        GenerateScript(s_transfer, workingFolder, db);
        s_transfer.CopyAllDefaults = false;
        break;
    case Db_Types.Functions:
        s_transfer.CopyAllFunctions = true;
        GenerateScript(s_transfer, workingFolder, db);
        ....
        ....
    ...
    }
}

All the script is generated by the following helper function in ScriptEngine class:

C#
private void GenerateScript(TransferClass tc, string folder, Database db)
{
    db.ScriptTransfer(tc,
    SQLDMO_XFRSCRIPTMODE_TYPE.SQLDMOXfrFile_SingleFilePerObject,
            folder);
}

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:

C#
VssDbItem.Checkout("DBScriptManager Automatic Checkout",
        workingFolder,
        (int)(VSSFlags.VSSFLAG_GETNO | VSSFlags.VSSFLAG_RECURSYES));

VssDbItem.Checkin("DBScriptManager Automatic Checkin",
        workingFolder,
        (int)(VSSFlags.VSSFLAG_DELTAYES| VSSFlags.VSSFLAG_RECURSYES|
                VSSFlags.VSSFLAG_DELYES));

VssDbItem.UndoCheckout(workingFolder,
        (int)(VSSFlags.VSSFLAG_GETNO | VSSFlags.VSSFLAG_DELYES|
            VSSFlags.VSSFLAG_RECURSYES));

VssDbItem.Add(workingFolder, "Created by DBScriptManager",
        (int) (VSSFlags.VSSFLAG_DELYES | VSSFlags.VSSFLAG_RECURSYES));

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

  1. First, the existing scripts are checked out without getting the files.
  2. All the changed scripts are checked in. This will check-in only new scripts into the SourceSafe.
  3. The UndoCheckout call next will undo checkout of any script which is missing from the last check-in.
  4. 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.

A list of licenses authors might use can be found here


Written By
Architect
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralVS 2005 Version Pin
softty27-Dec-09 23:33
softty27-Dec-09 23:33 
GeneralVSS 2005 Pin
MikeRun12-Sep-07 5:57
MikeRun12-Sep-07 5:57 
Generala product that does just that Pin
yonision19-Jul-07 3:13
yonision19-Jul-07 3:13 
GeneralPlease Help... Pin
mbmartyns17-Jan-07 5:33
mbmartyns17-Jan-07 5:33 
Generala tool that does all that for you - and more Pin
yonision14-Nov-06 15:43
yonision14-Nov-06 15:43 
Generalcant get it to work Pin
Paulcurrivan23-Feb-06 0:24
Paulcurrivan23-Feb-06 0:24 
GeneralRe: cant get it to work Pin
Sriram Chitturi23-Feb-06 2:13
Sriram Chitturi23-Feb-06 2:13 
GeneralRe: cant get it to work Pin
Paulcurrivan23-Feb-06 2:50
Paulcurrivan23-Feb-06 2:50 
GeneralRe: cant get it to work Pin
Sriram Chitturi23-Feb-06 17:54
Sriram Chitturi23-Feb-06 17:54 
GeneralRe: cant get it to work Pin
Paulcurrivan23-Feb-06 23:26
Paulcurrivan23-Feb-06 23:26 
QuestionSQLSourceSafe-like for Vault Pin
Mario A Vasquez9-Feb-06 11:47
Mario A Vasquez9-Feb-06 11:47 
GeneralDupliation of scripts. Pin
Girish Patil19-Dec-05 20:09
Girish Patil19-Dec-05 20:09 
GeneralGreat, simple tool! Pin
Brian Brennan29-Nov-05 10:37
Brian Brennan29-Nov-05 10:37 
GeneralOpen source Pin
ckcatplague3-Nov-05 20:38
ckcatplague3-Nov-05 20:38 
GeneralRe: Open source Pin
Sriram Chitturi4-Nov-05 1:01
Sriram Chitturi4-Nov-05 1:01 
GeneralRe: Open source Pin
ckcatplague4-Nov-05 4:25
ckcatplague4-Nov-05 4:25 
GeneralThis is great!!! Pin
kevnord14-Jun-05 11:31
kevnord14-Jun-05 11:31 
GeneralRe: This is great!!! Pin
neil_b9-Dec-05 20:16
neil_b9-Dec-05 20:16 
GeneralSQLSourceSafe Pin
RayMu20021-Feb-05 9:24
RayMu20021-Feb-05 9:24 
GeneralSQL replication procedures make code fail Pin
javiguillen25-May-04 9:34
javiguillen25-May-04 9:34 
GeneralOS and VSS version problems Pin
Sriram Chitturi12-May-04 6:21
Sriram Chitturi12-May-04 6:21 
GeneralErrors Pin
Nick Horrocks11-May-04 22:51
Nick Horrocks11-May-04 22:51 
GeneralRe: Errors Pin
Sriram Chitturi12-May-04 6:20
Sriram Chitturi12-May-04 6:20 
GeneralRe: Errors Pin
Nick Horrocks12-May-04 10:38
Nick Horrocks12-May-04 10:38 
GeneralArticle updated Pin
Sriram Chitturi7-May-04 3:02
Sriram Chitturi7-May-04 3:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.