olivier pieri's WebLog

SQL, SQL Server, ADO.NET and other data topics

Create a stored procedure with SMO

Create a stored procedure with SMO

Rate This
  • Comments 2

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a stored procedure, we imagine that we have the code of a stored procedure in an embeded resources file. (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0).

For this sample you need to use the following name space :

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

 

And here is the code of the sample :

StoredProcedure mySP;

string myResourceFile;

mySP = new StoredProcedure(sqlserver.database, "createNewYear");

myResourceFile = "MyAssemblieName.StoredProc.sql";

StreamReader sr = new StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(myResourceFile), System.Text.Encoding.Default);

mySP.TextBody = sr.ReadToEnd();

mySP.Create();

  • We have been using StringBuilder :( I Could not figure out why no DOM for T-SQL. Glad to see it.

    What does SMO stand for?
  • SMO stand for SQL Server Management Object. It is the equivalent of SQL-DMO, but very optimized and using the .Net technology.

    SMO is going to be available for SQL Server 2005.
Page 1 of 1 (2 items)