none
AttachDBFileName option in Connection String

    Question

  • I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
    dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"

    However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.

    After adding "Database=Registration.mdf;server=<server N/W address>" and removing
    "AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.

    Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?

    Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?
    Thursday, August 23, 2007 5:13 PM

Answers

  • AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode.  Database is simply the name of the database to use, it has no additional connotation.  For any production server, you would most likely not be using AttachDBFilename.  It is strictly useful for development and experimentation in single-user mode.

     

    You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it.  The syntax of a server name is <Server>\<instance>.  Note that a blank <instance> is equated to the default instance.  In your case, the web server is running a default instance of SQL Server.  The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS.  You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

     

    Hope that cleared things up for you,

     

    John

    Monday, August 27, 2007 8:16 PM

All replies

  •  

    You might look following link. you need to provide the database name along with the DB filename

    http://msdn2.microsoft.com/en-us/library/ms130822.aspx

    Thursday, August 23, 2007 9:50 PM
  • Are database name & DB Filename not the same?? Do you mean complete DB Path when you say DB FileName??
    Moreover, what is an attachable Database??? The primary file of an attachable database is the default primary data file or it can be any primary data file???

    Also the ODBC driver Connection String mentions following:
    "The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"

    Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name??
    Friday, August 24, 2007 9:36 AM
  • Here.. what u need to understand is ... u r using the unique Embedded Database feature  of SQL Server Express. U r dynamically attaching the database.

     

    Refer this link it will help u

    Connecting to SQL Express User Instances in Management Studio
    http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

     

    Madhu
    Friday, August 24, 2007 10:00 AM
  • AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode.  Database is simply the name of the database to use, it has no additional connotation.  For any production server, you would most likely not be using AttachDBFilename.  It is strictly useful for development and experimentation in single-user mode.

     

    You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it.  The syntax of a server name is <Server>\<instance>.  Note that a blank <instance> is equated to the default instance.  In your case, the web server is running a default instance of SQL Server.  The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS.  You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.

     

    Hope that cleared things up for you,

     

    John

    Monday, August 27, 2007 8:16 PM