Tuesday, 15 November 2005
SQLExpress Problems
I've recently ran into a strange problem with SQLExpress where I couldn't create a database. This typically showed itself when I was running an ASP.Net 2 app for the first time and either creating a DB to hold profile or security data.
The error something like
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."
This was using the default LocalSqlServer connection string (from machine.config), changing that string to say 'User Instance=false' then gave me a different error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
Also if I tried to use the SQL Server Management Studio to connect to SQLExpress and then try to attach to an existing MDF file I get the following error:
----------------------------------------------------- The file "D:\home\kevinj\Course\dotnet\ASP2.Net\prep.2\Security\App_Data\aspnetdb.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. Could not open new database 'C:\HOME\KEVINJ\COURSE\DOTNET\ASP2.NET\PREP\SECURITY\APP_DATA\ASPNETDB.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5118) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5118&LinkId=20476 -----------------------------------------------------
These problems left me stranded for days, then today, on another machine, I hit a different set of problems I kept getting the following
"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."
and if I set "User Instance" to false the following
"An attempt to attach an auto-named database for file C:\[app_path]\ASPNetDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
I figured first of all this may have been something to do with the fact that I run as non-admin, so I tried running VS2005 as admin and executed the web site and sure enough it worked. So I thought I'd need to run as admin. I added myself to the admin group, logged back in and tried the app again, and got the same error! This was too weird. I had two users, both admins one for whom the app worked and one where it didn't.
In these circumstances Google is your friend. I found this post on a Microsoft forum. If you don't want to follow the link, the short answer is that SQLExpress creates a directory per user in "c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" that it uses to store information. Deleting this directory has fixed both of my problems.
Posted by at 2:32 PM in Net
Thanks a LOT. I had the same stupid problem and I spend two days on it, reinstalling the sofwares to address it. !! All I needed was to just delete the stupid directory ||
Many thanks from me as well. This problem was driving me nuts.
Hard to think deleting something would fix the problem.
THANK YOU!!! This has been frustrating me for 2 days now. I've uninstalled & reinstalled a few times and nothing worked.
One little click of the Delete key works wonders :)
WOW, thanks so much for blogging this fix. I googled the exact error message and you were the first ranked answer.
Instead of deleting (I am a pack rat, deleting is HARD for me!) I renamed the directory by appending .old on the end of the directory name.
That worked as well (of course!).... SO thanks so very much from someone who after two times with no success decided to google the error message and found your fix!!
Simple, elegant, and efficient!!
Angela
Thank you very much. I'm sure I would have suffered grieviously were it not for your post.
Thanks heaps Kevin, I recently had a similar problem, except I actually did want to use user instances. I googled the error, fond your post and It worked a treat.
PS. It might be worth grabbing beta2 of IE7 and having a look at your blog in it, it seems to do some nasty stuff to what is otherwise a good layout.
Cheers.
THANK YOU very much. I spent a whole afternoon reinstalling Visual Studio and SQL server and SQL express not knowing that this problem existed or being able to find it on Microsoft's support site..
You are a GODSEND!
Thanks. This also saved me. I am finding SQL Express a real pain having problems such as this. Cheers
Jolyon
You're the best!!!
I faced your same problem
thanx for the solution
Lorenzo