Visual Studion 2008 App_Data defaults

Question

Is it possible to use the App_Data folder in conjunction with SQL Server 2005?

When I try it specifies Express even though I have changed the Tools>Options>Database>Data Connections to the correct server. I have downloaded SQLEXPR32_x86_ENU.exe Version 10.0.1600.22 file locally and have gone through 7 installs and deinstalls with a variety of different errors. I pretty much given up on Express and would like to find a workaround if it exists.

Thanks

Dennis Keith

SQLEXPR32_x86_ENU.exe Version 10.0.1600.22 is SQL Server 2008. Version 9.0 is SQL Server 2005. — Lukasz Lysik, Oct 05 '09 at 14:43
I had 2008 installed on this machine and around iteration 3 or 4 I removed it attempting to get the Express loaded. SQL 2005 is loaded and running. Does this mean that I cannot have Express2008 running with SQL 2005? I used to have an older version of SQL running with 2005 as I made the shift and didn't have a problem with that. — , Oct 05 '09 at 15:19
I forgot to add - Does this mean that Visual Studio App_Data has hard coded functionality involving only Express? — , Oct 05 '09 at 15:21

score 0 · Answer 1 · edited May 23 '17 at 12:19

You can use a SQL Server 2005 (or 2008) database with a Web Application or Web Site project. You can probably have the database files (.mdf, .ldf) in the App_Data folder (and remember you need to attach the database¹ to SQL Server directly—the auto-connect file only works with Express).

But you do need to ensure the data connections used by the application are set to use connection strings defined in the application's own web.config. By default things like the membership provider default to a SQL Express database in App_Data due to the contents of the global machine.config² setting the membership provider to use connection LocalSqlServer which is set in the same file to be:

data source=.\SQLEXPRESS;Integrated Security=SSPI;
AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

Where |DataDirectory| will be replaced at runtime by App_Data in the application root.

Summary the application, (web.config, VS's data connections are not used outside the designer) needs to either:

Use SQL Express. .mdf in the App_Data folder with a connection string using "AttachDBFilename".
Use SQL Server (full) with the database configured (persistently) in SQL Server with the data files (.mdf, .ldf) in a location that the SQL Server user account can access. All the connection strings reference that database via "Data Source" (to set server name) and "Initial Catalog" to set the database. The account for the IIS application pool needs to be able to access SQL Server.

¹ Use SQL Management Studio to do this. (And thanks to the other answer for reminding me.)

² See %SystemRoot%\\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config. The

Thanks for your response Richard. With a huge AhHa I ran to machine.config only to find: This string works in a Data Connection made under the Server Explorer. There are no entries in the web.config and there is no mention of Express in the machine.config. Puzzled — , Oct 05 '09 at 15:54
As per ususal, I should add something. The error comes up when I try to add a new mdf data file to the App_Data folder. When I try to open an existing database there's no problem. — , Oct 05 '09 at 16:00
And the error reads "Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify ...." — , Oct 05 '09 at 16:01
I've expanded the answer. I think you are still mixing up SQL Express and SQL Server: choose one and configure consistently for that choice. — Richard, Oct 06 '09 at 07:45

score 0 · Answer 2 · answered Oct 05 '09 at 19:40

Putting a *.mdf file into your App_Data and attaching it as a user instance database works only with SQL Server Express, yes.

If you want to use "real" SQL Server, you can still put your *.mdf and *.ldf files into App_Data - you just can't automagically attach them to your SQL Server instance, you'll have to do this manually using SQL Server Management Studio (in the Object Explorer of your SSMS, go to the "Databases" node and right-click, pick "Attach...." and then navigate to your App_Data folder and pick the *.mdf file).

So yes, in a way, Visual Studio has a App_Data folder that makes most sense with SQL Server Express (which is being installed with Visual Studio by default).

Marc

Visual Studion 2008 App_Data defaults

2 Answers2