Alter Column - strange behaviour msg 5074

Hi,
I've a very strange behaviour when launching a
ALTER TABLE X ALTER COLUMN V VarChar(16)
an error 5074 is returned; previously the column was varchar(15). The column
is indexed. Sometimes it works without problems, other times it returns the
error 5074 as mentioned. I cannot see differences between the databases
where it works and where it doesn't work.
To reproduce the problem, follow this task:
- from query analyzer execute this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table testdata
go
create table testdata (
id int,
aName varchar(10) null,
CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
)
on [primary]
go
create index ix_testdata_aName on testdata (aName) on [primary]
go
insert into testdata (id, aName) values(1,'aaa')
go
insert into testdata (id, aName) values (2, 'bbb')
go
insert into testdata (id, aName) values (4, null)
go
alter table testdata alter column aName varchar(11)
go
- all works fine
- then copy this table to another database using the export-wizard of
enterprise manager
- then try to alter the table on the second database with
alter table testdata alter column aName varchar(12)
go
and you receive the error.
best regards
Anton SantaWhat is the TEXT of the error message?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Anton Santa" <santa@.sabesoft.it> wrote in message
news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> I've a very strange behaviour when launching a
> ALTER TABLE X ALTER COLUMN V VarChar(16)
> an error 5074 is returned; previously the column was varchar(15). The
column
> is indexed. Sometimes it works without problems, other times it returns
the
> error 5074 as mentioned. I cannot see differences between the databases
> where it works and where it doesn't work.
> To reproduce the problem, follow this task:
> - from query analyzer execute this:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table testdata
> go
> create table testdata (
> id int,
> aName varchar(10) null,
> CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> )
> on [primary]
> go
> create index ix_testdata_aName on testdata (aName) on [primary]
> go
> insert into testdata (id, aName) values(1,'aaa')
> go
> insert into testdata (id, aName) values (2, 'bbb')
> go
> insert into testdata (id, aName) values (4, null)
> go
> alter table testdata alter column aName varchar(11)
> go
> - all works fine
> - then copy this table to another database using the export-wizard of
> enterprise manager
> - then try to alter the table on the second database with
> alter table testdata alter column aName varchar(12)
> go
> and you receive the error.
> best regards
> Anton Santa
>|||the message is italian since I've an italian engine installed:
Server: Msg 5074, Level 16, State 8, Line 1
Il indice 'ix_testdata_aName' dipende da colonna 'aName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN aName non è riuscita perché uno o più oggetti
accedono a questa colonna.
meaning:
The index .. is dipendent on column ..
ALTER TABLE DROP COLUMN .. failed because one or more objects access this
column.
..
regards
Anton Santa
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> ha scritto nel messaggio
news:Oap9AhY%23EHA.2180@.TK2MSFTNGP10.phx.gbl...
> What is the TEXT of the error message?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Anton Santa" <santa@.sabesoft.it> wrote in message
> news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
>> Hi,
>> I've a very strange behaviour when launching a
>> ALTER TABLE X ALTER COLUMN V VarChar(16)
>> an error 5074 is returned; previously the column was varchar(15). The
> column
>> is indexed. Sometimes it works without problems, other times it returns
> the
>> error 5074 as mentioned. I cannot see differences between the databases
>> where it works and where it doesn't work.
>> To reproduce the problem, follow this task:
>> - from query analyzer execute this:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') =>> 1)
>> drop table testdata
>> go
>> create table testdata (
>> id int,
>> aName varchar(10) null,
>> CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
>> (
>> [id]
>> ) ON [PRIMARY]
>> )
>> on [primary]
>> go
>> create index ix_testdata_aName on testdata (aName) on [primary]
>> go
>> insert into testdata (id, aName) values(1,'aaa')
>> go
>> insert into testdata (id, aName) values (2, 'bbb')
>> go
>> insert into testdata (id, aName) values (4, null)
>> go
>> alter table testdata alter column aName varchar(11)
>> go
>> - all works fine
>> - then copy this table to another database using the export-wizard of
>> enterprise manager
>> - then try to alter the table on the second database with
>> alter table testdata alter column aName varchar(12)
>> go
>> and you receive the error.
>> best regards
>> Anton Santa
>>
>