SQL Server 2008

I was recently upgrading my NopCommerce version 1.4 to 1.8 , I had visual studio 2008 bit 1.8 runs on Visual Studio  2010 When I installed Visual web express 2010 SqlServer 2008 also got installed on my machine while I had SqlServer 2005. I wanted to continue with it . So I uninstalled sqlserver 2008.

but I was shocked to see that I was not able to connect to sqlserver 2005. My login was changed 

I uninstalled 2005 and reinstalled it Before that I took back up of my Database's files .

when I reinstalled SqlServer 2005 . I tried to attach the database but I was getting the following error.

The database '[path\MDF file] cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

I searched and found that my databases are no longer compatible with SqlServer 2005, as when I installed VisualwebExpress 2010 sqlserver 2008 also got installed and it upgraded my databases too.

Now I have no option other then using sql server 2008.

I struggled with installation of sql server 2008 , As no clear direction found this on SQL Express home page:

 Here is what you can look for.

You can manually download and install any edition of SQL Server 2008 directly from the Microsoft Download Center: SQL Server 2008 Express SQL Server 2008 Express with Tools SQL Server 2008 Express with Advanced Services SQL Server 2008  is available in the following 3 editions  SQL Server 2008 Express SQL Server database engine - create, store, update and retrieve your data SQL Server 2008 Express with Tools SQL Server database engine - create, store, update and retrieve your data SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases SQL Server 2008 Express with Advanced Services SQL Server database engine - create, store, update and retrieve your data SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases Full-text Search - powerful, high-speed engine for searching text-intensive data Reporting Services - integrated report creation and design environment to create reports So make sure you download the SQL Server 2008 Express with Tools(It will install Managment Studio as a tool) Here are the prerequisites for installing SqlServer 2008 Express with tools Download and install  Microsoft .Net Framework 3.5 SP1. Step 2 Download and install Windows Installer 4.5  [important of you have pre-Vista OS]. . Step 3 Download and install Windows PowerShell 1.0 And one more thing: If you have non-SP1 version of Visual Studio 2008, you will be stopped at the very last step before the install begins!!! So if you have VS2008 or any 2008 Express products, upgrade them to SP1 to install the Tools version to get your Management Studio Express.

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en

 Uninstall the existing sql server and it's components

 Uninstall the "SQL Server 2008 Express" installation completely from your Windows Server.
 Reboot for sure.. but it's not necessary
 

 Install "Microsoft SQL Server 2008 Express with Tools" (SQLEXPRWT_x64_ENU.exe)

1. If you run "SQLEXPRWT_x86_ENU.exe", you'll come across a "SQL Server Installation Center" window.
2. You normally click at the "Installation" tab on the left pane, and click "New SQL Server stand-alone installation or add features to an existing installation".
3. A "SQL Server 2008 Setup" installation window appears, and have the following steps included:
- Setup Support Rules
- Installation Type
- Product Key (depending on the type of installation type you selected)
- and so on...
4. Once you get to the "Installation Type" step of the installation, instead of adding a new feature to the existing SQL Instance (that I initially setup as SQLExpress), try the install new instance, or something to that effect. There are only two options in this portion, so it won't be difficult to select.
5. Click next until you reach the "Feature Selection" step of the installation. There, select All

(If you ll only select management studio not the Database engine , Your installation will be done successfully but you ll not be able to connect to server as there will be no services installed and restarted)

6. Install on 

System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

I deployed the DashCommerce on Godaddy Server .

when i hit for Register.aspx ( Register Link on Main page) below is the error which i got.

System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

I my development environment I solved this error by running aspnet_regsql.exe 
and fresh install of ASP Schema table but on my Godaddy Server 
I have no permissions to run aspnet_regsql.exe 

I checked my dashcommerce database and I found that above StoredProcedure was not even there.

so I generated the Script of ASPNETDB from my local database and executed all the stored procedures .

on godaddy DB server including aspnet_CheckSchemaVersion'.

It works I got the Register.aspx Page.

Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint.

I deployed DashCommerce on godaddy When I hit

Server/RoorDir/install/install.aspx to deploy the database i got the below error

Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint.
I figured out we can not deploy by hitting the install.aspx as it first deletes all the tables

including ASPSchema tables (here aspnet_Roles) for which I don't have permission on Godaddy server so I ran the SqlScripts in QueryAnalyzer

but again Dash Commerce Doesn't provide consolidated script so it took me some time n effort to figure in what order we have to run the sql scripts

below is the Order in which I ran the Script on godaddy database server

Scripts are here

dashCommerce 3.4.438 Source\Web\install\scripts

order is this

tables.sql

functions.sql

storedprocedures.sql

basedata.sql

basedata.it-IT.sql

basedata.pt-PT.sql
views.sql

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

I was working with ASP.NET classifieds starter kit.
When I Clicked on Post an Ad this is the error which i got


The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.


[ProviderException: The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.]

   System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +1976968

   System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +89

   System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +815

   System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105

   System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42

   System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78

   System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +60

   System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +119

   System.Web.UI.WebControls.Login.AttemptLogin() +115

   System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101

   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37

   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118

   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166

   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10

   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13

   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36

   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Solution :

In ASP.NET there is an API which allows Logins Membership maintained by ASPNETDB Database

Many of the Microsoft ASP.NET 2.0 providers are SQL providers—providers that persist state in SQL Server (or SQL Server Express) databases. The SQL providers include SqlMembershipProvider, SqlRoleProvider, SqlProfileProvider, SqlSessionStateStore, SqlWebEventProvider, and SqlPersonalizationProvider. Each stores data using a predefined schema.

 The Aspnet_regsql.exe tool that comes with ASP.NET 2.0 creates a SQL Server database with a compatible schema. 

That database, which is named aspnetdb by default, will hereafter be referred to as the SQL provider database or simply the provider database.

1.)Now First Check the databse reffered by SqlMemberShip Provider in Web.Config File.

in my case this is it

<membership>

   <providers>

    <clear/>

    <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="classifiedsConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>

   </providers>

  </membership>

Note : Make sure Some 

applicationName="Myclassified" is set in the above SqlMembershipProvider 

<connectionStrings>

  <add name="classifiedsConnection" connectionString="Data Source=LAKSH\SQLEXPRESS;Initial Catalog=ClassifiedStarterKit;Integrated Security=True;" providerName="System.Data.SqlClient"/>

 </connectionStrings>

 <appSettings>

2.) Now Second step is to Install the ASPNETDB in the referring DB by SQLMembershipProvider

    in my case it is reffering DB Specified by 

classifiedsConnection which is 

ClassifiedStarterKit

 To install the Tables , Scripts everything of ASPNETDB in 

ClassifiedStarterKit Db

Run the aspnet_regsql.exe from the below path by double clicking on it

C:\WINDOWS\microsoft.net\framework\v2.0.50727

3.) Follow the wizard steps give Your Server Name and select the DB (

ClassifiedStarterKit ) where your SqlMembershipProvider is pointing

4.)Above Step will install the Tables , Scripts, Procedures everything in your DB (

ClassifiedStarterKit )

5.)Now open the table aspnet_SchemaVersions

and check th entries 

common 1 True

membership 1 True

personalization 1 True

profile 1 True

role manager 1 True

Above data should be there if it is not Explicitly copy it in the table

Now it works!