SQL Compare Showing Differences in Default Constraints

kewlgeek

Hello,

SQL Compare 6 is showing differences between tables when the only thing different is the default constraint, which on one server is defined as (0) and on the other is ((0)). Even if I tell it to synchronize the 2 databases they still remain different. Is there any way to set an option to ignore extra parentheses in a default constraint? I looked and couldn't find one.

Thanks,
Karin

Michelle T

SQL Compare shouldn't be treating the two default constraints as different. They will show up on the SQL Differences pane as different, because they are textually different (the Differences pane only does a textual comparison for performance reasons), but the table shouldn't show up different in the main grid for this reason.

It looks like SQL Compare has found some other difference in your table which it is having trouble synchronizing.

Do you have encrypted triggers on one or both tables? If a trigger is encrypted (in 2005 where we can't break the encryption) SQL Compare can't tell if it's equal or not, so it assumes that it is different - and as it can't read the trigger on either side, it can't synchronize them, so the tables will remain different.

If encrypted triggers aren't the problem, could you post (or send me - michelle.taylor@red-gate.com) the table SQL on each side and the synchronization SQL that you get from pressing the 'View object synchronization script' button on the SQL Differences pane? Hopefully I'll be able to work out from these what the difference that SQL Compare is finding is, and why it isn't synchronizing properly.

kewlgeek

Hi Michelle,

No, we are not using encrypted triggers. Here are the scripts:

CREATE TABLE [dbo].[AssignRolesToElementActions]
(
[AssignRolesTestGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AssignRolesTest_AssignRolesTestGuid] DEFAULT (newid()),
[ElementGuid] [uniqueidentifier] NOT NULL,
[SecurityRoleGuid] [uniqueidentifier] NOT NULL,
[ElementAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_AssignRolesTest_CreatedDate] DEFAULT (getdate()),
[LastUpdatedDate] [datetime] NOT NULL CONSTRAINT [DF_AssignRolesTest_LastUpdatedDate] DEFAULT (getdate()),
[UpdatedBy] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SysTransID] [smallint] NOT NULL CONSTRAINT [DF_AssignRolesTest_SysTransID] DEFAULT (0)
)

GO

and here is the second table:

CREATE TABLE [dbo].[AssignRolesToElementActions]
(
[AssignRolesTestGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_AssignRolesTest_AssignRolesTestGuid] DEFAULT (newid()),
[ElementGuid] [uniqueidentifier] NOT NULL,
[SecurityRoleGuid] [uniqueidentifier] NOT NULL,
[ElementAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_AssignRolesTest_CreatedDate] DEFAULT (getdate()),
[LastUpdatedDate] [datetime] NOT NULL CONSTRAINT [DF_AssignRolesTest_LastUpdatedDate] DEFAULT (getdate()),
[UpdatedBy] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SysTransID] [smallint] NOT NULL CONSTRAINT [DF_AssignRolesTest_SysTransID] DEFAULT ((0))
)

GO


Thanks,
Karin

Michelle T

I tried putting these scripts in two script folders to see if I could generate the difference, but Compare correctly reported them as equal to each other. I ran the create statements into databases and managed to get a 2005 database with ((0)) and a 2000 database with (0), and the main grid still reports them as equal.

I strongly suspect there is some difference between the tables in the live database that we are not displaying clearly enough - something that isn't visible at the level of a creation script which is causing Compare to decide that the tables are different.

If you could send database backups (or SQL Compare schema snapshots, if you can't send out actual backups) of the affected databases to michelle.taylor@red-gate.com I can take a closer look at the actual objects and perhaps figure out what's different about those tables.

(If you aren't even allowed to send me schema snapshots, take a look at / send me the SQL script shown in the SQL Differences viewer when you press the SQL button - if it has any statements between the 'BEGIN TRANSACTION' and the line with 'ROLLBACK TRANSACTION', it might let me track down what's going on here.)