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
 Constraint Issues
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mikas


8 Posts

Posted - 08/13/2009 :  11:32:05  Show Profile Send Mikas a Private Message  Reply with Quote
Sorry for the long Post but I wanted to include as much as possible.

Below is the Master Source Table, Errors from the Upgrade and SQL Profiler statments.

The Profiler statments appear to be out of whack.

Other tables updated fine but this one and possible 2 others did not.

I'd like to know what's going on.

Mike


--DB GHOST MASTER

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

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

CREATE TABLE [dbo].[ECFCN](
[ECN] [nvarchar] (6) NOT NULL CONSTRAINT [DF__ECFCN__ECN__29ACF837] DEFAULT (''),
[ECN_STATUS] [nvarchar] (2) NOT NULL CONSTRAINT [DF__ECFCN__ECN_STATU__2AA11C70] DEFAULT (''),
[CHANGE_TYPE] [nvarchar] (2) NOT NULL CONSTRAINT [DF__ECFCN__CHANGE_TY__2B9540A9] DEFAULT (''),
[ORIGINATOR] [nvarchar] (8) NOT NULL CONSTRAINT [DF__ECFCN__ORIGINATO__2C8964E2] DEFAULT (''),
[CHANGE_ANALYST] [nvarchar] (8) NOT NULL CONSTRAINT [DF__ECFCN__CHANGE_AN__2D7D891B] DEFAULT (''),
[DATE_ORIGINATED] [datetime] NULL,
[DATE_RELEASED] [datetime] NULL,
[ECN_EFF_DATE] [datetime] NULL,
[PRODUCT_LINE] [nvarchar] (8) NOT NULL CONSTRAINT [DF__ECFCN__PRODUCT_L__2E71AD54] DEFAULT (''),
[ECN_REASON] [nvarchar] (4) NOT NULL CONSTRAINT [DF__ECFCN__ECN_REASO__2F65D18D] DEFAULT (''),
[COST] [decimal] (16,2) NOT NULL CONSTRAINT [DF__ECFCN__COST__3059F5C6] DEFAULT (0),
[SAVINGS] [decimal] (16,2) NOT NULL CONSTRAINT [DF__ECFCN__SAVINGS__314E19FF] DEFAULT (0),
[DESCRIPTION_TXT] [text] NOT NULL CONSTRAINT [DF__ECFCN__DESCRIPTI__32423E38] DEFAULT (''),
[REASON_TEXT] [text] NOT NULL CONSTRAINT [DF__ECFCN__REASON_TE__33366271] DEFAULT (''),
[ECFCN_USER_1] [nvarchar] (25) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__342A86AA] DEFAULT (''),
[ECFCN_USER_2] [nvarchar] (16) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__351EAAE3] DEFAULT (''),
[ECFCN_USER_3] [nvarchar] (8) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__3612CF1C] DEFAULT (''),
[ECFCN_USER_4] [nvarchar] (4) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__3706F355] DEFAULT (''),
[ECFCN_USER_5] [nvarchar] (4) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__37FB178E] DEFAULT (''),
[ECFCN_USER_6] [decimal] (16,6) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__38EF3BC7] DEFAULT (0),
[ECFCN_USER_7] [decimal] (16,6) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__39E36000] DEFAULT (0),
[ECFCN_USER_8] [decimal] (10,0) NOT NULL CONSTRAINT [DF__ECFCN__ECFCN_USE__3AD78439] DEFAULT (0),
[TIME_LAST_UPDT] [nvarchar] (5) NULL,
[DATE_LAST_UPDT] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ECFCN] ADD CONSTRAINT [ECFCN_PK] PRIMARY KEY
CLUSTERED
(
[ECN] ASC
) WITH
(
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO


-- DB GHost LOG

8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_5]
8/12/2009 4:59:26 PM...<ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_5]
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_5]
8/12/2009 4:59:26 PM...There is already an object named 'DF__ECFCN__ECFCN_USE__37FB178E' in the database.
8/12/2009 4:59:26 PM...Could not create constraint. See previous errors.
8/12/2009 4:59:26 PM...</ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_4]
8/12/2009 4:59:26 PM...<ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_4]
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_4]
8/12/2009 4:59:26 PM...There is already an object named 'DF__ECFCN__ECFCN_USE__3706F355' in the database.
8/12/2009 4:59:26 PM...Could not create constraint. See previous errors.
8/12/2009 4:59:26 PM...</ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_3]
8/12/2009 4:59:26 PM...<ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_3]
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_3]
8/12/2009 4:59:26 PM...There is already an object named 'DF__ECFCN__ECFCN_USE__3612CF1C' in the database.
8/12/2009 4:59:26 PM...Could not create constraint. See previous errors.
8/12/2009 4:59:26 PM...</ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_2]
8/12/2009 4:59:26 PM...<ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_2]
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_2]
8/12/2009 4:59:26 PM...There is already an object named 'DF__ECFCN__ECFCN_USE__351EAAE3' in the database.
8/12/2009 4:59:26 PM...Could not create constraint. See previous errors.
8/12/2009 4:59:26 PM...</ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_1]
8/12/2009 4:59:26 PM...<ERROR>
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_1]
8/12/2009 4:59:26 PM...Cannot alter column [dbo].[ECFCN].[ECFCN_USER_1]
8/12/2009 4:59:26 PM...There is already an object named 'DF__ECFCN__ECFCN_USE__342A86AA' in the database.
8/12/2009 4:59:26 PM...Could not create constraint. See previous errors.
8/12/2009 4:59:26 PM...</ERROR>


-- SQL PROFILER


-- drop USER_4 add USER_5
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[DF__ECFCN__ECFCN_USE__3706F355]'),'IsDefaultCnst')=1 ALTER TABLE [dbo].[ECFCN] DROP CONSTRAINT [DF__ECFCN__ECFCN_USE__3706F355]
ALTER TABLE [dbo].[ECFCN] ALTER COLUMN [ECFCN_USER_5] [nvarchar] (4) NOT NULL
ALTER TABLE [dbo].[ECFCN] ADD CONSTRAINT [DF__ECFCN__ECFCN_USE__37FB178E] DEFAULT ('') FOR [ECFCN_USER_5]


-- drop USER_3 add USER_4
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[DF__ECFCN__ECFCN_USE__3612CF1C]'),'IsDefaultCnst')=1 ALTER TABLE [dbo].[ECFCN] DROP CONSTRAINT [DF__ECFCN__ECFCN_USE__3612CF1C]
ALTER TABLE [dbo].[ECFCN] ALTER COLUMN [ECFCN_USER_4] [nvarchar] (4) NOT NULL
ALTER TABLE [dbo].[ECFCN] ADD CONSTRAINT [DF__ECFCN__ECFCN_USE__3706F355] DEFAULT ('') FOR [ECFCN_USER_4]


-- drop USER_2 add USER_3
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[DF__ECFCN__ECFCN_USE__351EAAE3]'),'IsDefaultCnst')=1 ALTER TABLE [dbo].[ECFCN] DROP CONSTRAINT [DF__ECFCN__ECFCN_USE__351EAAE3]
ALTER TABLE [dbo].[ECFCN] ALTER COLUMN [ECFCN_USER_3] [nvarchar] (8) NOT NULL
ALTER TABLE [dbo].[ECFCN] ADD CONSTRAINT [DF__ECFCN__ECFCN_USE__3612CF1C] DEFAULT ('') FOR [ECFCN_USER_3]


-- drop USER_1 add USER_2
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[DF__ECFCN__ECFCN_USE__342A86AA]'),'IsDefaultCnst')=1 ALTER TABLE [dbo].[ECFCN] DROP CONSTRAINT [DF__ECFCN__ECFCN_USE__342A86AA]
ALTER TABLE [dbo].[ECFCN] ALTER COLUMN [ECFCN_USER_2] [nvarchar] (16) NOT NULL
ALTER TABLE [dbo].[ECFCN] ADD CONSTRAINT [DF__ECFCN__ECFCN_USE__351EAAE3] DEFAULT ('') FOR [ECFCN_USER_2]

-- drop REASON_TEXT add USER_1
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[DF__ECFCN__ECFCN_USE__33366271]'),'IsDefaultCnst')=1 ALTER TABLE [dbo].[ECFCN] DROP CONSTRAINT [DF__ECFCN__ECFCN_USE__33366271]
ALTER TABLE [dbo].[ECFCN] ALTER COLUMN [ECFCN_USER_1] [nvarchar] (25) NOT NULL
ALTER TABLE [dbo].[ECFCN] ADD CONSTRAINT [DF__ECFCN__ECFCN_USE__342A86AA] DEFAULT ('') FOR [ECFCN_USER_1]

Google AdSense

USA
Mountain View


Mikas



8 Posts

Posted - 08/13/2009 :  12:05:03  Show Profile Send Mikas a Private Message  Reply with Quote
I should of added, I am using a trial version of Packager Plus for this test.
Go to Top of Page

leachm



125 Posts

Posted - 08/13/2009 :  12:27:16  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Hi Mikas,

Could you give me a step-by-step of what you're doing please - it's difficult to work out from just the create script, log and profiler trace.

Thanks
Go to Top of Page

Mikas



8 Posts

Posted - 08/13/2009 :  15:18:43  Show Profile Send Mikas a Private Message  Reply with Quote
Hi Leachm,

I have a Master Source Database as scripts on disk created by Change Manager.

I run Package Plus to create an .exe and then run that to update another database.

The only real change from Source to Target is from [char] to [nvarchar] on columns on the Target.

As best I can tell the table in question is identical in both databases except for the column type. When I analyze the Profiler Trace it really appears itís dropping the constraint not for the column itís about to ALTER but the previous column.

Mike
Go to Top of Page

leachm



125 Posts

Posted - 08/14/2009 :  02:27:33  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Would you be able to send your source scripts and target database to support@innovartis.co.uk so that we can reproduce the problem here?

Thanks,

Malcolm
Go to Top of Page

Mikas



8 Posts

Posted - 08/14/2009 :  11:22:08  Show Profile Send Mikas a Private Message  Reply with Quote
Hi Malcolm,

I emailed you about file transfer.

I also found something interesting.

Here are the Source and Target Constraints from the two databases when both are installed/restored in SQL Server.

You can see how they are out of sync by looking at the first 2.

Change Manager prompts me to change all these Constraints because they don't match whereas it appears that Packager Plus gets confused?

Mike

source
DF__ECFCN__REASON_TE__33366271 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__342A86AA dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__351EAAE3 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__3612CF1C dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__3706F355 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__37FB178E dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__38EF3BC7 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__39E36000 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__3AD78439 dbo ECFCN DEFAULT_CONSTRAINT

target
DF__ECFCN__REASON_TE__32423E38 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__33366271 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__342A86AA dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__351EAAE3 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__3612CF1C dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__3706F355 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__37FB178E dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__38EF3BC7 dbo ECFCN DEFAULT_CONSTRAINT
DF__ECFCN__ECFCN_USE__39E36000 dbo ECFCN DEFAULT_CONSTRAINT
Go to Top of Page

leachm



125 Posts

Posted - 08/14/2009 :  11:26:47  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Packager Plus uses the same compare and sync DLL behind the scenes as the Change Manager does so it's not a core issue - it must be a combination of settings that is producing this behaviour.

I'll get back to you when I have the details for the FTP...
Go to Top of Page

Mikas



8 Posts

Posted - 08/20/2009 :  19:47:51  Show Profile Send Mikas a Private Message  Reply with Quote
Got your email and have posted the files.
Go to Top of Page

leachm



125 Posts

Posted - 08/25/2009 :  08:51:23  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Hi Mike,

I actually reproduced the issue via a simple Change Manager run, only building and comparing tables. Iím not sure what is happening but the workaround is to give those default constraints proper names in the source table script Ė then it compares successfully and the problem will not re-appear.

Iíve raised this issue in our defect tracking system for more permanent resolution in the product.

Cheers,

Malcolm
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