Create a Stored Procedure with SQL SMO

I am currently rewriting an existing data generator which was written in Sql dmo, but now must be in Sql Smo.


Creating a stored procedure is a pretty straightforward affair. Add your header references:

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

And here's the most commonly used code I've found online:


StoredProcedure mySP;
String procString = "PUT YOUR PROCTEXTHERE";
mySP = new StoredProcedure(sqlserver.database,"SomeProcName");
mySP.TextBody = procString;
mySP.Create();

Something important to note here is that very often you will get back a nasty exception if you're not careful:

Create failed for StoredProcedure 'dbo.SomeProcName'.

WTF?!  Read the exception more carefully and you'll find the following explanatory note:

To accomplish this action, set property ExecutionContext.

Hmmmm.  What the hell is this, then?  The problem is the context of who is attempting to create this procedure on your database.  Okay, so we attempt to set the execution context to, say the owner, but the moment you try to set the executioncontext on your stored procedure, you will get another exception!


Writing property ExecutionContext of StoredProcedure 'dbo.SomeProcName' is not allowed when TextMode has been set.




Why is anyone's guess, but I haven't found a satisfactory answer anywhere on MSDN or the web.  I decided to take a more tai chi approach to this problem and dodged the stored proc object entirely:


     ServerConnection conn = new ServerConnection("ServerName", "UserName", "Password");
     Server myServer = new Server(conn);
     String procString = "create procedure [dbo].[SomeProcName]() as begin select top 1 from someTable;  end";
     Database myDb = myServer.Databases["DatabaseName"];


     myDb.ExecuteNonQuery(procString);

Here's hoping this saves you some headache as it did me.