Email Password Remember me?  Lost your password?

• Download source files - 41.61 KB
• Download demo project - 1.46 MB

Introduction

It is important to backup database for reliability and continuity of database administration. In addition to that, change management of schemas of database should not be an ignorable event in administration tasks. Generally, there are two types of databases, these are production and development. Both of them can be controlled changing schemas of structure. Change management is the basic foundation for security administration. Finally, to do work, I used SQL Server 2005 and Visual Source Safe. I also use SMO for scripting database. I found some work on DMO and Visual Source Safe. However, no work is made for VSS using SMO. I wrote some extra code for adaptation of new features. This article helped to do my study.

Demo Application

Requirements

• SQL Server 2005
• Visual Source Safe
• .NET 2.0

On startup,

• Left panel contains SQL connection parameters.
• Using NT Integrated security or SQL user to connect database.
• To get server list in your network, press “getserver” button.
• After writing the name database server, press “connect” button to connect SQL Server.
• After successful connection, the right panel would be active with the database list.
• After filling VSS parameters, select database that is being scripted and sent to VSS.
• Finally, press “Script and checkin selected databases” button to check in source to VSS.

Using the Code

SMO and Visual Source Safe automation library are added to references of the project before using these objects.

Add the below DLLs to references of the project:

• Microsoft.SqlServer.ConnectionInfo;
• Microsoft.SqlServer.Smo;
• Microsoft.SqlServer.SmoEnum;
• Microsoft.SqlServer.SqlEnum;
• Microsoft.VisualStudio.SourceSafe.Interop;

and deletion temp folder, add:

• System.Management

After script button,

if (m_dbList.Count > 0)
            {
                m_scriptEngine.DatabaseName = (string)m_dbList[0];
                m_dbList.RemoveAt(0);
                backgroundWorker1.RunWorkerAsync();
                System.Windows.Forms.Application.DoEvents();
            }

Program runs the backgroundworker to call script:

m_scriptEngine.Script();

backgroundworker component is used to script table because scripting table is a slow process.

In ScriptEngine class,

ReadandWriteObjectsFromDatabasetoFile(db, workingFolder);

To script file,

foreach (Rule rule in db.Rules)
            {
                filename = rule.Name;
                oname.Text = "_Progressing....:" + filename;
                FileStream file = new FileStream(workingFolder + filename +
		"_rules.sql", FileMode.CreateNew, FileAccess.Write);
                StreamWriter sw = new StreamWriter(file);
                StringCollection sc = rule.Script();
                foreach (string s in sc)
                    sw.WriteLine(s);
                sw.Close();
                file.Close();
            }

calls to every object of database to script temp folder. After locating script to temp folder, every files are sent to the specified VSS.

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

Delete temp folder that includes script files.

Finally

According to new features of the SMO, code can be improved. For the development environment, scheduled task could be used with proper changes of the program.

string dirObject = String.Format("win32_Directory.Name='{0}'", targetDirectory);
            using (ManagementObject dir = new ManagementObject(dirObject))
            {
                dir.Get();
                ManagementBaseObject outParams = dir.InvokeMethod("Delete", null,
                null);
                // ReturnValue should be 0, else failure
                if (Convert.ToInt32(outParams.Properties["ReturnValue"].Value) != 0)
                {
                }
            }

History

• 3^rd April, 2006: Initial post

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

Error when running Roel Schreurs 0:52 22 Jul '08   I have downloaded the demo project and tried to run it. After filling out all input fields, I clicked 'Script and Checkin Selected Databases'. This generates an exception: Unable to cast COM object of type 'Microsoft.VisualStudio.SourceSafe.Interop.VSSDatabaseClass' to interface type 'Microsoft.VisualStudio.SourceSafe.Interop.IVSSDatabase'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{2A0DE0EE-2E9F-11D0-9236-00AA00A1EB95}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). I do not know how to solve this. Sign In·View Thread·PermaLink 5.00/5 Re: Error when running getabdul 22:47 9 Sep '08   If the wrong version of ssapi.dll is registered, VSS Converter crashes with the following error: Unable to cast COM object of type 'Microsoft.VisualStudio.SourceSafe.Interop.VSSDatabaseClass' to interface type 'Microsoft.VisualStudio.SourceSafe.Interop.IVSSDatabase'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{2A0DE0EE-2E9F-11D0-9236-00AA00A1EB95}' failed with HRESULT: 0x80004002 (No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))). Unhandled exception caught. Additionally, VSS Converter displays either the "VSS Converter requires Microsoft Visual SourceSafe 2005 or above” or the “Microsoft Visual SourceSafe 2005 not installed” error message, even though Microsoft Visual SourceSafe 2005 is already installed. Solution VSS Converter requires Microsoft Visual SourceSafe 2005 or above to be installed on the computer on which you are performing migration. You may also experience this problem if in addition to Microsoft Visual SourceSafe 2005, you have installed an older version of Visual SourceSafe. This problem occurs when the older version of ssapi.dll is registered instead of the ssapi.dll that was included with Microsoft Visual SourceSafe 2005. To solve this problem, uninstall the older version of Visual SourceSafe and Microsoft Visual SourceSafe 2005, and reinstall only Microsoft Visual SourceSafe 2005. Sign In·View Thread·PermaLink 3.00/5 Re: Error when running x30 3:03 30 Sep '08   I was getting an error due to Windows Server 2008 (x64) and Visual Studio 2008. Right clicked project and set build from any CPU to x86 only. Once this was done the application no longer crashed due to 32-Bit interop assembly load error. If you are trying to do this without VSS installed on either the development or deployed machine, see http://social.msdn.microsoft.com/Forums/en-US/vssourcecontrol/thread/622abf62-6c25-4010-bce9-13c52e6443d9/[^] YMMV with this answer tho Sign In·View Thread·PermaLink Source code build failed Roel Schreurs 0:44 22 Jul '08   1. When loading the project in VS.Net, the referenced assembly Microsoft.VisualStudio.SourceSafe.Interop was missing. This was solved by referencing the one supplied with the demo project download. 2. The build still gave an error: Cryptographic failure while signing assembly 'C:\Documents and Settings\schreurr\My Documents\Visual Studio 2005\Projects\ScriptTable\obj\Release\ScriptTable.exe' -- 'Error reading key file 'c:\SampleKey.snk' -- The system cannot find the file specified. ' This was solved by navigating to: Solution explorer - Project 'ScriptTable' - Properties - tab 'Signing' and unchecking 'Sign the Assembly'. I probably should have has the assembly in the GAC, for it to work at once. This might be a useful work-around for others too. Sign In·View Thread·PermaLink 5.00/5 A small bug Philipos Sakellaropoulos 23:23 1 Mar '07   Hi thank you for this nice work. I noticed that it processes only the first checked database. I fixed it like this public void ScriptAndCheckIn() { ProcessNext(); } private void ProcessNext() { if (m_dbList.Count > 0) { m_scriptEngine.DatabaseName = (string)m_dbList[0]; m_dbList.RemoveAt(0); if (MessageBox.Show("Process " + m_scriptEngine.DatabaseName + "?", "Script", MessageBoxButtons.YesNo) == DialogResult.Yes) backgroundWorker1.RunWorkerAsync(); System.Windows.Forms.Application.DoEvents(); } } private void backgroundWorker1_RunWorkerCompleted( object sender, RunWorkerCompletedEventArgs e) ..... ProcessNext(); } Sign In·View Thread·PermaLink can any one tell me the link to do all this work in c# mian ghous 3:37 23 Dec '06   can u please tell me some links where some examples could be found in c# please mail me mian_ghous@yahoo.com if u dont mind explore the world of imagination and be known as creative Sign In·View Thread·PermaLink We got a simpler way yonision 16:53 14 Nov '06   Hi, We got something that does just that, and a whole lot more, in a much simpler way: http://www.nobhillsoft.com/Randolph.aspx thanks Sign In·View Thread·PermaLink 1.00/5 Compatibility with other SCC nirisarri 7:02 20 Apr '06   Hi. Is this software only vor VSS or can I use CVS via a SCC proxy to access it? Nicolas. Sign In·View Thread·PermaLink Re: Compatibility with other SCC Levent Soyalp 22:17 30 Apr '06   No, it supports only VSS. Sign In·View Thread·PermaLink wonderful work denny.regehr@acxiom.com 6:43 13 Apr '06   I really like this. nice going! My only request is that you save the field inputs from the previous session. I have to re-enter my VSS location, my vss project name everytime. That's the only thing I would do next. Again, this is a great tool. ~Denny Regehr Sign In·View Thread·PermaLink 2.00/5 Re: wonderful work Levent Soyalp 22:19 30 Apr '06   thanks, if i have time to update program, I will do the cache previous data for controls. Sign In·View Thread·PermaLink 3.50/5

Last Visit: 11:54 23 Nov '09     Last Update: 11:54 23 Nov '09 1