Database Change Management
Database Change Management
Home | Profile | Register | Active Topics | Active Polls | Members | Private Messages | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?




 All Forums
 Support
 DB Ghost Support Forum
 Computed columns
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GeoffPate


13 Posts

Posted - 09/01/2010 :  03:52:49  Show Profile Send GeoffPate a Private Message  Reply with Quote
Hi

I'm seeing DBGhost compare always mark computed columns as different in the schema compare, whether they've changed or not. Is this to be expected? Are computed columns beyond the scope of the compare engine? I'm using v5.0.0.1148 if that makes any difference.

Cheers
Geoff

Google AdSense

USA
Mountain View


jon.woolgar



105 Posts

Posted - 09/01/2010 :  05:22:23  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
Jeff,

Computed columns are supposed to work. There has not been a specific fix for what you describe since 1148. It works with the latest version 1233.

I'll try testing with 1148 to reproduce. Meanwhile could you post your computed column spec incase there is something about it that fools DBG.

By the way 1148 must be nearly a year old now...

Regards Jon W
(Innovartis)
Go to Top of Page

jon.woolgar



105 Posts

Posted - 09/01/2010 :  05:27:16  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
I've tested with 1148 and can't produce the behaviour you describe.

Which SQL are you on?


Regards Jon W
(Innovartis)
Go to Top of Page

GeoffPate



13 Posts

Posted - 09/01/2010 :  05:31:05  Show Profile Send GeoffPate a Private Message  Reply with Quote
This is the script:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Ranking_Monthly_Staging]') AND type in (N'U'))
DROP TABLE [dbo].[Ranking_Monthly_Staging]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[Ranking_Monthly_Staging](
[RankingTableId] [int] NOT NULL,
[UserId] [int] NOT NULL,
[Month] [int] NOT NULL,
[Year] [int] NOT NULL,
[RankingPoints] [decimal] (26,7) NOT NULL,
[UserParameterGroupId] [int] NOT NULL CONSTRAINT [df_Ranking_Monthly_Staging_UserParameterGroupId] DEFAULT (0),
[UserCountryId] [int] NOT NULL CONSTRAINT [DF__Ranking_M__UserC__010A0A00] DEFAULT (0),
[Rank] AS ([dbo].[RankingCalculateMonthly]([UserId], [RankingTableId], [Month], [Year], [RankingPoints], [UserParameterGroupId], [UserCountryId]))
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Ranking_Monthly_Staging] ADD CONSTRAINT [pk_ranking_monthly_staging] PRIMARY KEY
CLUSTERED
(
[RankingTableId] ASC
,[UserId] ASC
,[Year] ASC
,[Month] ASC
,[UserParameterGroupId] ASC
,[UserCountryId] ASC
) WITH
(
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO

With 'Rank' being the computed column. We're running SQL Server 2008 btw
Go to Top of Page

jon.woolgar



105 Posts

Posted - 09/01/2010 :  08:29:59  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
So you have a source and target database with exactly the same table and no changes but DBG thinks they are different.

I can't reproduce this. I have tried with 1148 and 1233 on sql2008 with various compare options. There must be something quite subtle going on.

If you click the + in the results tree, does the diff screen show anything useful?

If you let DBG sync the difference does the difference go away (guess not from what you said)?

Regards Jon W
(Innovartis)
Go to Top of Page

GeoffPate



13 Posts

Posted - 09/01/2010 :  09:06:11  Show Profile Send GeoffPate a Private Message  Reply with Quote
Hmm, I've let DBGhost sync the difference, then run the compare again, and the difference has gone away! I'm sure I already tried that, but perhaps I made a mistake. Sorry for the confusion
Go to Top of Page

kmh-ghost



3 Posts

Posted - 02/04/2011 :  13:30:52  Show Profile Send kmh-ghost a Private Message  Reply with Quote
I have two issues with persisted computed columns with Change Manager 5.0.0.1143 using SQL Server 2008 (v10.0.4000).

ISSUE #1:
Create two databases: TEST1 and TEST2.

Run this script to create a table in each db:
CREATE TABLE [dbo].[Member](
[MemberId] [bigint] NOT NULL,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SoundexLastName] AS (soundex([LastName])) PERSISTED,
[SoundexFirstName] AS (soundex([FirstName])) PERSISTED
) ON [PRIMARY]

Now run Change Manager with COMPARE and CREATE CHANGE SCRIPT. I get this ugly error from SQL Server about UNIQUE/PK constraints on computed columns. But I get this outside of DBGhost also, so that's OK.

ISSUE #2:
drop that table from the TEST1 db and re-create it using PERSISTED NOT NULL columns, like so:
CREATE TABLE [dbo].[Member](
[MemberId] [bigint] NOT NULL,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SoundexLastName] AS (soundex([LastName])) PERSISTED NOT NULL,
[SoundexFirstName] AS (soundex([FirstName])) PERSISTED NOT NULL
) ON [PRIMARY]

Now do the COMPARE/CREATE CHANGE SCRIPT and DBGhost thinks the table doesn't exist in the TEST2 db, but of course it does.

This seems like a DBGhost problem.



thanks
Go to Top of Page

jon.woolgar



105 Posts

Posted - 02/06/2011 :  14:33:03  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
hi,

When you say DBG "thinks the table doesn't exist", what is it doing tha makes you think it thinks that the table does not exist?

PS 1143 is fairly old now. I'd advise keeping up to date.

Regards Jon W
(Innovartis)
Go to Top of Page

kmh-ghost



3 Posts

Posted - 02/07/2011 :  08:15:26  Show Profile Send kmh-ghost a Private Message  Reply with Quote
well, the change script that is generated contain only a "create table", which is going to fail.

It should generate a "create new temp table, load old data into new temp table, drop old table, rename new temp table as old table".

I will look into upgrading, but for now, I can't use DBGhost for this table.




thx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Jump To:
Database Change Management © Copyright 2005 Innovartis Ltd. Go To Top Of Page
Snitz Forums 2000
RSS Feed 1 RSS Feed 2
Powered by ForumCo 2000-2008
TOS - AUP - URA - Privacy Policy
ForumCo Free Blogs and Galleries
Signup for a free forum or Go Banner Free