C# RSS C# RSS

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

Please support our C# advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!
Thread Solved
Reply

•
•
Join Date: Apr 2006
Posts: 88
Reputation: blacklocist is an unknown quantity at this point 
Solved Threads: 2
blacklocist blacklocist is offline Offline
Junior Poster in Training

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

 
0
  #1
Oct 18th, 2007
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) 
  1.  
  2. private void btnCreate_Click_1(object sender, EventArgs e)
  3. {
  4. SqlConnection sqlConn = new SqlConnection("Connection String HERE");
  5. Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
  6. Server svrSql = new Server(serverConn);
  7. Database db = svrSql.Databases["DBTest"];
  8. StoredProcedure spNew = new StoredProcedure(db, "StoredProc");
  9. spNew.TextMode = false;
  10. spNew.AnsiNullsStatus = false;
  11. spNew.QuotedIdentifierStatus = false;
  12.  
  13. spNew.TextBody = "Select * from TestTable";
  14. spNew.Create();
  15.  
  16. svrSql = null;
  17. }
  18.  
  19. private void btnDestroy_Click(object sender, EventArgs e)
  20. {
  21.  
  22. SqlConnection sqlConn = new SqlConnection("Connection String HERE");
  23. Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
  24. Server svrSql = new Server(serverConn);
  25.  
  26.  
  27. Database db = svrSql.Databases["DBTest"];
  28. StoredProcedure spNew = new StoredProcedure(db, "StoredProc");
  29. spNew.Drop();
  30. svrSql = null;
  31.  
  32. }

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.

???
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2006
Posts: 88
Reputation: blacklocist is an unknown quantity at this point 
Solved Threads: 2
blacklocist blacklocist is offline Offline
Junior Poster in Training

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

 
0
  #2
Oct 18th, 2007
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) 
  1. private void btnDestroy_Click(object sender, EventArgs e)
  2. {
  3.  
  4. SqlConnection sqlConn = new SqlConnection("Connection String HERE");
  5. Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
  6. Server svrSql = new Server(serverConn);
  7.  
  8.  
  9. Database db = svrSql.Databases["DBTest"];
  10. StoredProcedure spNew = new StoredProcedure(db, "StoredProc");
  11.  
  12. //HERE IS THE KEY!
  13. spNew.Refresh();
  14.  
  15. spNew.Drop();
  16. svrSql = null;
  17.  
  18. }
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the C# Forum
Thread Tools Search this Thread



.net access algorithm alignment array bitmap box c# c#gridviewcolumn check checkbox client combobox communication control conversion csharp custom database datagrid datagridview dataset datetime decryption degrees draganddrop drawing encryption enum excel file focus form format forms function gdi+ grantorrevokepermissionthroughc#.net hospitalmanagementsystem image input install java label list localization mandelbrot marshalbyrefobject math messagebox mouseclick mysql netcfsvcutil.exe numeric operator path photoshop php picturebox pixelinversion platform plotting pointer polynomial post programming properties radians read regex remote remoting richtextbox server sleep socket sql statistics string stringformatting study sun table teamsuite text textbox thread time timer update usb usercontrol validation visualstudio webbrowser whileloop winforms wpf wpfc# xml
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC