Rahul Singla


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



Re: AttachDBFileName option in Connection String

Ziran Lin - MSFT


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







Re: AttachDBFileName option in Connection String

Rahul Singla

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





Re: AttachDBFileName option in Connection String

Madhu K Nair

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




Re: AttachDBFileName option in Connection String

John Gordon - MSFT

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