Many different ways. Below is some C# code snippet through invoking osql.exe
(there are some references to classes not shown in the code. But they are not
essential for the purpose here.)
Linchi
/*******************************************************************
RunOsqlScript()
*******************************************************************/
static string RunOsqlScript(string script_name)
{
string msg;
string script_file = script_name;
string log_file = script_name + ".log";
try
{
if (Path.GetDirectoryName(script_name) == String.Empty)
{
script_file = Config.script_path + "\\" + script_name;
log_file = Config.log_path + "\\" + log_file;
}
else
{
log_file = Config.log_path + "\\" +
Path.GetFileName(script_name) + ".log";
}
// Use SqlConnectionStringBuilder to get the individual
connection string values
SqlConnectionStringBuilder scb = new
SqlConnectionStringBuilder(Config.connect_string);
string osqlArgs = null;
if (scb.IntegratedSecurity == true)
{
osqlArgs = "-S" + scb.DataSource + " -E -n -e -l 30 -w 1000
-t " + Config.query_timeout.ToString() +
" -i" + script_file;
}
else
{
osqlArgs = "-S" + scb.DataSource + " -U" + scb.UserID + "
-P" + scb.Password +
" -n -e -l 30 -w 1000 -t " +
Config.query_timeout.ToString() +
" -i" + script_file;
}
using (StreamWriter sw = new StreamWriter(log_file)) {
sw.WriteLine("{0} Starting to run osql.exe",
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
sw.WriteLine("osql.exe " + osqlArgs + Environment.NewLine);
msg = LaunchExe("osql.exe", osqlArgs);
sw.WriteLine("{0} {1} {2}",
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"),
"[" + Thread.CurrentThread.Name
+ "]",
msg);
if (ScanForError(msg))
{
throw new Exception("**Err: the osql output of " +
script_name + " contains error message.");
}
}
}
catch(Exception e)
{
Log.Append(e.ToString());
throw new Exception("**Err: RunOsqlScript() failed with script
file " + script_name + ".");
}
return msg;
} // RunOsqlScript()
/**************************************************************************
LaunchExe()
**************************************************************************/
private string LaunchExe(string exeName, string argLine)
{
string output = "";
try
{
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = exeName;
psi.Arguments = argLine;
psi.UseShellExecute = false;
psi.ErrorDialog = false;
psi.RedirectStandardOutput = true;
psi.RedirectStandardError = true;
psi.CreateNoWindow = true;
Process p = new Process();
p.StartInfo = psi;
p.Start();
output = p.StandardOutput.ReadToEnd();
output += Environment.NewLine;
output += p.StandardError.ReadToEnd();
p.WaitForExit();
}
catch
{
throw;
}
return output;
} // LaunchExe
/**************************************************************************
ScanForError()
**************************************************************************/
private bool ScanForError(string msg)
{
try
{
Regex re = new Regex(@"\s*Msg:", RegexOptions.IgnoreCase);
return re.IsMatch(msg);
}
catch(Exception e) {
Console.WriteLine("**Err: ScanForError() found Msg.");
Console.WriteLine(e.ToString());
}
return false;
} // ScanForError()
Post by Nathan SokalskiI am connecting to a remote SQL Server database from Visual Studio .NET
2005. I have an sql script located on my machine containing the sql code I
want to use to create several tables, and want to execute it. How can I do
this from Visual Studio .NET 2005? I do not like designing the database
using the designer because sometimes the designer uses different words,
phrases things differently, and causes me to be unsure exactly what the
properties of the fields in my database are. If anybody know how to do this,
please let me know. Thanks.
--
Nathan Sokalski
http://www.nathansokalski.com/