locked
An attempt to attach an auto-named database for file failed. RRS feed

  • Question

  • User1031313367 posted

    Hi,

    I recently installed VS Web Developer Express Ediition and SQL Server Express and I'm doing the How To tutorials and I can't finish the basic data access walkthrough:

    http://msdn.microsoft.com/en-US/library/tw738475.aspx

    Because I get an error like this (link for complete error below):

     An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    http://pastecode.com/6196

    When I am setting up the data source for the gridview tool, everything works fine and the test query works fine, but then when I try to actually test the output in the browser with Cntrl+F5 I get this error.

    I have been trying for 48 hours to find a solution on my own. I have googled, I have searched everything and I just am at a point where I think I need to ask someone for help.

     

    Please help.

    Sunday, October 8, 2006 8:34 PM

Answers

  • User1224304770 posted

    In other words, make sure it has not been attached in any SQL instance, and no other application is using it to create a connection.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 9, 2006 1:38 AM
  • User630118385 posted

    First of all, you aren't crazy or a no brained soul. This is not you. This is quirkiness in how Microsoft (and I am their biggest fan), in trying to make things easier, has somehow made CHANGES to your SQL environment difficult. I am working on sorting this out and creating some kind of a flow chart to troubleshoot all of this, but here are some insights in the meantime (Gurus - please correct me where I am wrong on any of this):

     * ASPNETDB.MDF seems to occur for every ASP.NET application and should appear in the |DataDirectory| - which is normally(?/automatically?) APP_DATA

     * I think it gets automatically created the first time you run your app (because it seems to me to not be there, then suddenly appear) but it may be under program control - I don't know

    * if you have an instance that has been named ASPNETDB or ASPNETDB.MDF in your Sql database, connecting a new one will fail as a duplicate, so use Sql Management Studio to rename any such instances to something unique, like "c:\Webs\PartnerPortal\Northwind Traders\App_Data\ASPNETDB.MDF" instead of just "ASPNETDB.MDF"

    * In my environment I have several named instances of Sql Server, including: "STARTEAM" (a Borland created named instance), "OFFICESERVER" "SQLEXPRESS" "MSSQLSERVER" and "MICROSOFT##SSEE" (Mobile edition). However, none of these are useful to me. I have uninstalled SQLSERVER (SQL 2000) and SQLEXPRESS. The one I NEED does not appear - which is the unnamed instance - SQL Enterprise 2005. To access that all I do is enter the server name, ip or .\

    * I was using Microsoft Expression Web to edit the default connection string (and this is identical in Server Explorer in VS 2005 also). It asks you the provider. I was using the defaul and could not enter .\ as my Sql Server. I only had the other useless named instances and could not successfully connect. SOOOO I changed the provider. I had two choices:

    "Microsoft Sql Server" and "Microsoft Sql Server Database File" (along with "Sql Server Mobile Editiion", etc).

    * I changed my selection FROM "Microsoft Sql Server" TO "Microsoft Sql Server Database File". This allowed me to edit the server to be .\ when I went back to my connection string

    * Then I changed the provider BACK and now I had the choice I needed! I think that the choice are coming from the registry and it got out of sync

    Anyway, now I can connect. I think that connectionString updates the registry, where the problems lie.

    *************************************************
    Here is the skinny on troubleshooting connections:
    *************************************************

    (1) have you run aspnet_regsql from a command prompt at the c:\windows\Microsoft.net Framework v2xxx directory?

    (2) did you create a virtual directory for your app and enable Asp?
    (I have a separate memory pool for each version of Asp - another matter)

    (3) did you change the Asp settings to the right connection string? The important things are: .\SQLEXPRESS or .\ or your named instance. If you do not use SQLEXPRESS then User Instance must be False

    (4) are all of your instances of ASPNETDB.mdf named uniquely? (Ie: include a path)

    (5) does IIS_WPG have sufficient rights to your web directory (this may not be true if you put your webs in your "My Documents")

    (6) In VS2005, did you go to Websited-Asp config?

    (7) Do you have the right settings in your Web.config?

    (8) You must not have extraneous copies of Web.config on your path - they will be automatically referenced in the project. Ie, creating a backup in a subdir, is trouble.

    (9) did you set your Default.aspx as the Startup page? (and the right project as the startup project if you have multiple projects?)

    (10) did you use Server Explorer to try a different provider, update the registry choices of servers and to include your unnamed instance, per above?

    Does Machine.config figure into this? 

    What have I missed or misstated?

    To be honest, having done all of this, I still seem to have a web that I am still troubleshooting the connection for!


    ****
    If anyone can correct, add or improve on the above, please do. What i would love to see is a Visio flowchart in a .pdf that could be emblazoned here. I can be reached at my gmail account of norisksoftware at gmail . com
    AND I am looking for contract work doing almost anything technical (web, C#, consulting, Cobol, EDI..) that pays well. I was out of the country for 2 1/2 years and my business kind of dried up. Go figure.

    Bill Ross

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 25, 2006 12:43 PM

All replies

  • User1224304770 posted
    Make sure the file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf failed is not being used by other process. If it is the data file of some database in the SQL instance, you can't attach it by using AttachDBFileName property in the connection string.
    Sunday, October 8, 2006 10:15 PM
  • User1031313367 posted
    How do I do make sure it's not being used by an other process?
    Sunday, October 8, 2006 11:07 PM
  • User1224304770 posted

    In other words, make sure it has not been attached in any SQL instance, and no other application is using it to create a connection.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 9, 2006 1:38 AM
  • User1031313367 posted

     

    Thanks for your help so far. 

    But, how do I do that? How can I tell if another application is using 'it' to create a connection? I am using Visual Web Developer and SQL Server Management Studio Express and SQL Server Configuration Manager. Also when I hit 'Cntrl+F5' while in Visual Web Developer the page opens up in Firefox (my default browser).

     I am not using the database for anything else. I have no idea how to monitor SQL instances. I am just beginning to learn these things and am working with tutorials that aren't working for me.

    Monday, October 9, 2006 1:52 AM
  • User630118385 posted

    First of all, you aren't crazy or a no brained soul. This is not you. This is quirkiness in how Microsoft (and I am their biggest fan), in trying to make things easier, has somehow made CHANGES to your SQL environment difficult. I am working on sorting this out and creating some kind of a flow chart to troubleshoot all of this, but here are some insights in the meantime (Gurus - please correct me where I am wrong on any of this):

     * ASPNETDB.MDF seems to occur for every ASP.NET application and should appear in the |DataDirectory| - which is normally(?/automatically?) APP_DATA

     * I think it gets automatically created the first time you run your app (because it seems to me to not be there, then suddenly appear) but it may be under program control - I don't know

    * if you have an instance that has been named ASPNETDB or ASPNETDB.MDF in your Sql database, connecting a new one will fail as a duplicate, so use Sql Management Studio to rename any such instances to something unique, like "c:\Webs\PartnerPortal\Northwind Traders\App_Data\ASPNETDB.MDF" instead of just "ASPNETDB.MDF"

    * In my environment I have several named instances of Sql Server, including: "STARTEAM" (a Borland created named instance), "OFFICESERVER" "SQLEXPRESS" "MSSQLSERVER" and "MICROSOFT##SSEE" (Mobile edition). However, none of these are useful to me. I have uninstalled SQLSERVER (SQL 2000) and SQLEXPRESS. The one I NEED does not appear - which is the unnamed instance - SQL Enterprise 2005. To access that all I do is enter the server name, ip or .\

    * I was using Microsoft Expression Web to edit the default connection string (and this is identical in Server Explorer in VS 2005 also). It asks you the provider. I was using the defaul and could not enter .\ as my Sql Server. I only had the other useless named instances and could not successfully connect. SOOOO I changed the provider. I had two choices:

    "Microsoft Sql Server" and "Microsoft Sql Server Database File" (along with "Sql Server Mobile Editiion", etc).

    * I changed my selection FROM "Microsoft Sql Server" TO "Microsoft Sql Server Database File". This allowed me to edit the server to be .\ when I went back to my connection string

    * Then I changed the provider BACK and now I had the choice I needed! I think that the choice are coming from the registry and it got out of sync

    Anyway, now I can connect. I think that connectionString updates the registry, where the problems lie.

    *************************************************
    Here is the skinny on troubleshooting connections:
    *************************************************

    (1) have you run aspnet_regsql from a command prompt at the c:\windows\Microsoft.net Framework v2xxx directory?

    (2) did you create a virtual directory for your app and enable Asp?
    (I have a separate memory pool for each version of Asp - another matter)

    (3) did you change the Asp settings to the right connection string? The important things are: .\SQLEXPRESS or .\ or your named instance. If you do not use SQLEXPRESS then User Instance must be False

    (4) are all of your instances of ASPNETDB.mdf named uniquely? (Ie: include a path)

    (5) does IIS_WPG have sufficient rights to your web directory (this may not be true if you put your webs in your "My Documents")

    (6) In VS2005, did you go to Websited-Asp config?

    (7) Do you have the right settings in your Web.config?

    (8) You must not have extraneous copies of Web.config on your path - they will be automatically referenced in the project. Ie, creating a backup in a subdir, is trouble.

    (9) did you set your Default.aspx as the Startup page? (and the right project as the startup project if you have multiple projects?)

    (10) did you use Server Explorer to try a different provider, update the registry choices of servers and to include your unnamed instance, per above?

    Does Machine.config figure into this? 

    What have I missed or misstated?

    To be honest, having done all of this, I still seem to have a web that I am still troubleshooting the connection for!


    ****
    If anyone can correct, add or improve on the above, please do. What i would love to see is a Visio flowchart in a .pdf that could be emblazoned here. I can be reached at my gmail account of norisksoftware at gmail . com
    AND I am looking for contract work doing almost anything technical (web, C#, consulting, Cobol, EDI..) that pays well. I was out of the country for 2 1/2 years and my business kind of dried up. Go figure.

    Bill Ross

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 25, 2006 12:43 PM
  • User1224304770 posted
    Hi Bill, by default .NET 2.0 security providers(membership, profile, ect.) use a connection string named LocalSqlServer, to learn more details you can take a look at this article:

    http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
    Tuesday, December 26, 2006 4:50 AM