C# failing to drop stored procedure from SQL Server using SMO

[blacklocist]

Hi all,

I have been trying to drop a stored proc for sometime using SMO. I can create with ease but can't seem to drop. Any ideas? I thought it might be permission but for testing used SA account. No luck...

private void btnCreate_Click_1(object sender, EventArgs e)
{
SqlConnection sqlConn = new SqlConnection("Connection String HERE");
Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
Server svrSql = new Server(serverConn);
Database db = svrSql.Databases["DBTest"];
StoredProcedure spNew = new StoredProcedure(db, "StoredProc");
spNew.TextMode = false;
spNew.AnsiNullsStatus = false;
spNew.QuotedIdentifierStatus = false;

spNew.TextBody = "Select * from TestTable";
spNew.Create();

svrSql = null;
}

private void btnDestroy_Click(object sender, EventArgs e)
{

SqlConnection sqlConn = new SqlConnection("Connection String HERE");
Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
Server svrSql = new Server(serverConn);


Database db = svrSql.Databases["DBTest"];
StoredProcedure spNew = new StoredProcedure(db, "StoredProc");
spNew.Drop();
svrSql = null;

}

I keep geting a error of "You cannot execute this operation since the object has not been created". It is created, I can execute it, I can modify it, heck I can even delete it in SQL Management Studio with no problems.

???

[blacklocist]

Man this sucks. It's always after I post a question I then answer it. You have to use the spNew.Refresh(). It believe it asks the SQL Server the current status of the stored proc. Then you are allowed to delete it with ease. :)

private void btnDestroy_Click(object sender, EventArgs e)
{

SqlConnection sqlConn = new SqlConnection("Connection String HERE");
Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
Server svrSql = new Server(serverConn);


Database db = svrSql.Databases["DBTest"];
StoredProcedure spNew = new StoredProcedure(db, "StoredProc");

//HERE IS THE KEY!
spNew.Refresh();

spNew.Drop();
svrSql = null;

}