Could anyone show me how to execute a stored procedure using SMO. I can easily see how to created one and drop one but I cannot see how to execute one.
Thanks very much.
Smo does not have any method to Execute a StoredProcedure. You can however use the Database.ExecuteNonQuery method to run the TSQL to execute the stored procedure.
Thanks,
Kuntal
I have a C# assembly I've created that I wish to run inside SQL Server. Since SQL Server doesn't support SMO inside of it, does anyone have any suggestions on how I might execute a SQL Server job in my assembly?
Thanks - Amos.
Using SMO in CLR:
http://sqlblogcasts.com/blogs/simons/archive/2007/03/14/Using-SMO-from-within-SQLCLR.aspx
Alternatively you can start the job using the sp_start_job procedure.
Jens K. Suessmeyer
http://www.sqlserver2005.de
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,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
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,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
