Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2005
    Posts
    25

    Unanswered: Problem Dropping Database C#

    Hi,

    i have a problem dropping a database?

    I have a WinForm and i can create DB, create the Tables and drop the DB via buttons.

    When i start myProgram it's no problem to drop the Database.
    Then i can create the database without problems.
    The tables also without problems.

    Now if i try to drop my whole DB, i get the error message:

    SqlException Handle ystem.Data.SqlClient.SqlException: Cannot drop database "XtmsDb" because it is currently in use.


    The code for dropping db:

    public void deleteProjectDB()
    {
    conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";

    sqlStr = "Drop database XTmsDB";

    try
    {
    System.Console.WriteLine("Opening Connection...");
    conn.Open();
    System.Console.WriteLine("Connection opened!!!");

    SqlCommand cmd = new SqlCommand(sqlStr, conn);
    cmd.ExecuteNonQuery();
    System.Console.WriteLine("Database dropped!!!");
    }
    catch (SqlException dropEx)
    {
    System.Console.WriteLine("SqlException Handle :{0}", dropEx.ToString());
    }
    finally
    {
    conn.Close();
    System.Console.WriteLine("Connection closed!!!");
    }
    }


    The code for creating db:

    public void createProjectDB()
    {
    conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
    sqlStr = projectDBStrings.getDBCreateString();

    try
    {
    System.Console.WriteLine("Opening Connection...");
    conn.Open();
    System.Console.WriteLine("Connection opened!!!");
    SqlCommand cmd = new SqlCommand(sqlStr, conn);
    cmd.ExecuteNonQuery();
    System.Console.WriteLine("Database created!!!");

    }
    catch (Exception)
    {
    System.Console.WriteLine("Could not establish Connection!");
    }
    finally
    {
    conn.Close();
    System.Console.WriteLine("Connection closed!!!");
    }
    }


    The code for creating Tables:

    public void createProjectTables()
    {
    conn.ConnectionString = "Data Source=TURM21;Initial Catalog=XtmsDb;Integrated Security=SSPI;";
    ArrayList createList = projectDBStrings.fillProjectSchema();
    SqlTransaction tx;
    SqlCommand cmd = new SqlCommand("", conn);
    IEnumerator createListEnum = createList.GetEnumerator();

    try
    {
    System.Console.WriteLine("Opening Connection...");
    conn.Open();
    System.Console.WriteLine("Connection opened!!!");
    try
    {
    while (createListEnum.MoveNext())
    {
    sqlStr = (String)createListEnum.Current.ToString();
    tx = conn.BeginTransaction();

    cmd.CommandText = sqlStr;
    cmd.Transaction = tx;
    cmd.ExecuteNonQuery();

    tx.Commit();

    }
    System.Console.WriteLine("Schema created!!!");

    }
    catch (SqlException deleteEx)
    {
    System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
    }
    }
    catch (SqlException connectionEx)
    {
    System.Console.WriteLine("SqlConnection Handle : {0}", connectionEx.ToString());
    }
    finally
    {
    conn.Close();

    System.Console.WriteLine("Connection closed!!!");
    }
    }



    Who can help me?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't drop a database while you (or anyone) are connected to it. You will need to switch your connection to a different database before issuing your command.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2005
    Posts
    25
    But i closed the connection. Isn't that enough. How can i change connection to other DB? SOrry, but i'm a beginner.

    Greetz

  4. #4
    Join Date
    Nov 2005
    Posts
    25
    Ok i know hoe to change. But why isnt it enough to close connection?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not an application programmer, but if you close the connection how can you issue ANY command to the server?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like to think I'm an application programmer.

    Just looking at the code.... isn't Crean connected to Master when he tries to drop the db?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Posts
    25
    yes, i'm connecting to master, so i don't know where the problem is.

    I changed to another db

    conn.ChangeDatabase("XTMSTEST2");

    Now it works. But its not a nice solution.

    Anyone knows what the problem is?

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If there are any open connections to the database you cannot drop the database and the error is self-explanatory. In general it is better to use master database whenever dropping any user datatabase.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Nov 2005
    Posts
    25
    Yes but i switched to master in the connection string.

    But I solved my Problem. U have to turn pooling off in connection String.

    On master and on xtms

    Connection string looks like this:

    Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;pooling false


    So thats the solution. But what are the drawbacks if pooling is on false?

    Really a great forum. U always get an answer. Great!!!!!!!!!!!

    Greets

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks like you just GAVE the answer.

    Thanks for posting the solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2003
    Posts
    269
    U can improve the perfomance of ur application by enabling pooling.
    Applications often have different users performing the same type of database access.
    For example, many users might be querying the same database to get the same data. In those cases, the performance of the application can be enhanced by having the application pool, connections to the data source.

    The overhead of having each user open and close a separate connection can otherwise have an adverse effect on application performance.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mallier
    U can improve the perfomance of ur application by enabling pooling.
    Applications often have different users performing the same type of database access.
    For example, many users might be querying the same database to get the same data. In those cases, the performance of the application can be enhanced by having the application pool, connections to the data source.

    The overhead of having each user open and close a separate connection can otherwise have an adverse effect on application performance.
    In most cases, this is quite correct. In this case, since the pooled connection stays in a database that needs to be unused in order to be dropped, turning off the pooling for this particular operation seems to be required, not optional.

    -PatP

  13. #13
    Join Date
    Nov 2005
    Posts
    25
    thx all.

    Thread closed.

  14. #14
    Join Date
    Jan 2009
    Posts
    2

    Exclamation

    Except I tried both of the solutions listed here,

    sqlConn.ChangeDatabase("AnotherDatabase");

    and my ConnectionString already had an Initial Catalog of "master", and I still could not delete the "in-use" database. I am the only user, and I've seen it suggested to run the command

    ALTER DATABASE myDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    -- well, this doesn't help when you only have one account accessing it. It will tell you it is still in use by the one account.

    Setting pooling=false in a ConnectionString to the old database and to master did not work for me.

    Any other way to logout a user programmatically from a database? I was thinking I could delete the database after a restart of SQL services, but the services will not start after they have been stopped in that manner (using ServiceController, in the System.ServiceProcess namespace). I don't get it.

    -Tom

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Connect to the server with Query Analyzer, or SSMS. Run sp_who2, and see who else is connected to the database in question. The SQL Server tools open all sorts of connections, and you might even be surprised at what your own applications are doing.

    EDIT: One other question. Are people dropping databases so often that they need to have a program to do it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •