none
Error -214721790: failed to execute SQL string

    Question

  • I heard about the beta relase of the TFS TIP and downloaded a copy...our team still needs a tool for our consolidation efforts.

    I'm attempting to installthe TFS TIP on a Hyper-V Windows Server 2008 R2 server that has TFS 2010 RTM installed, Visual Studio 2008 Team Suite+SP1, Visual Studio 2010 Ultimate, and SQL Server 2008 R2 RTM.

    I'm able to run the installer and add the necessary settings (Domain Account, SQL Instance, etc) but in the middle of the installer I'm getting the following error:

    Error -214721790: failed to execute SQL string, error detail: The state of the target database Tfs_IntegrationPlatform, is not set to ONLINE. To deploy this database, it's state must be set to ONLINE., SQL key: CreateDatabaseScript SQL string: IF(DB_ID(N'Tfs_IntegrationPlatform') IS NOT NULL AN...

    Has the TFS TIP been tested with SQL Server 2008 R2?

    I'm sure happy that this is a Hyper-V demo environment...the installer just crashed ungracefully and god knows what changes it did to the environment..at least with snapshots I can roll back.

    Wednesday, May 05, 2010 2:00 PM

Answers

  • We had this situation reported by another user for the first time just a few days ago and we have an active bug tracking the problem.  We believe this is a race condition due to the fact that the SQL extensions in WiX for managing a DB deployment require that the DB is available before running a script.  That leads people to a pattern like the one we use:

        <!-- Deploy a new DB -->

        <Component Id="DeployNewDatabase" Guid="958FBAE1-F712-45f4-AC65-EA0731EEE6F6">

          <Condition><![CDATA[(EXISTINGDBVERSION = "DBNOTFOUND")]]></Condition>

          <sql:SqlDatabase Id="CreateIntegrationDB" Database="[DATABASENAME]" Server="[DATABASESERVER]" CreateOnInstall="yes" CreateOnUninstall ="no" ContinueOnError="no">

            <sql:SqlScript Id="CreateDatabaseScript" BinaryKey="MigrationToolSqlScript" ExecuteOnInstall="yes" />

     

    That first SqlDatabase line creates a DB and the problem happens because the DBPro-generated script seems to find that the new DB is not ONLINE when it runs.  The failure was not repeatable in the first case where it was encountered, so retrying the install is probably the simplest thing to try.  There are uglier workarounds, but that is the place to start.

     

    Bill

    Wednesday, May 05, 2010 6:25 PM
    Moderator

All replies

  • We had this situation reported by another user for the first time just a few days ago and we have an active bug tracking the problem.  We believe this is a race condition due to the fact that the SQL extensions in WiX for managing a DB deployment require that the DB is available before running a script.  That leads people to a pattern like the one we use:

        <!-- Deploy a new DB -->

        <Component Id="DeployNewDatabase" Guid="958FBAE1-F712-45f4-AC65-EA0731EEE6F6">

          <Condition><![CDATA[(EXISTINGDBVERSION = "DBNOTFOUND")]]></Condition>

          <sql:SqlDatabase Id="CreateIntegrationDB" Database="[DATABASENAME]" Server="[DATABASESERVER]" CreateOnInstall="yes" CreateOnUninstall ="no" ContinueOnError="no">

            <sql:SqlScript Id="CreateDatabaseScript" BinaryKey="MigrationToolSqlScript" ExecuteOnInstall="yes" />

     

    That first SqlDatabase line creates a DB and the problem happens because the DBPro-generated script seems to find that the new DB is not ONLINE when it runs.  The failure was not repeatable in the first case where it was encountered, so retrying the install is probably the simplest thing to try.  There are uglier workarounds, but that is the place to start.

     

    Bill

    Wednesday, May 05, 2010 6:25 PM
    Moderator
  • I was able to get past this error by doing the following.

    I had rolled back to a previous Hyper-V snapshot and re-ran the installer. It failed at the same place and then the installer crashed (ungracefully). I tried to install a second time (as recommended) and the installer completed successfully a second time.

    I might be a good idea to make the crash atleast crash more gracefully or give a retry button.

    Thanks!

    Wednesday, May 05, 2010 8:28 PM
  • Agree.  Thanks for the follow up.

    Bill

    Wednesday, May 05, 2010 10:03 PM
    Moderator
  • This issue should be fixed in the next drop of the TFS Integration Platform to CodePlex.  One database that exists before running the DBPro generated script is master.  We don't need to create the Tfs_IntegrationPlatform DB first and have removed that logic.

    Bill

    Tuesday, May 11, 2010 3:58 PM
    Moderator