Problem connecting to database - user instance and Entity Framework issue

Question

I've created an entity model file (.edmx) based on an .mdf file in my asp.net application, placed in my App_Data Folder.

first of all my connection string, created by the entity framework wizard :

<connectionStrings>
    <add name="Sales_DBEntities" 
        connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='data source=.\SQLEXPRESS;attachdbfilename=&quot;c:\users\ext\documents\visual studio 2010\Projects\WebProject_A\WebProject_A\App_Data\Sales_DB.mdf&quot;;integrated security=True;USER INSTANCE=TRUE;multipleactiveresultsets=True;App=EntityFramework'" 
        providerName="System.Data.EntityClient" />
</connectionStrings>

The above is placed in an app.config file created by the wizard.

I've copied the same connection string to the web.config file as well (I'm not sure if this is necessary).

I've come across two problems when attempting to later run the same application on a my machine (I've created the application on a different machine).

The first was regarding the user instance:

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed

The first thing I don't really comprehend is what a user instance actually defines.

According to MSDN :

A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance

Is my local database considered a user instance ?

I would really like some clarification on the matter because I came across posts suggesting to mark it as False, and the reason was not clear to me .

another post on this matter

From there I came across another solution that explains that for this problem you need to enable user instance in the SQL Server on your machine.

The Fix

I've done what was explained in that post

but now I've come across a new problem :

An attempt to attach an auto-named database for file (Full Path to my app_data\my.mdf file) A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I've already attempted to delete all the files under

 C:\Documents and Settings\(your user account name)\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

and rebooting like suggested in other posts, but still I can't find a clear solution for this problem.

I know this is a long post but any help would be appreciated

thanks in advance.

Favorable

Answer 1

Read this very good overview of what user instances really are.

In a nutshell:

• SQL Server allows you to have multiple instances of itself on a single computer - the default instance can be accessed using just the machine name (or IP address) of the host machine - all other instances need to be specified by an instance name (like the SQLExpress on your machine)

• for development purposes, you can also create a user instance - each user gets their own separate instance of SQL Server (works in the Express edition only) and a database is attached by its file name (path\Sales_DB.mdf) to that user instance. This instance is started up on demand, then runs and is shut down when no longer needed

While this works great for development, it's not really intended for later on - certainly not for production use.

In a production environment, you want to attach your database files to the actual SQL Server instance they should run on - typically using SQL Server Management Studio. When you do this, you no longer refer to that database via its file name, but instead you use the database name that was given to the database when being attached to the server. In this case, SQL Server will handle all details of operations - and that SQL Server instance is up and running all the time (doesn't need to be started "on demand" all the time), and you can use things like permissions and logins to control access to the database.

Your connection string would then look something like this:

<connectionStrings>
    <add name="Sales_DBEntities" 
         connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='server=YourServerNameHere;database=Sales_DB;integrated security=True;App=EntityFramework'" 
        providerName="System.Data.EntityClient" />
</connectionStrings>

When you have attached a database to a production (or testing) SQL Server instance, you do not need to know what the files are that make up the database, and you don't need to specify those files - SQL Server will take care of that. You just refer to the database via its database name.

Morrie

thanks for the clarification of the user instance , i can't say i fully understand it, but ill dig more into it . does running an asp.net application with a local DB inside App_Data create a USER INSTANCE ? do you have any thoughts about the 2 exceptions i got ? which to my understanding have something to do with the way the sql express server defines user instances on my machine . – Favorable

Answer 2

The solution is always simpler then anticipated:

1. double click the model.edmx file -> the designer will open.
2. right click on the designer -> Update model from database .
3. new connection -> Choose Database file (.mdf) under type .
4. Browse -> go to your App_Data folder, and choose the DB.
5. copy the connection string created in App.config to web.config.

bye.

Favorable

Thank you. Your solution helped. Earlier the error was coming because of the |Data Directory|. On explicitly choosing the mdf file its path was changed to actual path by entity. – Spring