0

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.

???

1
Contributor
1
Reply
2
Views
9 Years
Discussion Span
Last Post by blacklocist
0

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;

}
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.