Default Constraints Added After Column Alter Attempted - Script Fails

Xander

Hi,

I'm trying out the 14 day trial of SQL Compare and am facing a big issue. Any table that has NULL columns on my target DB but has been changed to a NOT NULL with a default constraint on the source DB fails when I try to deploy because the order the script is generated in means it tries to alter the column before adding the default constraint.

I even see it attempts to update the column to it's default value before setting it to not null.

So I see these three statements in this order in the script:

ALTER TABLE [dbo].[Table] ALTER COLUMN [Column] [varchar] (1) NOT NULL
</code>UPDATE [dbo].[Table] SET [Column]=DEFAULT WHERE [Column] IS NULL
</pre><div><pre class="CodeBlock"><code>

ALTER TABLE [dbo].[Table] ADD CONSTRAINT [DF_Table_Column] DEFAULT ('F') FOR [Column]

Seems like the last statement here should have been run before the other 2, but I can't see any options to change the order the script is built in.

Is this possible?

Thanks,
Xander

Tianjiao_Li

Hi @Xander

It seems I can't reproduce the problem. Can you upgrade to the latest version and test again?

Xander

Hi Tianjiao,

I was on version 13.4.5.6953 but just upgraded to the one you linked (13.5.0.7565) and I'm encountering the same problem.

In your screenshot you've shown the deployment script is adding the default constraint, which mine is doing too, but it's trying to add it too late. The order the script is generated means it tries to replace nulls with the default (which does nothing, because there is no default), then tries to change the column to not null, then adds the default. Because the nulls weren't replaced, the script fails when it tries to change the column to not null.

Tianjiao_Li

Hi @Xander

Would you be able to share the create script of the table from your source and target database to help me with a reproduction?

Xander

I've just sent you a PM with the scripts.

I think you'll need to add some data to the target table after creating it, leaving at least one of the columns NULL that can be NULL on the target but not on the source. [Use ERV Say] is a good example column.

Thanks.

Tianjiao_Li

Hi @Xander

So I see these three statements in this order in the script:
UPDATE [dbo].[Table] SET [Column]=DEFAULT WHERE [Column] IS NULL
ALTER TABLE [dbo].[Table] ALTER COLUMN [Column] [varchar] (1) NOT NULL
ALTER TABLE [dbo].[Table] ADD CONSTRAINT [DF_Table_Column] DEFAULT ('F') FOR [Column]

Thanks for the scripts. I can see the same behaviour in the deployment script. However, the deployment went through successfully. 

Can you confirm if you were able to deploy?

Xander

This is what happens when I try to deploy.

It would work perfectly if the ADD CONSTRAINT statement was executed before the other two, but as is the script tries to replace NULLs with a default that doesn't exist, so it will never succeed.

Tianjiao_Li

Hi @Xander

Would you be able to send in the snapshot of your source and target database?

Xander

Hi Tianjiao,

That really isn't necessary... I've just replicated the bug myself on 2 newly created databases. I've just created a TestSource and a TestTarget DB, applied the create table statements I gave you, added a row of data into the TestTarget DB where I left [Use ERV Say] as NULL, and I get the exact same failure.



Because the script doesn't try to add the default until after setting the column to NOT NULL, the column is still NULL for that row and the update fails. All the script needs to do is put this one before the other two:

ALTER TABLE [dbo].[Table] ADD CONSTRAINT [DF_Table_Column] DEFAULT ('F') FOR [Column]

But as is this tool is unfortunately useless for databases with any data in the tables...

Tianjiao_Li

Hi @Xander

That what I've tried, but the deployment went successfully.

I've attached my test databases, kindly give it a try. Did you use the default compare options?

Xander

Hi Tianjiao,

I've just restored your DBs and looked at the row you added to the Tenancy table on the target, and you didn't actually leave any columns as NULL, but rather empty string. So the deployment succeeds for me like that, but if I change the [Use ERV Say] column to NULL, I get the same failure:



All SQL Compare needs to do is change the order it generates the statements for the deployment script, and it would work fine.

Tianjiao_Li

@Xander

I've reproduced the issue and logged in our internal bug tracking system whose reference number is SC-10262!

Thanks for helping us with the reproduction and your patience is much appreciated!

Xander

Tianjiao_Li said:

@Xander

I've reproduced the issue and logged in our internal bug tracking system whose reference number is SC-10262!

Thanks for helping us with the reproduction and your patience is much appreciated!

Hi Tianjiao,

It's great to hear you've been able to replicate it. I'm surprised this hasn't come up before as it doesn't seem like an exceptionally uncommon case for SQL databases to have a column that was once NULL-able to become not NULL-able.

Is there any way for me to track the bug? This was one of the big things that stopped my company from purchasing SQL Compare so would be great to see when it gets fixed.

Thanks.

Tianjiao_Li

@Xander

It does feel like a common case, but no one has reported this issue before surprisingly.

We use an internal bug tracking system which unfortunately does give accessibility to the public. So far this bug has been triaged and it's under investigation by the development team.

I'll keep you posted with any meaningful update!

Xander

@Tianjiao_Li

Any news with this bug?

Tianjiao_Li

@Xander

I'm pleased to let you know we've released 13.6.4.8233 to frequent updates, which fixes SC-10262. Please upgrade.

Xander

Hi @Tianjiao_Li,

Brilliant! Thanks so much for your help and ensuring the bug was fixed

We have since bought SQL Compare and are using it a bit more. I have a quick question I hope you could resolve. The columns listed in the grid when comparing 2 DBs are great, but I was wondering if these were customisable at all. Specifically I was hoping to have a column showing the difference in days between the last modified dates on each database. We sometimes have a case where the 2 DBs being compared have both had updates the other hasn't had, so a column where I could see the day difference, especially showing a negative or positive number, would be very helpful.

Thanks again.

Tianjiao_Li

Hi @Xander

I'm afraid it's not possible. It will be very helpful if you can  post your suggestion on our user voice forum?