Toad World® Forums

6.7.0.37: Schema comparison doesn't handle CONVERT() in computed columns correctly

For example, execute this to create identical tables in two databases and then compare them:

CREATE TABLE [dbo].[foo] (
[ID] int IDENTITY(1, 1),
[isvoid] varchar(255) CONSTRAINT [DF_foo_isvoid] DEFAULT (‘N’),
[bIsVoid] AS (CONVERT([bit],case when [isvoid]=‘Y’ then (1) else (0) end)) PERSISTED NOT NULL,
PRIMARY KEY([ID])
)

You’ll find that Toad shows a difference, with the target having a “,0” as an added last parameter for the CONVERT().

Possible Correction:

Also try creating the table in one database and using schema compare to generate a script to put it into the target and execute the script. Then compare the databases and it will find the difference I reported.

Hi Mark,

Thank you very much for your posting. I have reproduced this problem. We will investigate it and reply this as soon as possible.

Kelly

Hi Mark,

We located this issue. And trying to fix it now(TSS-1225).

Thanks,

Kelly

Hi Mark,

After we investigate, this CONVERT() statment is formated by SQL Server, Toad doesn’t modify the script.

  1. When we execute the create table statement without ‘0’

[bIsVoid] AS (CONVERT([bit],(1))

SQL Server will add “0”, So it become to

[bIsVoid] AS (CONVERT([bit],(1),0))

  1. After the schema compare, Toad will generate the script like source:

[bIsVoid] AS (CONVERT([bit],(1),0))

But after run the script, SQL Server add a pair of bracket:

Target will become:

[bIsVoid] AS (CONVERT([bit],(1),(0)))

We doesn’t have good idea to improve this. We close this issue now. If you have any suggestion, please kindly let us know.

Thanks,

Kelly

A start would be to generate the third version (with the extraneous parenthesis) in the script to better match what SQL Server will actually store in the database. However, Toad needs to understand that all three versions of the statement are functionally identical and not treat them as differences.

Thanks,

Mark Freeman
Database Administrator | R****ogue Fitness
1080 Steelwood Road, Columbus, OH 43212
MFreeman@RogueFitness.com

On Fri, Dec 18, 2015 at 12:08 AM, Kelly.Chen bounce-KellyChen@toadworld.com wrote:

RE: 6.7.0.37: Schema comparison doesn’t handle CONVERT() in computed columns correctly

Reply by Kelly.Chen
Hi Mark,

After we investigate, this CONVERT() statment is formated by SQL Server, Toad doesn’t modify the script.

  1. When we execute the create table statement without ‘0’

[bIsVoid] AS (CONVERT([bit],(1))

SQL Server will add “0”, So it become to

[bIsVoid] AS (CONVERT([bit],(1),0))

  1. After the schema compare, Toad will generate the script like source:

[bIsVoid] AS (CONVERT([bit],(1),0))

But after run the script, SQL Server add a pair of bracket:

Target will become:

[bIsVoid] AS (CONVERT([bit],(1),(0)))

We doesn’t have good idea to improve this. We close this issue now. If you have any suggestion, please kindly let us know.

Thanks,

Kelly

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for SQL Server - Beta notifications altogether.

Toad for SQL Server - Beta Discussion Forum

Flag this post as spam/abuse.