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
 Errors in Packager Plus when Allow Nulls
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

takilroy


9 Posts

Posted - 05/10/2010 :  12:31:48  Show Profile Send takilroy a Private Message  Reply with Quote
Hi,

I have a table with an index (not unique) on a table. If the column with the index on it allows nulls and then run the packager plus .exe to upgrade the database which will disallow nulls it fails with the following errors:
5/10/2010 1:26:54 PM...Retrying failed change propagations
5/10/2010 1:26:55 PM...Cannot alter column [dbo].[CE_InspectionTypesCat].[CE_InspectionTypesCat]
5/10/2010 1:26:55 PM...<ERROR>
5/10/2010 1:26:55 PM...Cannot alter column [dbo].[CE_InspectionTypesCat].[CE_InspectionTypesCat]
5/10/2010 1:26:55 PM...Cannot alter column [dbo].[CE_InspectionTypesCat].[CE_InspectionTypesCat]
5/10/2010 1:26:55 PM...The index 'CE_InspectionTypesCat' is dependent on column 'CE_InspectionTypesCat'.
5/10/2010 1:26:55 PM...ALTER TABLE ALTER COLUMN CE_InspectionTypesCat failed because one or more objects access this column.
5/10/2010 1:26:55 PM...</ERROR>
5/10/2010 1:26:55 PM...Cannot alter column [dbo].[CE_InspectionTypesCat].[CE_InspectionType]
5/10/2010 1:26:55 PM...<ERROR>
5/10/2010 1:26:55 PM...Cannot alter column [dbo].[CE_InspectionTypesCat].[CE_InspectionType]
5/10/2010 1:26:55 PM...Cannot alter column [dbo].[CE_InspectionTypesCat].[CE_InspectionType]
5/10/2010 1:26:55 PM...The index 'CE_InspectionType' is dependent on column 'CE_InspectionType'.
5/10/2010 1:26:55 PM...ALTER TABLE ALTER COLUMN CE_InspectionType failed because one or more objects access this column.
5/10/2010 1:26:55 PM...</ERROR>

Any ideas?

Thanks,
Teresa

Google AdSense

USA
Mountain View


jon.woolgar



105 Posts

Posted - 05/10/2010 :  15:11:43  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
Hi Teresa,

This message comes from SQL server. You can't change the column with the index dependent on it. However, on the face of it this does look like a bug. I would expect DB Ghost to realise that the constraint or index needs to be dropped and then recreated after the change.

The error is at least making you aware that this upgrade required a potentially expensive indexing opertation.

A work around would be to drop the index in a before custom script. Then Ghost will change the column and add the index.

Which version are you using?

Regards Jon W
(Innovartis)
Go to Top of Page

takilroy



9 Posts

Posted - 05/11/2010 :  07:36:17  Show Profile Send takilroy a Private Message  Reply with Quote
Hi Jon,

I am using version 5.0.0.1156

I know I can get it to work by dropping the index first, but it does appear to be a bug, because at least in SQL Enterprise Manager I can change the column to disallow nulls and save the table without having to drop/readd the index. Is there a newer version of DBGhost than the one I have that may fix this?

Thanks,
Teresa
Go to Top of Page

jon.woolgar



105 Posts

Posted - 05/12/2010 :  04:34:36  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
Yes 1183 is the latest version and this week I expect to release 1191.

I suspect it may be the index is only dropped if it can be created later - check for missing objects must also be selected to enable the process to work.

Regards Jon W
(Innovartis)
Go to Top of Page

takilroy



9 Posts

Posted - 05/12/2010 :  11:43:16  Show Profile Send takilroy a Private Message  Reply with Quote
Hi Jon,

I'm not sure I understand you. Are you saying that this is fixed in release 1183? And that there is an option 'check for missing objects' that I need to select for it to work?

Thanks,
Teresa
Go to Top of Page

jon.woolgar



105 Posts

Posted - 05/12/2010 :  14:49:37  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
On the schema compare screen... there is a set of three check boxes in the botton right under the heading "Find objects that need to be..." make sure the "Created in Target" check box is checked. This will tell DB Ghost that it is allowed to create objects in the target so it will freely drop the index knowing it will be able to recreate it later.

That is the theory anyway.

These options are the same in your version.

I don't recall you remail if you need a screen shot or further detail mail me at jon.woolgar@innovartis.co.uk


Regards Jon W
(Innovartis)
Go to Top of Page

takilroy



9 Posts

Posted - 05/13/2010 :  08:08:12  Show Profile Send takilroy a Private Message  Reply with Quote
Hi Jon,

I don't see that option anywhere in Packager Plus with my version: 5.0.0.1156. I looked in the options button and there is a 'Schema Processing Options' area with a Schema Compare heading, but it contains the following:
Check permissions on all objects (I have checked)
Check for duplicated objects
Ignore collation differences (I have checked)
Ignore column ordinal position
Ignore extended properties (I have checked)
Request passwords for application roles (I have checked)
Do not drop tables
Do not drop columns

Maybe I am missing something? No "Created in Target" option that I can find.

I will send you my email and maybe you can send me a screen shot.
Thanks,
Teresa
Go to Top of Page

jon.woolgar



105 Posts

Posted - 05/13/2010 :  10:13:31  Show Profile  Visit jon.woolgar's Homepage Send jon.woolgar a Private Message  Reply with Quote
Massive apologies Teresa, I was describing the Change Manager interface.

In packager Plus that option is called "Add all new objects and data to the target database".

Regards Jon W
(Innovartis)
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