Migration Script Failures

ElliotRodriguezElliotRodriguez Posts: 37
edited September 1, 2016 4:59AM in SQL Compare 11
I intend to file a ticket for this, but I wonder if anyone has found a solution in the interim.

I have a new database that I am connecting to SQL Source Control (dedicated database). The source contains 6 migration scripts, a filter, and database comparison options file.

When getting the latest from the repo, I am first prompted to grab the comparison options file, as expected.

When getting the filter, however, the Get Latest window forces me to get migration scripts as well. I use the term "forces" because the checkboxes are grayed out.

When I inspect the script that is being executed, the script does not contain ANY of the migration script contents. Instead it contains the contents of the project's last check-in. So I get errors referencing objects that don't exist, because I can't pull them down, and I can't pull them down because the scripts are run first before any of the dependent objects are created, which makes absolutely no sense to me. They do not appear to be picked up correctly as dependent objects.

Comments

  • I am using SQL Compare instead, and side note - I still do not understand why, if SQL Compare fails a deployment, Roles and Users are not part of a rolled back transaction.

    I received an error deploying from SQL Compare, because DTC was not running, and after I toggled it on and ran the same deployment script, I got another error complaining of a User that was already created. How can a failure not roll back the entire database to the same state it was in before the failure? This has been a long standing issue and I really wish it were addressed (or at least explained).
  • And now SQL Compare fails because the deployment script is STILL trying to execute the migration scripts on the database! And of course they fail because the script references objects that don't exist, which I can't get because the migration scripts are run FIRST.
  • I am using SQL Compare instead, and side note - I still do not understand why, if SQL Compare fails a deployment, Roles and Users are not part of a rolled back transaction.

    I received an error deploying from SQL Compare, because DTC was not running, and after I toggled it on and ran the same deployment script, I got another error complaining of a User that was already created. How can a failure not roll back the entire database to the same state it was in before the failure? This has been a long standing issue and I really wish it were addressed (or at least explained).

    I'll try to get someone to look at the migrations issue since I don't know much about that unfortunately.

    About the users/roles thing - we rely on a transaction to roll back changes if things go wrong, and according to the code I'm looking at the problem is that adding/removing users from a role isn't allowed inside a transaction. Since we have to create users and roles before we can add users to roles, and since we have to add users to roles before we get to start the transaction, all those things end up happening before the transaction starts. However, from some quick experiments on our test servers, it seems like this might only be a SQL 2000 limitation with sp_addrolemember and sp_droprolemember. Since we're removing SQL 2000 support in v12 we might be able to revisit this and get users/roles inside the transaction. I'll make a note to investigate futher
  • Thanks for the clarification. That is interesting regarding the transaction limitation and role creation.

    We love the tool, we love the support we get too - but the object dependency issues we run into make it difficult sometimes. We appreciate the input.
  • We've opened a support ticket for the Migrations issue - hopefully we'll be able to get that sorted out too
Sign In or Register to comment.