| Author |
 Topic  |
|
|
Mikas
7 Posts |
 Posted - 08/13/2009 : 11:32:05
|
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]
|
|
|
Mikas
7 Posts |
Posted - 08/13/2009 : 12:05:03
|
| I should of added, I am using a trial version of Packager Plus for this test. |
 |
|
|
leachm
124 Posts |
Posted - 08/13/2009 : 12:27:16
|
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 |
|
|
|
Mikas
7 Posts |
Posted - 08/13/2009 : 15:18:43
|
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
|
|
|
|
leachm
124 Posts |
Posted - 08/14/2009 : 02:27:33
|
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 |
|
|
|
Mikas
7 Posts |
Posted - 08/14/2009 : 11:22:08
|
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
|
|
|
|
leachm
124 Posts |
Posted - 08/14/2009 : 11:26:47
|
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... |
|
|
|
Mikas
7 Posts |
Posted - 08/20/2009 : 19:47:51
|
| Got your email and have posted the files. |
|
|
|
leachm
124 Posts |
Posted - 08/25/2009 : 08:51:23
|
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
|
|
|
| |
Topic |
|
|
|
Constraint Issues
