Warning "No default value" despite using migration script

mfearbymfearby Posts: 24
This is my first attempt at a migration script, and my scenario is exactly the one documented here: http://documentation.red-gate.com/display/SOC3/Worked+example+-+deploying+with+migration+scripts, that is, adding a column that doesn't allow nulls.

So I followed the guide faithfully and now that I'm checking in my changes, SQL Source Control 3.6.3.149 is still warning me as follows:

Severity: High
Object: tbMyTable
Title: The column [field_type] on table [dbo].[tbMyTable] must be added but has no default and does not allow NULL values. The table must be rebuilt. If the table contains data then the deployment script will fail.

Does this warning always show up, regardless of whether a migration script is present, or have I perhaps done something wrong? The guide makes no mention of this warning still being displayed.
I don't always test my code, but when I do, I do it in production :-p

Comments

  • I decided to acknowledge the warning and proceed with the commit anyway (I'm linked to VisualSVN Server). This seemed to be successful, so I fired up SQL Compare and it's complaining as follows:

    1 of 1 warning: Error retrieving migration scripts

    The migration script was saved successfully to the repo because I did an SVN Update with TortoiseSVN on my computer and it brought down the new folder created especially for these scripts.

    Now I am well and truly at a loss. I'll probably run my script customisations manually and just remove the allow nulls on the field afterwards, I guess. I'd still like to know why things are failing miserably. I don't think I'm asking too much of it :-) THanks.
    I don't always test my code, but when I do, I do it in production :-p
  • If I click on the Migration Scripts tab it shows my one and only migration script and when I click edit, up it comes, and all my customisations are there. I've also closed/reopened SQL Compare several times.
    I don't always test my code, but when I do, I do it in production :-p
  • By the way, it would nice if the following article explained more about the error, rather than just telling you how to ignore migration scripts altogether:

    http://documentation.red-gate.com/displ ... n+warnings

    I do actually want to use a migration script, but it won't let me and won't tell me why. :-(
    I don't always test my code, but when I do, I do it in production :-p
  • Well, I can't see to catch a break! I decided to delete the migration script in source control (and commited its removal successfully), and now when I go to use SQL Compare (after closing it and reopening it) it STILL says "Error retrieving migration scripts". It's really got a bee in its bonnet about them now.
    I don't always test my code, but when I do, I do it in production :-p
  • The "Review deployment script" step in SQL Compare now shows 2 warnings:

    Severity: High
    Title: An error occurred connecting to source control; migration scripts could not be retrieved.

    If you do not want SQL Compare to try and retrieve migration scripts for future comparisons with this project, edit the project and
    select the Ignore migration scripts for databases project option.


    Severity: Low
    Title: SQL Compare cannot verify database is identical to associated revision.



    Damn, these migrations scripts don't seem like a good idea at all! :-(

    And yes, I am aware that I appear to be talking to myself here!
    I don't always test my code, but when I do, I do it in production :-p
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Marc,

    I'm sorry you are having a problem with SQL Compare - it should tell you about the connection problem but it does not. There was a short-lived version of SQL Compare 10.5 that could not retrieve migration scripts, so consider upgrading if you are on that version.

    I will reply to you via email with some debugging instructions to try to find the sepcific failure cause if you are on SQL Compare 10.7.
  • I'm using SQL Compare 10.7.0.18
    I don't always test my code, but when I do, I do it in production :-p
  • Problem solved. After trying to run your debug program (sent via email), and seeing that it said "Access is denied", it got me thinking. I'm running SQL Compare as a separate user because (until now) we've tried to keep the product database more secure by using separate logins to administer it, one different to my everyday account. I had modified my SQL Compare shortcut like this:

    C:\Windows\System32\runas.exe /user:my_domain\!other_account "M:\Program Files (x86)\Red Gate\SQL Compare 10\RedGate.SQLCompare.UI.exe"

    We've decided that it's now more trouble than it's worth (not to mention I have to enter my credentials too often), and now that we've given my everyday account access to the production SQL Server, everything is OK :-)

    Thanks.
    I don't always test my code, but when I do, I do it in production :-p
Sign In or Register to comment.