Failed to Generate User Instance of SQL Server

I have seen many posts on the subject, but I haven't seen a clear answer to the following:

1. Start a new Web Project (VS 2005 Pro). The solution is built.

2. Select App_Data, right click and select Add New Item.

3. In the dialog box, select SQL Database and give it a name.

4. When you select Add, the message "Failed to Generate User Instance of SQL Server ... " appears.

5. I am running SQL Express. What is the correct fix to get this simple process to work?

thanks,

Flavelle

I have been puttering, trying get this supposedly simple thing working for several hours.

1. The problem appears to be directly related to User Instance with SQL Server Express. There may also be issues with the setup of SQL Server Express to work with ASP .Net.

2. I have run the asp_regsql utility against my installation of SQL Server Express. I have reinstalled SQL Server Express, making sure that enable User Instances is set.

3. Is there ANYTHING else that I need to do to SQL Server Express to make it work for the scenario above. I do not have any other versions of SQL Server installed. I do have SSME installed and working.

I need an answer so that I can start to work on a project!

Sorry, but I am hugely frustrated, I am seeing a lot of posts related to this problem, but I am not seeing any answers!

Many thanks,

Flavelle

|||

Solved it - but this is not a solution for everyone and does raise some concerns about the viability.

At the time that I installed Visual Studio, I did not have an instance of SQL Express - so I installed the one that was packaged with Visual Studio. I think that this was the November CTP, but I'm not sure. When the release version came out, I uninstalled the SQL Express, then installed the released version. I am now suspecting that there was something in that process that did not go cleanly.

In total frustration, I backed up my data, reformatted the hard drive and did a clean install of everything (like I said, not a solution for everyone). Before I installed Visual Studio, I installed the release version of SQL Express. When I installed Visual Studio, it correctly realized that there was a version of SQL Express, and that it was later. It did not overwrite the installation.

When all of that was done, I repeated the tasks that started this posting, and this time, I did not get the exception.

Weird, and someone may want to do some further investigating, but I'm going to lurch on to the next problem, whenever that rears its ugly head. I think my solution is dependant on the order of the install, with the release version of SQL Express going in prior to Visual Studio. Hope this helps.

Flavelle

Faiied Query

I am running SQL Server 2005 Express on the backend and Access 2000 on the front end.
This query fails and it is not clear why?

SELECT DISTINCTROW tblTutoringHours.[Site ID] AS Expr1, tblTutoringHours.[Student ID] AS Expr2, Sum(tblTutoringHours.Hours) AS [Tutoring Hours], Min(tblTutoringHours.Level) AS [Start Level], Max(tblTutoringHours.Level) AS [End Level], [End Level]-[Start Level] AS Gain, Max([Date Graduated] Is Not Null)*(-1) AS GainGrad, Max([Drop ID]=8)*(-1) AS GainPromoted
FROM tblSites, tblStudents, tblTutoringHours
WHERE ((([tblSites].[Database Totals])=Yes))
GROUP BY tblTutoringHours.[Site ID], tblTutoringHours.[Student ID]
HAVING (((Sum([tblTutoringHours].[Hours]))>0))
WITH OWNERACCESS OPTION;

Doesn't look like you have all of the non-aggregate fields in the GROUP BY statement.|||

This calculated column contains columns that are not aggregrated and NOT in the GROUP BY:

[End Level]-[Start Level] AS Gain

This just won't work as written:

Max([Date Graduated] Is Not Null)*(-1) AS GainGrad

It could be revised as:

max( isnull( [Date Graduated], 0 ) * (-1) AS GainGrad