Tuesday, April 24, 2007

SQL 2005 Express error regarding generating new instances

Recently, I installed Microsoft SQL 2005 Express. Late on, I encounted a problem to open a database from Visual Web Developer 2005 Express. The error message is something like:

Generating user instances in SQL Server is disabled. Use 'user instances enabled' ...

I think the problem was caused by my installtion of SQL-Tools for adding Reporting services. I googled through web and found a solution to solve the problem from this link MSDN143684. Here are some steps:

  1. Open Microsoft SQL Server Management Studio Express
  2. Open a query and type in: Select * from sys.dm_os_child_instances. This will list all the users instances.
  3. Comment out the above line. Type in the commands:
sp_configure 'user instances enabled','1'

Then open a project in VWD, and try to open a db again. The db was opened without error message.

I think there is a configuration in SQL to set either shared DB or new instances. If it is shared DB, no new instances are created.