Publishing database changes with SSDT that include adding columns to the existing table in the target database(which was previously created through SSDT).Instead of creating a new SSDT database. Trying to add a column through table create a script or through alter table script.i was Encountered by the same error. Even tried through Schema Compare still the table in the target database encountering with the same error. How to alter the changes to the target database objects through SSDT publishing?
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT;
I tried by checking out the
-
Block incremental deployment if data loss might occur.
-
Don't ALTER replicated objects.
-
Do not alter Change DATA Capture Objects.
Best Answer
This is happening because you've added the column in the middle of the
CREATE TABLE
script. This causes a "table rebuild" to happen. I talked about this on my blog a few months ago: SSDT problems: Table RebuildsThe section on column ordinality is the specific problem you have. Say you have a table like this:
And then add a column in the middle of the column list:
This will "rebuild" the whole table in order to keep the underlying table metadata in sync between the model (your SSDT project) and the target database.
The solution? Just add the column to the end:
This doesn't rebuild the table, and thus doesn't cause potential data loss.