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

Expand Post »

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...

C# Syntax (Toggle Plain Text)
 
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;
 
}
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.

???

Similar Threads

filling combobox with data stored in a table in sql server in VB.NET
Updated : Simple ASP.Net Login Page in ASP.NET
How to connect a SQL stored procedure to a windows application? in VB.NET
Must declare the scalar variables in storedprocedure in sql 2005 in MS SQL
ASP.net/Stored Proc & Login Verification in ASP.NET

blacklocist

Reputation Points: 10

Solved Threads: 2

Junior Poster in Training

Offline

87 posts
since Apr 2006

Permalink

Oct 18th, 2007

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

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.

C# Syntax (Toggle Plain Text)
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;
 
}
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;

}

blacklocist

Reputation Points: 10

Solved Threads: 2

Junior Poster in Training

Offline

87 posts
since Apr 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: Compiling Problems code snippets

Next Thread in C# Forum Timeline: Anyone using dotnet 3.0

DaniWeb Message

Cancel Changes

User Name
Password