Code-First with SQL Server Express ; Operating System Error 2

Question

I'm trying to generate a SQL Server Express embedded database (.mdf) with code-first Entity Framework. It used to work fine but now, I just get over and over the same error, and I cannot identify the issue. The problem occurs on the initialization, it just hangs forever and if you look at intellitrace you can see some kind of loop of throw/catch statements all returning

"Unable to open the physical file "c:\gt\aspnetdb.mdf". Operating system error 2: "2 (failed to retrieve text for this error. Reason: 1815". Cannot attach the file "c:\gt\aspnetdb.mdf" as database 'WikDb'." (System.Data.SqlClient.SqlException)

UPDATE I also get the following error now just before the "unable to open physical file...."

Column 'InvariantName' is constrained to be unique. Value 'System.Data.SqlClient' is already present.

END UPDATE

Here is my app.config

<configuration>
   <system.data>
      <DbProviderFactories>
         <remove invariant="System.Data.SqlServerCe.4.0" />
         <add name="Microsoft SQL Server Compact Data Provider 4.0"
              invariant="System.Data.SqlServerCe.4.0"
              description=".NET Framework Data Provider for Microsoft SQL Server Compact"
              type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0"/>
      </DbProviderFactories>
   </system.data>
   <connectionStrings>
      <add name="WikDb"
           connectionString="data source=.\SQLEXPRESS;Integrated Security=True;
                       database=WikDb;AttachDBFilename=c:\gt\aspnetdb.mdf;
                       User Instance=True"
           providerName="System.Data.SqlClient" />
   </connectionStrings>
</configuration>

And here is my initializing code

Database.SetInitializer(new DbInitializer()); //DropCreateDatabaseAlways<WikDb> intializer
Database.DefaultConnectionFactory.CreateConnection( "WikDb" );
WikDb db = new WikDb();
db.Database.Initialize( true );

Any help appreciated

Thank you,

Operating system error 2 is "File not found", just for general knowledge. — Ken White, Dec 06 '11 at 16:41
What puzzles me a bit, although I have to admit I have never worked with code-first EF which might be the reason, is that in your app.config there are references to SQL Server Compact (or SqlServerCe) and then the connection string is for SQL Server Express. Might be a feature of the EF, usually one would choose one *or* the other though. — Sascha Hennig, Dec 07 '11 at 08:04
I know it was working before with this DbProviderFactory, however I looked for another one and found one for SQLExpress. I know have the same error, but just before I also get the following exception: Column 'InvariantName' is constrained to be unique. Value 'System.Data.SqlClient' is already present. — Pierluc SS, Dec 07 '11 at 15:02
So it was working with SQL Server Compact. Then you found another one wich was supposed to work with SQL Server Express? — Sascha Hennig, Dec 07 '11 at 15:56

Sascha Hennig · Answer 1 · 2011-12-07T07:48:13.603

2

It seems like your SQL Server Instance is broken. This tends to happen when installing additional instances pretty often - if using the Express Version that is, the full version is way easier to install and maintain (if you believe google - or my personal experience :P). Best way to see if thats the case would ofc to run your application on another machine with a working SQL Server instance. Can you connect to your database using the SSMS (SQL Server Management Studio). If not, try making a clean install of SQL Server.

Some more info:

Edit: I found that SQL Server Express works best if running a single default instance (the one named SQLEXPRESS), everything else gave me trouble, might just be me though.

edited Dec 07 '11 at 07:48

answered Dec 06 '11 at 16:36

Sascha Hennig

2,556
1
19
22

there might be multiple instances and I dont know. what i forgot to say, is that it says "unable to open" but there is no file, code-first is suppose to generate it. – Pierluc SS Dec 06 '11 at 16:41
this snippet of code (excluding app.config) used to work flawlessly, and it suddenly just started to prompt me this error, even when cancelling all changes that were made. – Pierluc SS Dec 06 '11 at 16:42
1

To see what SQL Server Instances you have installed open your SQL Server Configuration Manager. Under _"SQL Server Services"_ you should see one or more entries _SQL Server(Name)_ where name is the name of the instance. The default instance of SQL Server Express is *SQLEXPRESS*. Check if it is running or weather you have more than just the default instance installed. Alternatively open the command prompt and type *osql -L* for a list of all installed instances. If the server instance is not running or you got more than one instance installed deinstall all and reinstall the default instance. – Sascha Hennig Dec 07 '11 at 07:44
What is pretty interesting is that there is no connection in Configuration Manager. Which is pretty interesting thinking backward, because nothing is created. – Pierluc SS Dec 07 '11 at 14:42
Nevermind last comment, I just realised I had SQL Server 2005 and 2008 on my machine. There is only one instance running. – Pierluc SS Dec 07 '11 at 15:00
Do any sql server error/warning entries come up in the window event viewer for when the system boots or at the time you run your application? Start *eventvwr* via the run textbox thingy and see if you can find any sql server error entries. – Sascha Hennig Dec 07 '11 at 15:55
There is no error but only an information message. "Server resumed execution after being idle 11671 seconds: user activity awakened the server. This is an informational message only. No user action is required." – Pierluc SS Dec 07 '11 at 18:18
Thats not it. And Google tells me that apparently the new error you are getting has something to do with wrong entries in your app.config/machine.config. See if you are using the proper references/assemblies. SqlServerCe 4.0 databases did not work with SqlServer 2008 R2 for example, if I remember right. Try the 3.5 assemblies. I can only do guesswork. I'd solve this by trial and error myself I guess. ^^ You also might try to create a simple, new project to see weather that gets around the issue and how the app.config looks like, as it might have been introduced by switching the dbs. – Sascha Hennig Dec 08 '11 at 08:08
When I build up a new ASP.NET project or MVC register/login work just fine and .mdf is created (in .NET 4, MVC 3) – Pierluc SS Dec 08 '11 at 16:08
Compare the app.config files. I am pretty sure that when you "reverted the changes" some references to the new database you used were not reverted back. So basically half of your project thinks it is supposed to work with a SQL Server Compact DB and the other half is looking for the SQL Server Express DB. Your best bet is comparing the working project with your non-working and go from there - if you do not want to restart coding the old project. – Sascha Hennig Dec 09 '11 at 09:02
i'll probably try to start from scratch on monday =P. I'll let you know – Pierluc SS Dec 09 '11 at 20:09

Code-First with SQL Server Express ; Operating System Error 2

1 Answers1