Web Parts & User Instance without SQL Express

Question

Hi,

I'm having hard times with the SQL Error

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.

No matter what I do (yes, I have the SQL Express running in the Local account and deleted the SQLExpress folder on Local Profile), users under Windows 2008 always get this issue and sometimes on Vista as well (not that frequent).

I do need to find other ideas!

I was thinking move to SQLite (as there is a wonderful Provider) or even use XML file instead.

All I do is manage WebParts as panels that each user can modify their visibility and place. When done editing it saves automatically into the user instance.

I need only this functionality but I can't find a decent way to drop SQL Express 2005 User Instances ... any ideas?

Any help is highly appreciated, Thank you.

Answer 1

Using "microsoft sql server compact 3.5" gives you almost any of the normal sqlexpress functionality while being easily portable without a real server to be put on.

http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx

Unless you're doing a big and complex application (lots of stored procedures, triggers and such), its a nice way to work.

Here's some other information :

http://blogs.msdn.com/stevelasker/archive/2006/11/27/sql-server-compact-edition-under-asp-net-and-iis.aspx

Answer 2

Hi balexandre,

I worked for webhosting company where SQL Express was used for freehosting users. We never experienced this problem so it must be some weird combination of parameters.

I suggest to do some probing

• Use Process Monitor to trace which files/directories/registry entries is SQL Express trying to access when the error occurs. Using this utility you can also locate problems with access rights.

• Look into SQL Express log located in C:\Documents and Settings\user_name\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS for some additional info

• Make sure you have user instances enabled running this on SQL express default instance sp_configure 'user instances enabled', '1'

• Just for sure I would try to list all user instances and eventually try to connect to one.
  
  To list user instances connect to default one (usually named COMPUTER\SQLEXPRESS) and run:
  SELECT instance_pipe_name FROM sys.dm_os_child_instances.
  
  Then use value in instance_pipe_name with sqlcmd utility:
  C:>sqlcmd -S np:\.\pipe\69651E0A-5550-46\tsql\query.
  
  Now you are connected to user instance. Try to execute some DML.

• You probably did this but for sure have a look in Windows Event Viewer

If nothing comes up from probing you can always create database using snandard way on SQL Express default instance (connect to server, create database, setup connection string and use it instead of database.mdf in your solution).