Re: Alter Column - strange behaviour msg 5074

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 10:51:46 -0500

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
>
>