none
Cannot attach database file - In use error message

    Question

  • Hi there,

    I have a clickonce app that uses the connection string 'Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydb.mdf;Database=mydb;Trusted_Connection=Yes;Integrated Security=True;Connect Timeout=60' to attach the physical mdf at runtime. When an application update is completed by clickonce, the application is restarted.  As I understand, clickonce will copy the mdf to the new data directory for the new version of the application.  When my application starts up after the update, it complains that the database is already attached to another file (the old mdf). 

    Specifically, i get this error:

    System.Data.SqlClient.SqlException: Database 'C:\Users\[username]\AppData\Local\Apps\2.0\Data\RYE192OJ.E39\MCTMJLJD.ENX\scan..tion_6c86dc47e08f800f_0002.0000_00bfc966235ba6c4\Data\mydb.mdf' already exists. Choose a different database name.
    Cannot attach the file 'C:\Users\[username]\AppData\Local\Apps\2.0\Data\RYE192OJ.E39\MCTMJLJD.ENX\scan..tion_6c86dc47e08f800f_0002.0000_3393505633b30179\Data\mydb.mdf' as database 'mydb'.

    Do you have to explicity detach the database before application restart or is there a standard pattern about getting around this? Or is my approach incorrect.

    Thanks,

    Luke
    Thursday, May 28, 2009 8:36 PM

Answers

  • Just remove the following from your connection string: Database=mydb. You should not specify a database name when connecting to a user instance, just let the system autoname the database.

    Regards,
    Mike
    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Luke Hutton Friday, May 29, 2009 8:58 PM
    Friday, May 29, 2009 5:30 AM
    Moderator

All replies

  • It looks like already one Userinstance is using this file or this file is attached to express instance as normal db. Check this link
    http://technet.microsoft.com/en-us/library/bb264564(SQL.90).aspx
    The other main issue with user instances occurs because SQL Server opens database files with exclusive access. This is necessary because SQL Server manages the locking of the database data in its memory. Thus, if more than one SQL Server instance has the same file open, there is the potential for data corruption. If two different user instances use the same database file, one instance must close the file before the other instance can open it. There are two common ways to close database files, as follows.

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, May 29, 2009 1:19 AM
    Moderator
  • Just remove the following from your connection string: Database=mydb. You should not specify a database name when connecting to a user instance, just let the system autoname the database.

    Regards,
    Mike
    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Luke Hutton Friday, May 29, 2009 8:58 PM
    Friday, May 29, 2009 5:30 AM
    Moderator
  • Thank you for the replies.....

    Yes, that worked, I also added the User Instance = True

    Luke
    Friday, May 29, 2009 8:58 PM
  • I was having the same difficulty.  I'd connect to the database via some C# code, like this:

    string Connstr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Folder1\\MCNamespace\\library.mdf;Initial Catalog=library;Integrated Security=True;Asynchronous Processing=False;Connect Timeout=30;User Instance=True;Context Connection=False";

     

    using (SqlConnection cnn = new SqlConnection (ConnStr))

        {

     

        using (SqlCommand cmd = new SqlCommand ("GetMemberFromMemberid", cnn))

            {

            cmd.CommandType =

    CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue (

    "@membernum", member_num);

            cmd.Parameters.AddWithValue (

    "@child", "N");

            cnn.Open ();

     

            using (SqlDataReader reader = cmd.ExecuteReader ())

                {

     

                while (reader.Read())

                    {

                    AM.FirstName = reader[

    "FName"].ToString ();

                    AM.MiddleInitial = reader[

    "MI"].ToString ();

                    AM.LastName = reader[

    "LName"].ToString ();

     

     

                    }

     

                }

            }

        }

    The Connection would work OK.  But then I'd attempt to Bind a control using a DataAdapter and when I attempted to do that, I received the above error.  So I followed the instructions above and removed the "Initial Catalog=library", and the connection failed entirely.  The above C# code wouldn't connect at all.

    If anyone can shed some light, that'd be great.

    Chewdoggie
    Saturday, July 18, 2009 2:02 AM
  • First, I don't recommend use of the (soon to be deprecated) User Instance=True approach. It can cause several issues that are tough to solve.
    Because I'm monitoring this thread via an NNTP bridge, I don't see the error you're getting, but one of the requirements of using AttachDBFilename is that you must explicitly specify the initial catalog (default database) as you have discovered.
    I suggest you take another approach. Create your database and attach it to an instance of SQL Express using the Server explorer (or better yet SQL Server Management Studio Express) and open it without the AttachDbFilename or User Instance keywords. Your connection string would look like this (after you've attached the MDF to the SQLEXPRESS instance running on your system):
     
        "Server=.\SQLEXPRESS; Initial Catalog=Library; Integrated Security=True;"
     
    The rest of the options are unnecessary (they simply restate the default behaviors). If you take this approach you'll discover that lots of things just run faster and work better. Of course, when you deploy the application, you'll need to include a script to attach the working MDF to the client's instance of SQL Express. I describe all of this in my book in great detail.

    --
    __________________________________________________________________________
    William R. Vaughn
    President and Founder Beta V Corporation
    Author, Mentor, Dad, Grandpa
    Microsoft MVP
    (425) 556-9205  (Pacific time)
    Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    http://betav.com  http://betav.com/blog/billva

    William (Bill) Vaughn -- Mentor, Author, Dad and MVP
    Monday, July 20, 2009 4:35 PM
    Moderator
  • Thank you for the reply. 

    Development with C# 2008 Express and Visual Web Developer 2008 Express is very frustrating.  In both environments, I have my Server Explorer to display the contents of the db, but sometimes, for no reason, I can't view the data b/c it says "login failed for MeMyself\Chewdoggie", other times, I can view all tables and stored procs just fine.  I have no idea when it's going to allow access or barf.

    However, I'm going to have to get back to you regarding this error b/c I am now having issues with syncronizing my dll's in the Visual Web Developer which is preventing me from even reaching the Data Access Layer entirely.  I keep getting errors stating, "There is no source code available for the current location".  I've deleted and re-added my references, re-built the solution and cannot get this error to go away.

    Thanks again for your expertise.  I'll be returning to the connection error shortly.

    Chewdoggie
    Tuesday, July 21, 2009 4:38 PM