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 ignorable event in
administration tasks.
In
generally, there are two types of database, these are production and
development. Both of them can
be
controlled changing schemas of structure. Change management is 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 codes for adaptation of new features.
http://www.codeproject.com/cs/database/DBScriptSafe.asp is be of help 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
successfully connection, right panel would be active with list of
database.
-
After
filling VSS parameters and 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
below dll 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
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 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 spesified 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));
To
delete temp folder that includes script files.
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)
{
}
}
Finally,
Accoring
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.
| You must Sign In to use this message board. |
|
| | Msgs 1 to 11 of 11 (Total in Forum: 11) (Refresh) | FirstPrevNext |
|
 |
|
|
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 (1 vote) |
|
|
|
|
|
|
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 (2 votes) |
|
|
|
|
|
|
|
|
|
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 (1 vote) |
|
|
|
|
|
|
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 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 | |
|
|
|
|
|
|
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 (1 vote) |
|
|
|
|
|
|
|
|
|
|
|
|
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 (1 vote) |
|
|
|
|
|
|
|
|
|
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
