Comments
3 comments
-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntityContent_ContentID_Content_ContentID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EntityContent] DROP CONSTRAINT FK_EntityContent_ContentID_Content_ContentID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntityContent_VersionID_Version_VersionID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EntityContent] DROP CONSTRAINT FK_EntityContent_VersionID_Version_VersionID
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaInsContent]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaInsContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaUpdContent]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaUpdContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaDelContent]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaDelContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaInsVersion]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaInsVersion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaUpdVersion]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaUpdVersion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaDelVersion]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaDelVersion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaInsEntityContent]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaInsEntityContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaUpdEntityContent]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaUpdEntityContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trgDbaDelEntityContent]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trgDbaDelEntityContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EntityContent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EntityContent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Content]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Content]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Version]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Version]
GO
if exists (select * from dbo.systypes where name = N'XMLDATA')
exec sp_droptype N'XMLDATA'
GO
setuser
GO
EXEC sp_addtype N'XMLDATA', N'ntext', N'not null'
GO
setuser
GO
CREATE TABLE [dbo].[Content] (
[ContentID] [int] NOT NULL ,
[ContentXML] [XMLDATA] NOT NULL ,
[RowID] [uniqueidentifier] NOT NULL ,
[dbaDateCreated] [datetime] NULL ,
[dbaDateUpdated] [datetime] NULL ,
[dbaIsActive] [tinyint] NULL ,
[dbaIsDeleted] [tinyint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Version] (
[VersionID] [int] NOT NULL ,
[VersionMajor] [int] NOT NULL ,
[VersionMinor] [int] NOT NULL ,
[VersionText] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateInstalled] [datetime] NOT NULL ,
[EffectiveDate] [datetime] NOT NULL ,
[RowID] [uniqueidentifier] NOT NULL ,
[dbaDateCreated] [datetime] NULL ,
[dbaDateUpdated] [datetime] NULL ,
[dbaIsActive] [tinyint] NULL ,
[dbaIsDeleted] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[EntityContent] (
[EntityContentID] [int] NOT NULL ,
[EntityGUID] [uniqueidentifier] NOT NULL ,
[VersionID] [int] NOT NULL ,
[ContentID] [int] NOT NULL ,
[RowID] [uniqueidentifier] NOT NULL ,
[dbaDateCreated] [datetime] NULL ,
[dbaDateUpdated] [datetime] NULL ,
[dbaIsActive] [tinyint] NULL ,
[dbaIsDeleted] [tinyint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Content] ADD
CONSTRAINT [DF_Content_RowID] DEFAULT (newid()) FOR [RowID],
CONSTRAINT [DF_Content_dbaDateCreated] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateCreated],
CONSTRAINT [DF_Content_dbaDateUpdated] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateUpdated],
CONSTRAINT [DF_Content_dbaIsActive] DEFAULT (1) FOR [dbaIsActive],
CONSTRAINT [DF_Content_dbaIsDeleted] DEFAULT (0) FOR [dbaIsDeleted],
CONSTRAINT [PK_Content] PRIMARY KEY NONCLUSTERED
(
[ContentID]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Content_RowID] ON [dbo].[Content]([RowID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Version] ADD
CONSTRAINT [DF_Version_RowID] DEFAULT (newid()) FOR [RowID],
CONSTRAINT [DF_Version_dbaDateCreated] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateCreated],
CONSTRAINT [DF_Version_dbaDateUpdated] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateUpdated],
CONSTRAINT [DF_Version_dbaIsActive] DEFAULT (1) FOR [dbaIsActive],
CONSTRAINT [DF_Version_dbaIsDeleted] DEFAULT (0) FOR [dbaIsDeleted],
CONSTRAINT [PK_Version] PRIMARY KEY NONCLUSTERED
(
[VersionID]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [AK_Version_Text] ON [dbo].[Version]([VersionText]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [AK_Version_Version] ON [dbo].[Version]([VersionMajor], [VersionMinor]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Version_RowID] ON [dbo].[Version]([RowID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EntityContent] ADD
CONSTRAINT [DF_EntityContent_RowID] DEFAULT (newid()) FOR [RowID],
CONSTRAINT [DF_EntityContent_dbaDateCreated] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateCreated],
CONSTRAINT [DF_EntityContent_dbaDateUpdated] DEFAULT (convert(nchar(19),getutcdate(),121)) FOR [dbaDateUpdated],
CONSTRAINT [DF_EntityContent_dbaIsActive] DEFAULT (1) FOR [dbaIsActive],
CONSTRAINT [DF_EntityContent_dbaIsDeleted] DEFAULT (0) FOR [dbaIsDeleted],
CONSTRAINT [PK_EntityContent] PRIMARY KEY NONCLUSTERED
(
[EntityContentID]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [AK_EntityContent_Version] ON [dbo].[EntityContent]([EntityGUID], [VersionID]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_EntityContent_RowID] ON [dbo].[EntityContent]([RowID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EntityContent] ADD
CONSTRAINT [FK_EntityContent_ContentID_Content_ContentID] FOREIGN KEY
(
[ContentID]
) REFERENCES [dbo].[Content] (
[ContentID]
),
CONSTRAINT [FK_EntityContent_VersionID_Version_VersionID] FOREIGN KEY
(
[VersionID]
) REFERENCES [dbo].[Version] (
[VersionID]
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaInsContent ON Content
FOR INSERT
AS
Declare
@ContentID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@ContentID = ContentID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From inserted
If @dbaDateCreated = Null OR Not(Update(dbaDateCreated))
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null
Set @dbaIsActive = 1
If @dbaIsDeleted = Null
Set @dbaIsDeleted = 0
Update Content
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where ContentID = @ContentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaUpdContent ON Content
FOR UPDATE
AS
Declare
@ContentID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@ContentID = ContentID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From inserted
If @dbaDateCreated = Null
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null
Set @dbaIsActive = 1
If @dbaIsDeleted = Null
Set @dbaIsDeleted = 0
Update Content
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where ContentID = @ContentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaDelContent ON Content
INSTEAD OF DELETE
AS
Declare
@ContentID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@ContentID = ContentID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From deleted
If @dbaDateCreated = Null
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null OR @dbaIsActive = 1 OR Not(Update(dbaIsActive))
Set @dbaIsActive = 0
If @dbaIsDeleted = Null OR @dbaIsDeleted = 0 OR Not(Update(dbaIsDeleted))
Set @dbaIsDeleted = 1
Update Content
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where ContentID = @ContentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaInsVersion ON Version
FOR INSERT
AS
Declare
@VersionID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@VersionID = VersionID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From inserted
If @dbaDateCreated = Null OR Not(Update(dbaDateCreated))
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null
Set @dbaIsActive = 1
If @dbaIsDeleted = Null
Set @dbaIsDeleted = 0
Update Version
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where VersionID = @VersionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaUpdVersion ON Version
FOR UPDATE
AS
Declare
@VersionID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@VersionID = VersionID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From inserted
If @dbaDateCreated = Null
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null
Set @dbaIsActive = 1
If @dbaIsDeleted = Null
Set @dbaIsDeleted = 0
Update Version
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where VersionID = @VersionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaDelVersion ON Version
INSTEAD OF DELETE
AS
Declare
@VersionID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@VersionID = VersionID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From deleted
If @dbaDateCreated = Null
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null OR @dbaIsActive = 1 OR Not(Update(dbaIsActive))
Set @dbaIsActive = 0
If @dbaIsDeleted = Null OR @dbaIsDeleted = 0 OR Not(Update(dbaIsDeleted))
Set @dbaIsDeleted = 1
Update Version
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where VersionID = @VersionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaInsEntityContent ON EntityContent
FOR INSERT
AS
Declare
@EntityContentID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@EntityContentID = EntityContentID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From inserted
If @dbaDateCreated = Null OR Not(Update(dbaDateCreated))
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null
Set @dbaIsActive = 1
If @dbaIsDeleted = Null
Set @dbaIsDeleted = 0
Update EntityContent
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where EntityContentID = @EntityContentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaUpdEntityContent ON EntityContent
FOR UPDATE
AS
Declare
@EntityContentID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@EntityContentID = EntityContentID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From inserted
If @dbaDateCreated = Null
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null
Set @dbaIsActive = 1
If @dbaIsDeleted = Null
Set @dbaIsDeleted = 0
Update EntityContent
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where EntityContentID = @EntityContentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER trgDbaDelEntityContent ON EntityContent
INSTEAD OF DELETE
AS
Declare
@EntityContentID int,
@dbaDateCreated datetime,
@dbaDateUpdated datetime,
@dbaIsActive tinyint,
@dbaIsDeleted tinyint
Select
@EntityContentID = EntityContentID,
@dbaDateCreated = dbaDateCreated,
@dbaDateUpdated = dbaDateUpdated,
@dbaIsActive = dbaIsActive,
@dbaIsDeleted = dbaIsDeleted
From deleted
If @dbaDateCreated = Null
Set @dbaDateCreated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateCreated = (convert(nchar(19),@dbaDateCreated,121))
If @dbaDateUpdated = Null OR Not(Update(dbaDateUpdated))
Set @dbaDateUpdated = (convert(nchar(19),getutcdate(),121))
Else
Set @dbaDateUpdated = (convert(nchar(19),@dbaDateUpdated,121))
If @dbaIsActive = Null OR @dbaIsActive = 1 OR Not(Update(dbaIsActive))
Set @dbaIsActive = 0
If @dbaIsDeleted = Null OR @dbaIsDeleted = 0 OR Not(Update(dbaIsDeleted))
Set @dbaIsDeleted = 1
Update EntityContent
Set
dbaDateCreated = @dbaDateCreated,
dbaDateUpdated = @dbaDateUpdated,
dbaIsActive = @dbaIsActive,
dbaIsDeleted = @dbaIsDeleted
Where EntityContentID = @EntityContentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO -
Hi,
We'd like to reproduce your problem here. Could you please supply us with insert statements for your tables to cause Data Compare to show incorrect differences? Also, are you using SQL Server 2000 or 2005?
Thanks,
David Atkinson
Red Gate Software -
We are using SQL 2000 SP4 for production. However, we are seeing the same situation with the same databases on SQL 2005.
I can supply two data set types: a) bulk loadable text files for each table b)synchronize script.
I do not have a public internet server to link to, is there an attach or upload facility you provide?
Add comment
Please sign in to leave a comment.
Assuming that the data is equal, even after the red-gate generated synchronization script has been executed then there is a bug that requires improvement for these types of columns.