Computed column issue Sql Compare 6.2.0.271

Michael Newcomb

I've identified what I think is a bug in v6.2.0.271. I have a table with a computed column. If I change the column's formula SQL Compare does not detect the change. In fact, the screen shows "these tables are identical" at the top but correctly indicates the difference in the detail pane.

I think this may be because I have "force column order" turned off(?).

Here's the source (new) table's DDL:

CREATE TABLE [dbo].[CostCenterUpdate] (
	[CCU_Code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CCU_Name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_FunctionCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Function] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Site] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_SiteDescription] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BGCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BGName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BUCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BUName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BDCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BDName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_GLCompany] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_OCMCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_OCMName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_ReplacementCCCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_ManagerCorpID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_ManagerName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_DivisionCode] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Division] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_SubdivisionCode] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Subdivision] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_HRDivisionCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Checksum] AS (checksum([CCU_Name],[CCU_FunctionCode],[CCU_Function],
		[CCU_Site],[CCU_SiteDescription],[CCU_BGCode],
		[CCU_BGName],[CCU_BUCode],[CCU_BUName],[CCU_BDCode],
		[CCU_BDName],[CCU_Status],[CCU_GLCompany],
		[CCU_OCMCode],[CCU_OCMName],[CCU_ReplacementCCCode],
		[CCU_ManagerCorpID],[CCU_ManagerName],[CCU_DivisionCode],
		[CCU_Division],[CCU_SubdivisionCode],[CCU_Subdivision],
		[CCU_HRDivisionCode])) 
)

Here's the destination (old) table:

CREATE TABLE [dbo].[CostCenterUpdate] (
	[CCU_Code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CCU_Name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_FunctionCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Function] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Site] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_SiteDescription] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BGCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BGName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BUCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BUName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BDCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_BDName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_GLCompany] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_OCMCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_OCMName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_ReplacementCCCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_ManagerCorpID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_ManagerName] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Checksum] AS (checksum([CCU_Name],[CCU_FunctionCode],[CCU_Function],
		[CCU_Site],[CCU_SiteDescription],[CCU_BGCode],[CCU_BGName],
		[CCU_BUCode],[CCU_BUName],[CCU_BDCode],[CCU_BDName],
		[CCU_Status],[CCU_GLCompany],[CCU_OCMCode],
		[CCU_OCMName],[CCU_ReplacementCCCode],[CCU_ManagerCorpID],
		[CCU_ManagerName])) ,
	[CCU_DivisionCode] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Division] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_SubdivisionCode] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_Subdivision] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCU_HRDivisionCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

As you can see there are more columns in the source table's computed column.

Thanks for any assistance you can provide,

Mike N.

kmoskal

I'm also having a similar issue with computed columns. I have two databases that SQL Compare reports to be in sync.

On my test database a table has 4 "real" columns and 10 computed columns. My production database has only the 4 “real” columns. The 10 computed columns got dropped by mistake during an upgrade.

We used SQL Compare to validate that the database were in sync after the upgrade and it report all tables in sync.

This is a major issue for me. I've since lost confidence in the SQL Compare tool and we’re removing it from all the DBA’s desktops until we can confirm issue like this are fixed.

Brian Donahue

Hi Michael,

Thanks for pointing this out. At first look, it seems that the "mini-parser" that examines computed columns doesn't recognize the difference in parameters passed to the CHECKSUM T-SQL function. The text parser that is part of the UI of SQL Compare shows differences because it will detect any differences in text and does not tokenize the strings in the same way. I'll get in touch with you when I know more about the cause of this.

Kurt -- I've opened a support incident for you so we can get the specific details of the problem(s) you've had.

Eddie D

I am pleased to inform you that we have fixed this problem in the recently released SQL Compare V.7.

If you have a valid Support & upgrades option, you can download SQL Compare V.7 using the 'Check for updates' mechanism (SQL Compare GUI ->Help ->Check for updates)

or download using this link: ftp://ftp.red-gate.com/SQLToolbelt.zip

SQL Compare V.7 will install along side any previous versions of the software.

Many Thanks
Eddie