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
