Executing .sql file using SMO

Hi all,

Is it possible to execute a .sql file using SMO. like in old days we used to use osql.exe to execute the sql files

Thanks in advance

Rujith

Hi,

you don′t need SMO for that,executing scripts can be done via a *normal* sqlconnection (untested script, just wroite down in notepad)

static void Main(string[] args)
{

if (args[0] == null)
{
Console.WriteLine("No file passed to the program.");
return;
}

if (!File.Exists(args[0].ToString()))
{
Console.WriteLine("File does not exist.");
return;
}

try
{
FileStream file = new FileStream(args[0].ToString(), FileMode, FileAccess);
StreamReader sr = new StreamReader(file);
string Commands = sr.ReadToEnd();
sr.Close();
file.Close();

SqlCommand cmd = new SqlCommand(Commands);
cmd.Connection.ConnectionString = "Data Source=.;Integrated Authentication=true";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(string.Format("Exeception occured: {0}", ex.Message));
}

}

HTH, Jens Suessmeyer.

|||

Hi

This code exevuted for some .sql file

but not for all .

how to do that ?

?

|||

Hi,

what do you mean by *all* ?

-Jens.

|||

I mean

that i do have some script file

One for TAble

One for View

One for Stored procedures and so on...

I also have developed same type of code.

But this code runs for only that table script file only

but not for othres;

specially

Code gives error where GO command is there in script;

|||Either replace the GOs in the script or slit the string into a string array which can be executed one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

|||

Thnx Artur laksberg

but u have not mentioned what is this "server" object.

please tell about class to which this object does belongs to.

Waiting for yor reply

uday (Emersion)

|||Hi ermersion,
take a look inte namespace

Microsoft.SqlServer.Management.Smo
There is a server class which hold the method Arthur was talking about.
The namespace can be used by referencing the assembly directly (installed in the GAC) or by a file reference on
Microsoft.SqlServer.Smo.dll
SomeTime Ago, I created a small app for a user mapping, the server class is also used there, perhaps you can get a feeling from this:
http://www.sqlserver2005.de/SharedFiles/UserMappingwithSMO.zip
HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Artur laksberg MSFT wrote:

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

That's what we did. It read the script line by line. When GO was encountered, it submitted what was accumulated to SQL Server using an ADO SQL command object ExecuteNonQuery method. It works fine.

Not familiar with the object you're talking about. We'll have to check that out because it would be more effecient to send scripts that contain multiple batches. However, that requires our customers to upgrade to SQL 2005 (or install the add-on's) to get SMO.

Joe