Executing an sql script on a remote database from Visual Studio .NET 2005

[Nathan Sokalski]

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

 

[William \(Bill\) Vaughn]

An SQL script (by definition) can be executed from anywhere--but it's
limited in what it can do in a single operation. On the other hand, a SQL
batch (which is composed of a set of scripts) can perform a whole series of
operations--but only one script at a time. So, it's not at all unusual to
create or maintain a database using scripts and batches. Generally that's
what DBAs do. However, nowadays SQL Server Management Studio (SSMS) knows
how to generate the tables through a well-understood user interface that
precludes the need for batches and scripts. If, however, you still want to
execute these, you can write your own code to execute the scripts or batches
or simply open SSMS and execute them directly from there--right from the
file containing the properly formatted script or batch.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Nathan Sokalski]

I think you are misunderstanding my problem. My only way to connect to my
SQL Server database (at least that I know of and have the software for),
which is located on a server at my webhost (which means I am working from a
remote location and connecting using the internet), is by using Visual
Studio .NET 2005. My sql script (hopefully I am using the correct term here)
that is a text file that basically just creates the tables, using the CREATE
TABLE command, like the following (my script is obviously longer, but this
is the only command I use):

CREATE TABLE table1
(id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(20),
birthday SMALLDATETIME);

All I need is to be able to execute scripts like this, but the only tool I
have that I can use to connect is Visual Studio .NET 2005. Hopefully this
clears up my situation.

 

[William \(Bill\) Vaughn]

Sure. Okay, assuming you have connectivity to the remote server, the
simplest approach is to use SQLCMD. This program (that comes with all
editions of SQL Server) is a command-line tool designed to execute scripts.
Yes, I wrote a Visual Basic.NET application to simulate what SQLCMD does,
but again, it's in an unpublished EBook that should be out soon. It's not
that hard to do, but why reinvent the wheel? And, if you have connectivity
(the ability to connect to the remote server via the net/web you can run SQL
Server Management Studio on your system and connect to the remote system to
run scripts or anything you want.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Guest]

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