Fix error "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance."

Fix error "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance."

Have you ever tried to create a user instance database from Visual Studio on SQL Server 2005 Express (or above) and got this terrible (can it be more confusing?) message:

"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."

If you did and tried to find how to resolve this you probably Googled (or Binged) for hours without being able to find the solution that really works.

Well lets put an end to that. Two simple things need to be done:

Step 1. Enabling User Instances on your SQL Server installation
First we are gonna make sure we have enabled User Instances for SQL Server installation.

Go to Query Window in SQL Server Management Studio and type this:

exec sp_configure 'user instances enabled', 1.
Go
Reconfigure

Run this query and then restart the SQL Server.

Step 2. Deleting old files
Now we need to delete any old User Instances.
Go to your C drive and find and completely DELETE this path (and all files inside):

C:\Documents and Settings\YOUR_USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

(Dont forget to replace the green text in the path with your current username (if you are not sure just go to C:\Documents and Settings\ path to figure it out).

After deleting this dir you can go to Visual Studio, create ASP.NET WebSite and click on your App_Data folder and choose Add New Item and then choose SQL Server Database and it should work!!!

Errors Handling in SQL Server

Errors Handling in SQL Server


When a function is wrapped around an indexed column in the WHERE clause it will usually prevent the proper usage of that index. This article gives an example and discusses what other options are available.

I still remember the first time I ever saw this truly crush a server.  I was doing performance tuning at a client site.  We still hadn't convinced their developers that the DBA's needed to review their T-SQL code before it went into production.  One morning we noticed performance problems on the server.  Disk reads would go way, way up.  SPIDs would begin to block.  Response time on their web site slowed to a crawl -- if it responded at all.
After first insisting that no new database code was rolled out they finally admitted they'd changed one line of one WHERE clause in a single stored procedure.  That change brought the database server to a stop.  They had added a YEAR function around an indexed date column so that a report would only pull back a single year.  That changed the query plan enough to start generating table scans.
A script to simulate this looks like this:

USE AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.indexes 
   WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
   AND name = N'IX_SalesOrderHeader_OrderDate')
 DROP INDEX [IX_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader] 
GO
CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate)
GO

SELECT SalesOrderID,
 OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2003
AND MONTH(OrderDate) = 7


SELECT SalesOrderID,
 OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2003' AND '7/31/2003'
GO


IF  EXISTS (SELECT * FROM sys.indexes 
   WHERE object_id = OBJECT_ID(N'[Sales].

[SalesOrderHeader]') 
   AND name = N'IX_SalesOrderHeader_OrderDate')
 DROP INDEX [IX_SalesOrderHeader_OrderDate] ON 

[Sales].[SalesOrderHeader] 
GO

This script creates an index on OrderDate.  In my sample queries I'm only pulling back a single month.  The first query has a cost of 0.080 and does a scan of the index.  The second query has a cost of 0.004 and does a seek on the index.  Yes, the costs are very low because I don't have a large set of data.  The first query still has a cost twenty times higher than the second.
When you wrap a function around an indexed column SQL Server must compute the value of the function for each row in the table.  When you just compare the indexed column to a scalar value or the result of a function then SQL Server can use that value to seek into the index.  The only time you can use a function around an indexed column is if you use an indexed computed column that matches the function you're using.
This type of coding is mostly used for date fields when computing ranges such as querying for the previous week.  Hopefully this little tip will save you from making the same mistake those developers did.