'System.InvalidOperationException' and 'Instance failure'

cuddling101 · November 14th, 2010, 02:35 AM

I am having difficulty at the stage of mapping data model to an entity model. I have honestly lost track a little here. I thought that I had it basically working but I am not sure. There was an issue arose where I had 'provider connectionstring' instead of 'provider connection string', but I corrected that, thanks to Imar's input. I have then come to wanting to update my Entity Data Model and I have hit various problems.

As I had got confused, I sought to delete all the stuff (maybe I deleted too much), that the process outlined on pages 481-3 of Beginning ASP.NET 4 had created, including the Entities line in web.config, and to restart the process at point 1 on page 481, but I am now receiving the message -

Code:

An error occurred while connecting to the database.  The database might be unavailable.  An exception of type 'System.InvalidOperationException' occurred.  The error message is: 'Instance failure.'.

This occurs when I try to go from Step 3 to Step 4, i.e, go on to where I will be adding Tables to the model.
In another post in these forums about ASP.NET.4, I found the following comment â

Quote:

USERNAME\AppData\Local\ Microsoft\Microsoft SQL Server Data\SQLEXPRESS. It was amazing to see all the new folders in the path showing up and expanded.

After I deleted the files in this folder I was finally able to see the database and all its files under the project Data Connections.

I wondered whether this had some relevance. I see that my version of this file contains a file named Entropy.bin, some error.log files, some log.trc files and 3 pairs of .mdf and .ldf files, which are named master.mdf, mastlog.ldf, msdbdata.mdf, msdblog.ldf, tempdb.mdf, and templog.ldf. Being uncertain as to what these are, I am hesitant to delete them, for fear of causing even more problems.

I am able to see the database quite happily in SSMS. I have been working on the database in there over the last few days. I have been updating contents and adding tables.

Any ideas re what to look for, or step I should go back to (in order to check that I haven't 'corrupted' things), would be very welcome, please.

Please do ask for more code 'input' if you need it, in order to help.

With thanks in anticipation

Philip Hunt'
Medina, Kwinana
Perth, Western Australia

Imar · November 14th, 2010, 07:19 AM

Hi Philip,

Can you post your connection string?

Also, can you try moving the SQL Express folder and see if that fixes things? That way, it's easy to restore the files if removing them doesn't fix it.

Quote:

step I should go back to (in order to check that I haven't 'corrupted' things),

This isn't so much "steps in the book" related; it sounds like an issue with SQL Server Express which needs to be solved separately before you can create and use a model.

Cheers,

Imar

cuddling101 · November 14th, 2010, 10:28 AM

Hello Imar

Thank you for your help.

The following is the connnection strings section of my web.config.

It';s getting late here now, so I will try the SQL Express folder move stuff tomorrow evening after work.

Code:

  <connectionStrings>
    <clear />
    <add name="How_and_Why_Wonder_BooksConnectionString1" connectionString="Data Source=.\MSSQLSERVER\;Initial Catalog=How_and_Why_Wonder_Books; Integrated Security=True; Connect Timeout = 30;" providerName="System.Data.SqlClient" />
    <add name="LocalSqlServer" connectionString="Data Source=.\MSSQLSERVER;Initial Catalog=aspnetdb;Trusted_Connection=True" providerName="System.Data.SqlClient" />
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
    <add name="How_and_Why_Wonder_BooksConnectionString2" connectionString="Data Source=.;Initial Catalog=How_and_Why_Wonder_Books;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Thank you

Philip

Imar · November 14th, 2010, 11:46 AM

Hi Philip,

I don't see a connection string targeted at EF. You should have one containing stuff like metadata=res://*/

The easiest thing to do probably is to create a new model. Add a new EF Entity Model to your site, follow the wizard and assign a connection string, choose your tables and so on. Then VWD will update your config file with a correct connection string.

Cheers,

Imar

cuddling101 · November 15th, 2010, 08:08 AM

Dear Imar

I thank you for your suggestion to create a new Entity data Model, but that is exactly what I was trying to do that led to the error that started this thread.

As I also explained, I had deleted some things, thinking that would get me back to a 'safe spot' so to say , but all that did was cause more problems.

I have now restored the things I deleted and my web.config file connection strings section now reads -

Code:

  <connectionStrings>
    <clear />
    <add name="How_and_Why_Wonder_BooksConnectionString1" connectionString="Data Source=.\MSSQLSERVER\;Initial Catalog=How_and_Why_Wonder_Books; Integrated Security=True; Connect Timeout = 30;" providerName="System.Data.SqlClient" />
    <add name="How_and_Why_Wonder_BooksEntities" connectionString="metadata=res://*/App_Code.How_and_Why_Wonder_Books.csdl|res://*/App_Code.How_and_Why_Wonder_Books.ssdl:res://*/App_Code.How_and_Why_Wonder_Books.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\MSSQLSERVER;Initial Catalog=How_and_Why_Wonder_Books;Trusted_Connection=True;MultipleActiveResultsSets=True&quot;" providerName="System.Data.EntityClient" />
    <add name="LocalSqlServer" connectionString="Data Source=.\MSSQLSERVER;Initial Catalog=aspnetdb;Trusted_Connection=True" providerName="System.Data.SqlClient" />
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
    <add name="How_and_Why_Wonder_BooksConnectionString2" connectionString="Data Source=.;Initial Catalog=How_and_Why_Wonder_Books;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

I have also restored the .edmx file and its associated designer.vb file, which I had deleted because I thought it might just be best to start again.

That edmx file is named How_and_Why_Wonder_Books.edmx.

What I was actually trying to do, before I got into this whole episode of deleting and redoing, was to update the entity data model.

As shown on page 1114 of Professional ASP.NET 4 I have sought to right click on my Entity Data Model, in order to add a table, just as it shows in that book.

On choosing the option Update Model from Database, I receive the following error message -

Code:

An exception of type
'Microsoft.VSDesigner.Data.Local.ConnectionStringConvertorServiceException' occurred while attempting to update from the database.  The exception message is ''.

When I tried to create a new Object Model, as per the instructions on page 481 of Beginning ASP.NET 4, I got the Instance Failure message that has started this thread. This occurred when I sought to go from step 2 to step 3, that is when I chose the Generate from Database option and clicked Next.

I hope this gives you an appropriate set of extra information that will enable you or your colleagues to come up with ideas for other things to check out.

As I stated at the beginning of this thread, I am able to successfully access the database through SSMS. I use Windows Authentication and I have certainly gone through and checked all the places I have been directed to re permissions etc.

With thanks for all you have done for me and hopes that you might be able to come up with some new avenues of investigation.

Best regards

Philip

Imar · November 15th, 2010, 08:39 AM

You have a mix of .\SqlExpress and .\MSSQLSERVER in your connection strings. Is that by design? Do you have multiple SQL Server instances installed?

Imar

cuddling101 · November 15th, 2010, 10:12 AM

Dear Imar

It has certainly not been my intention to have two versions of SQL Server running.

This whole MSSQLSERVER v SQLEXPRESS thing is something we have discussed before, and I have never really got completely to the bottom of it or understood it.

I do not consciously have two versions of SQL Express installed or active, but neither do I know how or where I could check for that, or even what I would be looking for to do such checking.

How can I establish which one my SSMS accessible database is running against? How, if I successfully eliminate the references to the incorrect one, do I make sure that VWD doesn't again generate references to the 'dud'?

Thank you for looking at this.

Regards

Philip

Imar · November 15th, 2010, 10:30 AM

If I remember correctly, you didn't have an instance name. In SSMS, you logged into either (local) or . (a single dot).

So, in your connection strings, drop \MSSQLSERVER and \SqlExpress and see if that works. E.g.:

... Data Source=.;Initial ...

Also get rid of the trailing slash you have following MSSQLSERVER

Cheers,

Imar

cuddling101 · November 17th, 2010, 07:27 AM

Dear Imar

I have tried your suggestions and the same error is arising when I try to update the edmx from the database.

Best regards

Philip

Imar · November 17th, 2010, 08:04 AM

And what happens when you create a brand new web site, add a brand new model and assign a brand new connection string to your database?

Imar