Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2016
    Posts
    19

    Answered: overriding "The schema update is terminating because data loss might occur"

    Hello,

    I was trying to do an update to one of my tables and I got this error message:


    (48,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
    (43,0): SQL72045: Script execution error. The executed script:
    IF EXISTS (SELECT TOP 1 1
    FROM [dbo].[Bowtie])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
    WITH NOWAIT;
    An error occurred while the batch was being executed.


    I'm essentially try to convert an nvarchar(max) column into an int column. Some of the rows do have non-null values. But I looked through them manually and I'm OK with losing them.

    So is there a way of overriding this restriction in SQL? I've tried commenting out the IF line above but I still get a similar error.

    Thanks.

  2. Best Answer
    Posted by gvee

    "You're using SSDT to update things... I love SSDT!

    There's an option you can set in your publish settings to disable the data loss check...

    On the publish screen, click Advanced... Un-tick the "Block incremental deployment if data loss might occur" option!


    Alternatively, if this is a one-off; chose to generate script, instead of publish, and then you can tweak it to your hearts content."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,858
    Provided Answers: 17
    It looks like you may be modifying a script that was written to do a different change to the table. The message you are seeing is generated by the RAISERROR statement. You should thoroughly understand the script before making many modifications to it. This safety exit and the condition that triggers it, may be there for a very good reason, but we can not tell you what that reason may be.

  4. #3
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by MCrowley View Post
    It looks like you may be modifying a script that was written to do a different change to the table. The message you are seeing is generated by the RAISERROR statement. You should thoroughly understand the script before making many modifications to it. This safety exit and the condition that triggers it, may be there for a very good reason, but we can not tell you what that reason may be.
    Yes, I know exactly why it's there, and I know what's generating the script. The script is generated by Visual Studio when I publish my Database project to the database. The check for non-null values in the column I want to change is to prevent data loss from occurring. If I convert a nvarchar(max) column to an int column, and there are values under that column, those values will be forced to be wiped away. This check is to prevent that from happening.

    But I did mention in the OP that I've checked the values and I'm OK with wiping them away. I assure you that I would not override such a check in any other situation, but it is safe in this situation to do so.

    Also, like I said in the OP, I tried commenting out this check in the script but I still get the same error. What I'm wondering is, is there a way in the SQL language to override this check (for example, when doing an ALTER TABLE statement)?

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    You're using SSDT to update things... I love SSDT!

    There's an option you can set in your publish settings to disable the data loss check...

    On the publish screen, click Advanced... Un-tick the "Block incremental deployment if data loss might occur" option!


    Alternatively, if this is a one-off; chose to generate script, instead of publish, and then you can tweak it to your hearts content.
    George
    Home | Blog

  6. #5
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by gvee View Post
    On the publish screen, click Advanced... Un-tick the "Block incremental deployment if data loss might occur" option!
    That's great! Thanks very much gvee. I agree with your sentiments about SSDT. It is pretty seamless.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •