When an instance of TFS2008 (or 2005) is to be upgraded to TFS2010 the SQL Server must be upgraded to 2008 (sp1) as a minimum. I expected this to be the smallest issue but the problem that:

"Rule 'Consistency validation for SQL Server registry keys' failed". The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.

It turned out to be a problem with the permissions for a registry key for SQL Server. Even though I tried to upgrade with an account with local admin rights there was a registry key which didn't allow access. For this particular instance it was the key:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\UDDI\MSSQLServer\SuperSocketNetLib\Lpc

The solution was to add the account which was used for the upgrade to have full access to this key. Probably write acces would have been sufficient.

The way to find out which key(s) it is/are is to look in the SQL Server installation log:

Folder: .\Microsoft SQL Server\100\Setup Bootstrap\LOG\ ("and then the date and time of the upgrade attempt")

Look for "System.UnauthorizedAccessException" in the Details.txt file

///Jørn

I use System.Data.SqlServer.SqlCeEngine() quite a lot in all my projects. I normally create the database on the fly when the application is launched for the first time and then I populate the initial data via a web service.

I often check if database objects exist before I create them. You can do this by querying the INFORMATION_SCHEMA views. I created a helper class called SqlCeEngineEx that contains the following methods for querying the INFORMATION_SCHEMA:

1) bool DoesTableExist(string table) - Checks if a table exists in the database
2) string[] GetTables() - Returns a string array of all the tables in the database
3) string[] GetTableConstraints(string table) - Returns a string array of all the constraints for a table
4) string[] GetTableConstraints() - Returns a string array of all the constraints in the database

And here is the full code:

public class SqlCeEngineEx : IDisposable
{
  private SqlCeEngine engine;

  public SqlCeEngineEx()
  {
   engine = new SqlCeEngine();
  }

  public SqlCeEngineEx(string connectionString)
  {
   engine = new SqlCeEngine(connectionString);
  }

  public bool DoesTableExist(string tablename)
  {
   bool result = false;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText =
      @"SELECT COUNT(TABLE_NAME)
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME=@Name";
     cmd.Parameters.AddWithValue("@Name", tablename);
     result = Convert.ToBoolean(cmd.ExecuteScalar());
    }
   }

   return result;
  }

  private string[] PopulateStringList(SqlCeCommand cmd)
  {
   List<string> list = new List<string>();

   using (SqlCeDataReader reader = cmd.ExecuteReader()) {
    while (reader.Read()) {
     list.Add(reader.GetString(0));
    }
   }

   return list.ToArray();
  }

  public string[] GetTables()
  {
   string[] tables;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
     tables = PopulateStringList(cmd);
    }
   }

   return tables;
  }

  public string[] GetTableConstraints()
  {
   string[] constraints;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText = "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS";
     constraints = PopulateStringList(cmd);
    }
   }

   return constraints;
  }

  public string[] GetTableConstraints(string tablename)
  {
   string[] constraints;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText =
      @"SELECT CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
       WHERE TABLE_NAME=@Name";
     cmd.Parameters.AddWithValue("@Name", tablename);
     constraints = PopulateStringList(cmd);
    }
   }

   return constraints;
  }

  public string LocalConnectionString
  {
   get { return engine.LocalConnectionString; }
   set { engine.LocalConnectionString = value; }
  }

  public void Compact()
  {
   engine.Compact(null);
  }

  public void Compact(string connectionString)
  {
   engine.Compact(connectionString);
  }

  public void CreateDatabase()
  {
   engine.CreateDatabase();
  }

  public void Repair(string connectionString, RepairOption options)
  {
   engine.Repair(connectionString, options);
  }

  public void Shrink()
  {
   engine.Shrink();
  }

  public bool Verify()
  {
   return engine.Verify();
  }

  public void Dispose()
  {
   engine.Dispose();
   engine = null;
  }
}