| Author |
Topic |
|
mgp
Starting Member
9 Posts |
 Posted - 2007-12-15 : 10:15:35
|
| Hi, I'm new to c# and sql2005 express. I'm getting an error on the simplest of activities. From C#, i create a database, create a table, then try to delete the database and get a database busy error. I think its because sql has some connections to the database its not releasing. I have already tried closing the connection, disposing of the object and forcing garbage collection for the c# app to no avail.can someone explain to me wat is going on?Here is the only function i am calling for c#. I pass in T-SQL command strings such as 'Ceate Database myDB','Create Table myTbl','Drop Database myDB', etc.. :private bool ExecuteSQLNonQry(string sqlStr){ SqlConnection myConn = new SqlConnection(ConnectionString); SqlCommand SqlCmd = new SqlCommand(sqlStr, myConn); try { myConn.Open(); SqlCmd.ExecuteNonQuery(); myConn.Close(); return true; } catch(Exception e) { MessageBox.Show(e.Message.ToString()); return false; }} |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 11:12:12
|
you can't drop myDB if anyone is actively connected to it. you have to switch context to another db first.
elsasoft.org |
 |
|
|
mgp
Starting Member
9 Posts |
Posted - 2007-12-15 : 11:13:22
|
| An Update.I can create the db then drop the db all day long. So the problem must be coming in after the create table command. |
|
|
|
mgp
Starting Member
9 Posts |
Posted - 2007-12-15 : 11:16:15
|
| Jesseman, ty for your input. I should have mentioned that i adjust the connection string before each command sent. For work on the db i attach to the master db. For work on the table i attach to the respective db. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 11:26:45
|
can you post the actual error msg text?
elsasoft.org |
|
|
|
mgp
Starting Member
9 Posts |
Posted - 2007-12-15 : 17:17:21
|
| The actual message is: Cannot drop the database 'mydb' because it is currently in use.Here are the results of more testing.If i close down the c# app and reopen it, i can drop the existing db.This leads me to think that it is the app that is holding the connection open.However i cannot see how because the class i use to talk to sql gets instantiated and destroyed in private function calls.Also the SQL Connection gets instantiated and destroyed within a private method inside the class instance.Also worth noting is again, i can create and drop the table all day no errors, but i am attached to the master db. And i can create and drop the table all day long with no errors. But after i attach to the 'mydb' db the connection remains so that i can no longer drop the db.I know, I'm confusing myself. LoL. But thats why i'm asking fo help. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 17:22:13
|
try executing this sql on the SqlCommand that you used to connect to mydb:use master; drop database mydb;
elsasoft.org |
|
|
|
mgp
Starting Member
9 Posts |
Posted - 2007-12-15 : 17:36:08
|
| Thanks again.I get this message now:Cannot drop the database 'mydb' because it is currently in use. Changed database context to 'master'.Perhaps if you are familiar with c#, you would like me to email you the VisualStudio solution. It is a very basic test app. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 17:44:50
|
try this then:foo.ExecuteSQLNonQry("use master");foo.ExecuteSQLNonQry("drop database mydb");
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
mgp
Starting Member
9 Posts |
Posted - 2007-12-15 : 18:26:57
|
Jezemine, you rock.Thank you you sticking with this subject!The link provided some valuable insight.This is the command that freed the sql processes.ExecuteSQLNonQry("ALTER DATABASE mydb SET RESTRICTED_USER WITH ROLLBACK immediate")Evidently setting the user type to restricted and allowing for immediate processing is what i needed.This makes me question what was locking the processes. A simple t-sql "Create Database mydb" must default the user mode to multiple?I will research that further. In the meantime, its working, thanks again!!!! |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 20:50:46
|
I think multi-user is the default. look in BOL. at the syntax for CREATE DATABASE for details.
elsasoft.org |
|
|
|
|
database busy, whats going on?