p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 (http://p2p.wrox.com/forumdisplay.php?f=389)
-   -   Chapter 18: Getting "Failed to generate a user instance of SQL Server" error (http://p2p.wrox.com/showthread.php?t=76945)

HungryCaterpillar November 7th, 2009 11:39 PM

Chapter 18: Getting "Failed to generate a user instance of SQL Server" error
 
I was able to get my begASPNET/Release site to work under IIS, as described in the book, but I get variants of the following error every time I access the database (Reviews>By Genre, Gig Pics, upon login, etc.):

Code:

Server Error in '/' Application.
--------------------------------------------------------------------------------
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.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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.

Source Error:

Line 23:
Line 24:            Repeater1.DataSource = allGenres;
Line 25:            Repeater1.DataBind();
Line 26:        }
Line 27:    }
 

Source File: c:\begASPNET\Release\Reviews\AllByGenre.aspx.cs    Line: 25

Stack Trace:

[SqlException (0x80131904): 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.]
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4845255
...

My connection string is:
Code:

<connectionStrings>
                <add name="PlanetWroxConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PlanetWrox.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
        </connectionStrings>

I'm running Windows XP and using SQL Express for the PlanetWrox database, but I also have SQL Server 2005 installed. I'm using the Professional Edition version of Visual Studio 2008.

I did the file system setup steps described in the "Configuring the File System" Try It Out. I also Googled the error message and found some instructions about running sp_configure 'user instances enabled' 1 in SQL Server Mgmt Studio and deleting the Microsoft SQL Server Data\SQLEXPRESS directory, but to no avail.

One thing I noticed was that the \SQLEXPRESS directory is created and populated with: master.mdf, mastlog.ldf, msdbdata.mdf, and msdblog.ldf when I try to access the database (in c:\Documents and Settings\ASPNET\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS). So something does seem to be at least partially working.

Any thoughts or suggestions on what else to try would be appreciated. I can't really tell from the error message what's actually failing or what kind of thing needs to be corrected.


Thanks,

Geoff

Imar November 8th, 2009 03:48 AM

Hi Geoff,
Quote:

but I also have SQL Server 2005 installed.
And do you want to use this version, or the Express edition? You often get this error when you try to target a SQL Server version that doesn't have user instances enabled, but the connection string tries that anyway.

You can try a few things:

1. Remove User Instance=True from the connection string

2. Change your connection string so it doesn't target the Express edition, but your full version of SQL Server.

I think you already saw this article: http://social.msdn.microsoft.com/For...5-cac99740949b

If not, take a look as it contains a lot of useful tips.

If this doesn't help, can you provide more info? E.g. whether you're on the machine directly or using remote desktop, whether you ran "sp_configure 'user instances enabled'" against the Express edition and any other relevant information?

I've seen this problem reported many times before, every time with a different fix, so it's not easy to fix it for you remotely... ;-)

Cheers,

Imar

HungryCaterpillar November 9th, 2009 12:02 AM

Imar,

Thanks for the quick response. I tried removing "user instance=true" from my connection string and got the following error:

Code:

Server Error in '/' Application.
--------------------------------------------------------------------------------

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\begASPNET\Release\App_Data\PlanetWrox.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\begASPNET\Release\App_Data\PlanetWrox.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 23:
Line 24:            Repeater1.DataSource = allGenres;
Line 25:            Repeater1.DataBind();
Line 26:        }
Line 27:    }
 
Source File: c:\begASPNET\Release\Reviews\AllByGenre.aspx.cs    Line: 25

Stack Trace:

[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\begASPNET\Release\App_Data\PlanetWrox.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4845255
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194

I can't tell if this is due to a permissions error or just that I already have a PlanetWrox.mdf file in my App_Data folder.

To answer your questions, I am running this locally on my machine - that is, I'm using "http://localhost" as the URL in my browser to connect to it. Is that what you meant by "on the machine directly or using remote desktop" ?

Also, I ran my "sp_configure user instances enabled" command from my Microsoft SQL Server Mgmt Studio connected to <machine name>\SQLEXPRESS. I think I was using the master database.

Maybe I am actually using SQL Server 2005 instead of SQLExpress for my PlanetWrox database. I was just following the Try It Out instructions since Chapters 11 and 12 and assuming I was using SQLExpress, but maybe I've really been using SQL Server 2005 all along. The connection string has always had "sqlexpress" in it, though, just as entered on page 395.

I had read the article you referenced and reread it again more thoroughly. There's a few mentions of "modifying the sql server (sql express) service to use your local or system account", but I wasn't able to find the SQL Configuration Tools for SQLExpress mentioned, or any mention of SQLExpress in my Start menu or even in add/remove programs, so I couldn't try that. The only reference to sql express I can find is within my SQL Server Mgmt Studio. I'm using my work computer which had that preinstalled.

Maybe I should assume I'm using SQL Server 2005 and use your instructions in Appendix B to set up my site that way ? Sorry to sound so clueless.


Thanks,

Geoff

Imar November 9th, 2009 12:54 PM

Quote:

Maybe I should assume I'm using SQL Server 2005 and use your instructions in Appendix B to set up my site that way ?
Yes, you can always do that, as follows:

1. Attach the database to a non-Express version of SQL Server

2. Add the web server account (ASPNET or Network Service) (or use SQL Server security)

3. Grant permissions to this account

4. Change your connection string to point to your instance of SQL and use Initial Catalog instead of the attachdbfilename

Hope this helps,

Imar

HungryCaterpillar November 9th, 2009 09:19 PM

Imar,

Your suggestion worked! I now have a fully functional PlanetWrox web site running on my laptop, using IIS and SQL Server 2005!

It was gruelling working through all the steps in Appendix B, but the time I spent working on my issues in Chapter 18 made it more managable.

Thanks for all your help. This book has been a great learning experience for me, and the Programmer Forum has been very helpful, too!


Sincerely,

Geoff

Imar November 11th, 2009 01:56 PM

Excellent. Glad it's all working now, and thanks for the update.

Cheers,

Imar


All times are GMT -4. The time now is 01:41 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.