0

I suddenly felt into issue when schema which I have edited for table doesnt applied on server. Found that out when wrote seed script and it crashed: I've changed most fields to null so script inserts only NOT NULL values. But in runtime it crashes with 'Expected value in column, seems it is NOT NULL'.

Again,

  • I have table modified to NULL for columns
  • Wrote seed script against it
  • asked for 'Publish'
  • script fails since table schema still requires NOT NULL in most columns

I did:

  • removed bin/obj,
  • removed .refactorlog and .dbml so ended up with broken build since it requires .refactorlog.

Then I DROPed table. Still no changes detected! Since table doesnt exist I ran CREATE manually and seems any later (subsequently, afterwards) changes been detected.

So question is - what am I doing wrong? I thought SSDT will analyze static code against target schema. But seems not. I dont wanna run into such issue out of sudden I need bullet-proof schema migration.

Environment:

  • VS 2017 15.6.6
  • Sql Server EXPRESS 2014 (12.0.2000)

Found only this but it seems not my case (Hovewer, generated script sets SET ANSI_NULLS ON).

Much appreciate explanation of this issue!

DevOvercome
  • 151
  • 1
  • 12
  • What do you see if you do a schema compare of your project against the DB? – Peter Schott May 01 '18 at 15:42
  • Hi! Please note that I solved this particular issue and asking how to prevent next ones. I took a look on compare (is there such tool? wow), and found other table DELETED which I have RENAMED into other. So its a big issue too - I expected that table would be deleted (why I expected that?), but it still exists and confuses the team. So my answer - I cant provide any info on issue. Just counter-asking for other issue - should I make Update from comparison manually in order to sync project and SQL DB? – DevOvercome May 01 '18 at 18:43
  • So you renamed it? Did you do it through the "Refactor-Rename" command or just change the name? Using the Refactor option should result in a rename. There could also be the option to not drop objects in the target if they don't exist in the source. I've tended to set that for Production systems to avoid unpleasant surprises. – Peter Schott May 02 '18 at 19:35
  • Yes this renaming issue could be solved by setting drop. So your expirience recommends to drop... what? everything? Or something specific? – DevOvercome May 03 '18 at 07:21
  • 1
    The option is "Drop objects not in source" if I remember correctly. I tend to be wary of doing that when releasing to Prod without checking the report of what will be dropped first, though. Can also do in a pre-deploy or post-deploy script if needed. The best way to handle renames is through REFACTOR. The only ways to handle DROPs are either to allow dropping objects not in source or to do in a pre/post deploy script. – Peter Schott May 04 '18 at 22:37

0 Answers0