Error on Getting Latest Version after adding new column

gknierimgknierim Posts: 31
edited August 2, 2012 6:57PM in SQL Source Control Previous Versions
I have added a new column to an existing table that does not allow NULL. I have also specified a default value for the column. However, when the other developers try to get latest, they are getting the following error:

Cannot insert the value NULL into column 'DataSourceID', table 'Fikes.SkylineSP.tmp_rg_xx_dm_Current_CompetitorPriceHistory_Daily'; column does not allow nulls. INSERT fails.

I saw a post from Dec 2010 that said my options were to clear out the table of data or manually add the column and the default value.

Is there a better way to fix this problem now with Migrations scripts somehow?

Thanks,
Greg
· Share on Twitter

Comments

  • gknierimgknierim Posts: 31
    UPDATE: So, I decided to setup the Migration scripts and I created a migration script for my specific revision. However, when the other developer tried to get latest, we got the same error.

    I found out that even though the Migration script is created, it is not run when a Get Latest is executed. Is this correct?

    We ended up running the migration script manually and then doing a Get Latest worked on the remaining objects.

    Seems to me that SQL Source Control should know what revision you are on, and look for the migration scripts to run and run them automatically. If it is supposed to do this, then what am I doing wrong?

    Also, it would be nice if I could run the Migration script from somewhere instead of editing it, copying all of the text and then pasting it in a separate window.

    Thanks,
    Greg
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Get Latest should pick up migration scripts. Have you checked that all the developers have specified the migration scripts folder in the Setup Tab in SQL Source Control?

    Also, it's possible that only once you set up this migrations folder the revision extended property gets written to your database. This is what is used to determine which migration scirpts get picked up, so if you set up migrations on machines after you've added a new migration script, it won't work the first time because the extended property won't be there.

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • gknierimgknierim Posts: 31
    Yes, I made sure the folder was setup correctly. I think what you are trying to say in your 2nd paragraph is that I should've had the migrations folder setup before I check in my change that required a migration script? If not, then I don't follow.

    My order of events were this: Made the schema change and checked it in. Setup the Migrations folder and created a script pointing to that revision. Then, I had the other developer setup his migrations folder and do a get latest. If I did something out of sequence let me know. I don't have an immediate need to to do another script so maybe it will work next time.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Can you check that the database that you're doing 'get latest' to has the SQLSourceControl Database Revision extended property? Right click on the database in the Object Explorer, select Properties, then Extended Properties.
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • gknierimgknierim Posts: 31
    Yes, the database has the extended properties.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    That's strange. If the migration script has revision numbers greater than that of the database they should be picked up.

    Could you try using SQL Compare to deploy from source control to a new database (empty) to check that this picks up the migration script?
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • gknierimgknierim Posts: 31
    We don't use SQL Compare so the only thing I could try would be to create a new database and link it to SQL Source Control. However, that will probably work since there is no data in those tables.

    It could very well be that the revision that the database was at was later than the revision that I made the migration script for since there was additional revisions after the one I needed the script for. I did not check the revision number before the get latest was attempted.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    If the database revision was later, then the migration script wouldn't have been picked up.

    SQL Compare has a fully functional 14-day trial and is a useful tool for troubleshooting. If it picks up migrations scripts, they will be displayed in the comparison results screen with a different icon against the changes.
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • gknierimgknierim Posts: 31
    The only thing I did notice is that when we went to get latest, the migration script was not in the list of other objects on the Get Latest tab. So, that was probably the issue.

    Needless to say, we ran it manually, and everything else worked so we are ok and now know for next time.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Unfortunately we don't yet list the migration scripts on 'get latest'. However, if they are picked up, a 'warning' is displayed after get latest is selected so you're aware that it has been picked up.

    Do let us know if this doesn't work next time as we'd like to work with you to resolve this.
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.