Ch. 12 Connecting to the SQL Server Sample Database

KingJames978 · April 3rd, 2012, 02:35 PM

I am having trouble connecting PlanetWrox.mdf in Solution Explorer. I followed all the instructions on pages 406 and 407 including going to Ch. 19 NTFS settings for Planet Wrox which first I completed the Try it out pg. 707 Using The Copy Website Option and created a release folder in C:\BeginningASPNET,C-sharp on my hard drive. Then I did the pg. 707 Try it out Configuring the File System which I set the permissions with the release folder and added Authenticated Users to Group or User names. I completed that Try it out till step 5. I wasn't sure where to go after that step. Then I opened DatabaseTest from (C:\BeginningASPNET,C-sharp\Release\DatabaseTest) where I copied the PlanetWrox.mdf database file in the App_Data folder then when I double clicked it I got the 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."

I did a side-by-side installation which I installled SQL Server 2008 R2 Standard on my machine which is running on Windows 7 64-bit. The named instance of SQL Server 2008 R2 is MSSQL2008R2 and the instance ID is MSSQL2008R2.

My web.config code:

Code:

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\MSSQL2008R2;Integrated Security=SSPI;AttachDBFilename=C:\BeginningASPNET,C-sharp\Release\DatabaseTest\App_Data\PlanetWrox.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

  <system.web>
    <compilation debug="false" targetFramework="4.0" />

    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login.aspx" timeout="2880" />
    </authentication>

    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
             enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
             maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
             applicationName="/" />
      </providers>
    </membership>

    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
      </providers>
    </profile>

    <roleManager enabled="false">
      <providers>
        <clear/>
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" />
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
      </providers>
    </roleManager>

  </system.web>

  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
</configuration>

I changed in the connectionString the data source

Code:

"data source=.\SQLSERVER;

to

Code:

"data source=.\MSSQL2008R2;

a screen shot of my Database Explorer is found at:

http://s1257.photobucket.com/albums/...seExplorer.png

I don't know where to start on how to get the PlanetWrox.mdf to work. Any suggestions?

Imar · April 3rd, 2012, 03:27 PM

Hi there,

This question has come up a few times. Usually, this post helped:

Problem using SQL Server Express 2008

If you're not using Express (e.g. now that you're targeting MSSQL2008R2), remove the User Instance=True attribute from the connection string.

Hope this helps,

Imar

KingJames978 · April 4th, 2012, 01:33 PM

I changed the data source back to SQLSERVER, changed the User Instance to False in the connection string. I don't think in this exercise it takes information from the connection string since the database is runned in Database Explorer. In Database explorer, I right clicked PlanetWrox.mdf and selected modify connection then clicked Advanced and changed that User Instance to False, clicked ok and clicked ok again and the connection succeeded. When I re-open the site the User Instance in Database explorer goes back to false which I don't get even if you save all the files. I don't know if there is a way to save the changes made in Database Explorer? I have SQL Server 2005 Express still on my computer and the Database I guess still runs on that vs. SQL Server 2008 R2. Changing the User Instance to False in Database Explorer was key. I appreciate the help with this issue.

Imar · April 4th, 2012, 02:12 PM

Is the SQLSERVER instance an Express edition? If not, you should either install it (highly recommended, and easy to do using the Web Platform Installer) or update your connection string to use Initial Catalog rather than the AttachDBFilename attribute. Appendix B shows you more.

Cheers,

Imar

KingJames978 · April 4th, 2012, 06:57 PM

I tried to install SQL Server 2008 R2 Express and I had a hard time with the registry through the setup struggling to install the Standard version and the Express version reinstalling both versions, which after a month a I finally got the Standard version to install. I don't know if SQL server will allow you to have the 2005 and 2008 express versions to run on the same machine. The SQL Server 2005 Express Management Studio won't load, but the SQL Server 2008 R2 Management Studio will run.

Is my connection string supposed to be:

Code:

<connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\MSSQL2008R2;Initial Catalog=PlanetWrox.mdf;" providerName="System.Data.SqlClient"/>
</connectionStrings>

?

SQL Server 2005 Express uses the Data source SQLEXPRESS. SQL Server 2008 R2 uses the Data source MSSQL2008R2. The MSSQL2008R2 runs in Database explorer with User Instance= False. I can't figure out how to find the data source in the Management Studio of SQL Server 2008 R2 and the User Instance.

Imar · April 5th, 2012, 01:47 AM

Quote:

I don't know if SQL server will allow you to have the 2005 and 2008 express versions to run on the same machine.

You certainly can. On my development machine I have 2005, 2008, 2008R2 and 2012 and various flavors of each all running side by side.

Quote:

The SQL Server 2005 Express Management Studio won't load, but the SQL Server 2008 R2 Management Studio will run.

I think that's ny design; newer version upgrade the tools when installed.

Quote:

I can't figure out how to find the data source in the Management Studio of SQL Server 2008 R2 and the User Instance.

It's what you log in to using SSMS.

A User Instance is connection specific and should only be used on Express....

Cheers,

Imar

KingJames978 · April 5th, 2012, 01:19 PM

I am going to stick with SQL Server 2008 R2 Standard since I was able to install it and look at outside resources if I have a problem. The User Instance, great point, I would have struggled with that probably with standard. Is there an alternative name for User Instance in the Standard version?

Imar · April 6th, 2012, 02:39 AM

Nope; the non-express installations run as server instances usign a service account and this don't support user instances.

Cheers,

Imar