Stuck between "Failed to generate user instance" and "An attempt to attach...failed"

I wrote an application using Visual Studio 2005 beta 2 which uses a SQL Express .mdf file, included in the project, for the database. After installing Visual Studio 2005 RC and the SQL Express that comes with it (I followed all of the uninstall instructions first) I can no longer add a SQL Express database to any of my projects, nor can I open the SQL Express database in my original project. When I try either of these tasks, I get the "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I read the thread which dealt with this error, and changed the connection string for the database file that had worked before to User Instance = false. The moment I click Test Connection or OK, I get the error: "An attempt to attach an auto-named database for file C:\<path to project>EngSQL.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." (<path to project> is actually a full path, I didn't want to type the whole thing out. :) ). A search for this error turns up a solution of setting User Instance = false, which puts me in a catch 22 position.

Any suggestions

-- Chris

Answer this question

Stuck between "Failed to generate user instance" and "An attempt to attach...failed"

Rob Duffy
I can Connect SSE but its when i'm listing databases i have only default databases (master ...)
I suppose that's normal why my database has never been attached on the server (i has always failed with this error :( ).

Perhaps the folder for IIS_WPG member accounts is not document and settings

Respectfully,
Lorenz Buchberger
If you have the June CTP and then upgraded to the September CTP, you may well have issues with your installation. If you had the ability to log into SSE with the June ctp on the primary account, then there will be files at : C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. You will need to view hidden folders to see it, but it will be there.
Kevin TX
What is troubling is that you say that it works fine on a different account on the same machine. If that is in fact true, then that speaks to the only thing that may be unique per your account. The only data that is unique per your account is the data at the location I mentioned above. But, you're saying, if I understand correctly, that there is nothing to delete at that location. So, that won't actually help. So, I don't know what to tell you. And, I don't think anyone is the SQL org could tell you either. It just doesn't really make any sense.
Sundaywork

thanks but it does no solve my problem on my server i have just copy my .mdf and there was no .mdf in documents and settings directory. So i have nothingto delete. And i see hidden folders and files : activate this option is my first action when i setup windows ;).

I add that on my server the only one version that has never been setup is September CTP. This on my developpement machine where i have used several version of sql server. but on this dev computer it work fine...

Respectfully
Richard Diamond
It sounds like attaching using the auto-attach functionality is failing for some reason that is not clear to me. I suspect that your installation is faulty for some reason
sunshine58
And, on the machine where you have the problem, are you able to connect to SSE at all Or, are you only able to connect on the development machine
D.R.B.
Thx. Deleting the above mentioned directory did actually help. We're up and runnning again. :-)

Ilsandor: Note that in \Documents and Settings\<your user account name> you should look in the "Local Settings\Application Data\" not just the "Application Data\" diretory. (I did make that mistake myself.....)
Laurensvp
OK, here's the deal. There are actually two key things associated with the ability to use an MDF locally. The first is the *logical* database name. If you look closely at a connection string that uses MDFs locally, you will see that it is missing. The missing logical database name is a signal to SQL Server to create one for you automatically.

The second thing is the User Instance = True. This simply says, "let me use an MDF even though I"m not logged in as an admin". Thus, it will let you work with an MDF as a "normal user".

The other thing to keep in mind is that once you attach an MDF, SQL Server keeps track of it until you detach it. So, here is the remedy.

1.) Download SSEUtil from the web. http://www.microsoft.com/downloads/details.aspx FamilyID=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en and use it to detach your database from the running instance of SSE. It is very much like SQLCmd, but much friendlier.

2.) You should be OK to create/connect the way you have in the past. However, if you want to continue down the path you were on, you can add a logical database name to your connection and be off.
Pimenta186
the problem is really simple i have one machine : developpement and one other : hosting.
When i copy file from the first to the other i get that error when a run my asp.net website
satishdayala
+1

Chris, I don't have anything to add to your post except for the fact that I'm experience exactly the same symptoms you are. Did the uninstall/install steps for the RC from the PDC. What used to work (drag/drop MDF file into project) doesn't. And I tried the same thing you did (user instance=false).
a_takavci
I got the same problem here.

The hotfix did not correct the problem.

Running SSEUtil -l under a different user account works.
CliftNorris
Thank you for your post, but in regards to it being marked in green as the "Answer" I must respectfully disagree and as such have unmarked it.

My problem lies deeper than your suggested solution.

For me, running sseutil -l just to get a list of attached databases gives:

C:\sseutil>sseutil -l
Failed to generate a user instance of SQL Server due to a failure in starting th
e process for the user instance. The connection will be closed.

I used some ideas presented in the bug tracking forum for VS .NET 2005 and the following is what I posted there:

I uninstalled everything again (VS 2005 RC, SQL Express, .NET 2.0, etc), re-installed, and got same result. Can't even add a database file to a new project. I then created a new user on the machine and was able to add a database to a new project. I opened the project I had done in Beta 2 and when trying to open the database received an error saying that converting from a non-release version of SS (592 if I recall correctly) was not supported and I have to recreate the database. Something in the original user profile is causing the problem.

Something in my current user profile is preventing me from using SQL Express databases normally. And this something is not corrected when VS.NET 2005, SQL Express, .NET 2.0, etc are uninstalled and re-installed.

Now, if I do a runas and use the new profile I added, I can do a sseutil -l and here is what I get:

C:\sseutil>sseutil -l
1. master
2. tempdb
3. model
4. msdb

A sseutil -l on each of these says they are system databases. Are any of these the "logical" database you refer to in your post

I really would like to figure out what in my current profile is causing this problem, but for now have resorted to using the new profile to continue development on my application.

-- Christopher C. Bernholt
Aggag
OK. First know that the error message you are getting is very often caused (if not by the issues described by my original post) by using Terminal Server to get into the box and then using SSE. There is a fix for the TS bug posted by Windows.

But, the error message is fairly general; it catches a number of different issues. Your original bug description didn't mention TS and you are *currently* able to get into SSE under a different profile.

So, here's some additional info about how SSE runs under this mode. When you first run SSE this way, it makes a local copy of master etc. in C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. That's why it's so slow the very first time it loads under a new user profile. It may even timeout the first time you ever use it under a given profile.

Based on your description, it's very possible something went bad in those files for some reason. So, I would first make a backup and then delete the SQLExpress directory under C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data. That will get rid of the local copy of master, etc. Then, reboot.

When you run under your user account again and try to connect to SSE, it will recopy all of the appropriate files (master, etc.) to your local settings. It should work after that. You shouldn't worry about the recopy of master etc. Generally, unless you are adding logins, etc, (which you don't need to if you are working with UserInstance=true) the recopy of master should be fine.
BruceTT
I get the same probleme on my server but none of the answers abose solve my problem. I also try to put all rights on Database directory to Everyone.
But for the last post i have no content in document and settings for my IIS_WPG members then i can't delete this files.
Is there a compatibility issue between June CTP .mdf generated files and september CTP files (i use my old database)

Respectfully
Stuck between "Failed to generate user instance" and "An attempt to attach...failed"

Answer this question