Failed to generate a user instance SQL Server Express 2005

Hello,

I get the following message after updating to sql server express 2005 advanced.

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.

I noticed that in my C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data I have not databases? How do I get them back?

I also had to add back the aspnet to the permissions and the network and network service to the app_data folder.

Can any one help?

Thanks

Tom

Moving this thread over to the SQL Express forum.

-- Robert

|||

With User Instances the database files are not created in the old data directories, they are normally installed in the main project output directories for the project. In ASP.Net this is normally the ASP_Data directory. When the application starts it will automaticle attach the database to the database engine and set the permissions.

With your problem have you tried a complete rebuild of the application, Did it work befor you upgraded to Express Advanced. Also you might want to try opening up the file and attaching it to the database engine using the management studio just to make sure it is working fine, you can then detach it and try the application again.

|||

Hi Tom,

Did you ever resolve this issue?

You definitely need the databases located in Template Data in order for User Instances to work. If you didn't get this resolved, I would suggest trying to re-run the install. Usually the only reason an installation results in those files going missing is that some SQL process was running at the time of the installation.

If you haven't tried it yet. I'd also recomend moving to SQL SP1 which you can download from http://msdn.microsoft.com/vstudio/express/sql/download/.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Facing some problem in Instead of Trigger for MultiRow Insert

This is the Trigger which is not working properly during Update, no any
record is going to be updated so pls help.

I am updating the requisition table when any update in quantity in
podetails table

CREATE TRIGGER trig_updateRequistion ON ERP.DBO.TranPurchaseOrderDetail

INSTEAD OF UPDATE

AS

IF UPDATE(Quantity)

BEGIN

Update RequisitionSlipDetail
set RequisitionSlipDetail.PoQuantity =

(Select PoQuantity from RequisitionSlipDetail where
ItemCode=(Select CAST(i.ItemCode as nvarchar(20)) from inserted as i)
and
RSlip_No=(Select CAST(i.RSlip_No as int) from inserted as i)
)
-

((Select Quantity from TranPurchaseOrderDetail where Purchase_OrderNo
=
(Select CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i))

- (Select CAST(i.Quantity as int) from inserted as i))

where RequisitionSlipDetail.ItemCode = (Select CAST(i.ItemCode as
nvarchar(20)) from inserted as i) and RequisitionSlipDetail.RSlip_No =
(Select CAST(i.RSlip_No as int) from inserted as i)

Update TranPurchaseOrderDetail set
TranPurchaseOrderDetail.Quantity =
(Select CAST(i.Quantity as int) from inserted as i)
where TranPurchaseOrderDetail.Purchase_OrderNo = (Select
CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.ItemCode = (Select CAST(i.ItemCode as
nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.PurchaseDetailId =
(Select PurchaseDetailId from TranPurchaseOrderDetail where
Purchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))
from inserted as i))

ENDsantoshborfalkar (santosh.borfalkar@.gmail.com) writes:

Quote:

Originally Posted by

This is the Trigger which is not working properly during Update, no any
record is going to be updated so pls help.
>
I am updating the requisition table when any update in quantity in
podetails table

It's very difficult to tell what might be wrong without any knowledge of
your tables or the business rules.

But I noted a few things that appears ood.

Quote:

Originally Posted by

CREATE TRIGGER trig_updateRequistion ON ERP.DBO.TranPurchaseOrderDetail
INSTEAD OF UPDATE
AS
IF UPDATE(Quantity)

So if the Quantity columns is not mentioned in the SET clause, then you
will not perform any update at all?

Quote:

Originally Posted by

Update RequisitionSlipDetail
set RequisitionSlipDetail.PoQuantity =
>
(Select PoQuantity from RequisitionSlipDetail where
ItemCode=(Select CAST(i.ItemCode as nvarchar(20)) from inserted as i)

This may work, if only one row at a time is updated, but it will fail
with an error if many rows are updated. Recall that triggers fire once
per statement, not once per row.

Quote:

Originally Posted by

Update TranPurchaseOrderDetail set
TranPurchaseOrderDetail.Quantity =
(Select CAST(i.Quantity as int) from inserted as i)
where TranPurchaseOrderDetail.Purchase_OrderNo = (Select
CAST(i.Purchase_OrderNo as nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.ItemCode = (Select CAST(i.ItemCode
as
nvarchar(20)) from inserted as i)
and
TranPurchaseOrderDetail.PurchaseDetailId =
(Select PurchaseDetailId from TranPurchaseOrderDetail where
Purchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))
from inserted as i))

I don't understand this casting business. Why cast the columns of "inserted"
when they are the same as in the target table? A simplified version of
the above could be:

Update TranPurchaseOrderDetail
set Quantity = i.Quantity
FROM TranPurchaseOrderDetail T
JOIN inserted i ON T.Purchase_OrderNo = i.Purchase_OrderNo
AND T.ItemCode = i.ItemCode
and T.PurchaseDetailId =

Quote:

Originally Posted by

(Select PurchaseDetailId from TranPurchaseOrderDetail where
Purchase_OrderNo = (Select CAST(i.Purchase_OrderNo as nvarchar(20))
from inserted as i))

I did not rewrite the last bit, because, frankly, I don't understand what
it's supposed to mean. It just looks strange.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx