sql server user instances enabled?

Will · March 10th, 2009, 12:25 PM

I have been working through chapter 11 and 12 and have managed to add the planet wrox database fine and it works, have got up to the bit about styling the appearance of it.

So I created a database in sql server 2005 express for a practice site I am working on and went to attach it through the App_Data folder like before but I got a problem that it was already in use by another program. So I did it again and just detached the database and it worked fine.

But when I go into the server/database explorer it isn't showing a connection to the database.

So I removed it from the App_Data folder and then in the server explorer tried to add a sql server database connection and it goes smoothly until I test the connection at which point it tells me that user instance is not enabled.

I also took a look at the advanced settings and it had user instance set to true so I tried both ways of having it on true and false and still no joy.

So I reopen sql server and I execute sp_configure 'User instance enabled' and it executes fine confirming that I have user instances set to 1.

So I then tried it again and I still get the same error message. What am I doing wrong?

As far as I can tell I have done nothinbg differently apart from create the database in SQL Server 2005 and I only did it in there as I liked the setup/layout better than in Visual Studio.

Imar · March 10th, 2009, 01:17 PM

Here's what I usually do:

1. Rely on App_Data with user instances against .\SqlExpress as much as possible. Just create a new database in App_Data and things just work.

2. If I need more control over the database I attach it to SQL Server (Express or Dev edition), do what I need to do, detach it again and then again rely on the Express version and user instances to make it work.

3. If I need full control over a database, I attach it to SQL Server (Express or Dev edition) and then manually modify the connection strings in Web.config to point to this server. I don't use attachdbfile but simply refer to the Initial Catalog.

If you follow these rules, you shouldn't have to go through all the troubles you're going....

Imar

Will · March 10th, 2009, 02:25 PM

Hmm Ok I will have another go and create it directly into the App_Data folder as you suggest.

Thanks for the help.

Will · March 10th, 2009, 02:41 PM

It would appear I get the same problem when I try to create it straight into the App_Data folder...

Should I just un-install and reinstall the express edition, do you think that would help?

Imar · March 10th, 2009, 06:09 PM

Are you saying you can't simply add a new .MDF file to App_Data, double click it to open it in the Server Explorer and then later use it for, say, a SqlDataSource to see if it works?

How does your connection string look if you add such a control? Does it referece .\SqlExpress?

Don't know if reinstalling helps. It might, Since I don't know what else you changed in SQL Server it might undo some of what you changed so maybe then things start to work again.

Imar

Will · March 10th, 2009, 06:26 PM

Yup thats what i'm saying..

Well I got a new computer and just put vs 08 and sql express on there and haven't changed anything from what it should be.. so i don't understand why it wont work

Will · March 12th, 2009, 03:38 PM

Ok even though during the installation of SQL Express I told it to have user instances enabled it seems that it ignored this.

If anyone else get the problem you solve it by running this query:

Code:

exec sp_configure 'user instances enabled', 1
Reconfigure

hope this helps some people out as it frustrated me for a few days.