Archives / 2005 / September
  • Schema Changes in the latest ASP.NET V2.0 bits

    I just installed the latest version of Visual Studio 2005 and the .NET Framework.  I went from version v2.0.50215 (common February version) to v2.0.50727 (August version).  The install went smooth and everything was good for the most part.

    One issue I ran into though is that the Membership provider didn't work.  I received an error "Procedure or function aspnet_Membership_GetPasswordWithFormat has too many arguments specified.".  Fortunately the error is descriptive enough to be quite obvious.  The database schema has changed slightly between the two builds.  I am using a SQL 2000 database that was prepared using the v2.0.50215 aspnet_regsql.exe tool so the latest version of the framework doesn't match with the schema on the database.

    The difficult way
    To let you learn from my mistakes, I'll explain what I tried at first.  To upgrade I created a backup of the database, ran the v2.0.50727 aspnet_regsql.exe tool to remove the schema with the goal to ran it again to create the schema back again.  The first issue was the v2.0.50727 aspnet_regsql.exe tool ran into errors removing the schema which was created using v2.0.50215.  After some manual adjustments I was able to complete it.  Then I ran aspnet_regsql.exe again to create the schema on the new database.  I then copied over just the relevant data from the backup database to the new live database.  This worked fine but it was a lengthy process.

    The better way
    I'm kicking myself for not thinking of it before but as an experiment I created a test database using the old v2.0.215 schema and ran the aspnet_regsql.exe tool, but this time I just selected the option to "Configure SQL Server for application services".  Sure enough it upgraded everything and didn't mess with my data.  The ASP.NET team made upgrading between minor versions almost too easy.

    When upgrading to a newer version of the ASP.NET framework, simply run the aspnet_regsql.exe tool and select "Configure SQL Server for application services."  It serves as an upgrade option.

    I'm not promising that this will work between all minor upgrades, but it did work between this one.  Warning: Make sure to create a backup of your database before any of these changes, even if it has worked properly in the past.  Any large change of any sort should be preceded by a fresh backup of the database.