Data integrity error

Hi All,
This is a wierd one... I have a table for which I wish to remove the
IDENTITY attribute for one of the columns. To do this I intended to
create a new column, copy the data from the identity column to the new
column, delete the identity column and then rename the new column...
All pretty straight forward stuff.
However, when trying to do step 2 (up, I get a recursive "Data
integrity error" message appearing in my results window (there are only
15 rows in the table and I get approximately 20K "DIE" messages a
minute)
The script I am using is as follows;
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
Column names have been changed to protect the innocent ;)
>From where I am sitting this all looks pretty generic, and I can't see
why my script is kicking up such a fuss...
Merry christmas to all,
Best, Marki would do it otherwise:
why dont you try to create a new table with all needed indexes, identity, etc'
and then use the select into old table to new table. if your table consist
only 15 rows - may be you should consider rebuid the index after you do the
select into. it worth a try...
tomer
"Cuperman" wrote:
> Hi All,
> This is a wierd one... I have a table for which I wish to remove the
> IDENTITY attribute for one of the columns. To do this I intended to
> create a new column, copy the data from the identity column to the new
> column, delete the identity column and then rename the new column...
> All pretty straight forward stuff.
> However, when trying to do step 2 (up, I get a recursive "Data
> integrity error" message appearing in my results window (there are only
> 15 rows in the table and I get approximately 20K "DIE" messages a
> minute)
> The script I am using is as follows;
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = [id]
> ALTER TABLE tToBeAltered DROP COLUMN [id]
> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
> Column names have been changed to protect the innocent ;)
> >From where I am sitting this all looks pretty generic, and I can't see
> why my script is kicking up such a fuss...
> Merry christmas to all,
> Best, Mark
>|||... and also do DBCC CHECKDB on the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tomer" <tomer@.discussions.microsoft.com> wrote in message
news:9CDA2C47-ADF2-4AF9-AAA0-2730F5D61DAB@.microsoft.com...
>i would do it otherwise:
> why dont you try to create a new table with all needed indexes, identity, etc'
> and then use the select into old table to new table. if your table consist
> only 15 rows - may be you should consider rebuid the index after you do the
> select into. it worth a try...
> tomer
> "Cuperman" wrote:
>> Hi All,
>> This is a wierd one... I have a table for which I wish to remove the
>> IDENTITY attribute for one of the columns. To do this I intended to
>> create a new column, copy the data from the identity column to the new
>> column, delete the identity column and then rename the new column...
>> All pretty straight forward stuff.
>> However, when trying to do step 2 (up, I get a recursive "Data
>> integrity error" message appearing in my results window (there are only
>> 15 rows in the table and I get approximately 20K "DIE" messages a
>> minute)
>> The script I am using is as follows;
>> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
>> UPDATE tToBeAltered SET [new_id] = [id]
>> ALTER TABLE tToBeAltered DROP COLUMN [id]
>> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
>> Column names have been changed to protect the innocent ;)
>> >From where I am sitting this all looks pretty generic, and I can't see
>> why my script is kicking up such a fuss...
>> Merry christmas to all,
>> Best, Mark
>>|||Hi Tomer,
Thanks for taking the time to respond.
I actually have more than one table that will be modified, so was
scripting a solution that would do all tables. Most are working OK
(where Identity is Numeric or Int), but this one table is causing a
real problem.
At the moment I am working on a test DB, hence 15 rows, but when we get
to production environment the table will contain millions of rows.
Thanks for suggesting the option, but I think a full table recreate
would be too dangerous (missing constraints here or there) and more
importantly too slow for my requirements here.
I guess I was hoping that there is a known configuration issue / bug
that I could simply tweak to fix this. Something seems fundamentally
wrong here.
Best, Mark|||** Update: There is even a problem with:
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = 1
This throws the same D.I.E. error... is this a corrupt DB?
Thanks, Mark|||Thanks for taking the time to respond Tibor.
results from DBCC CHECKDB
...
DBCC results for 'tToBeAltered'.
There are 13 rows in 2 pages for object 'tToBeAltered'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
...
This seems OK to me. Doesn't suggest anything to worry about...
Best, Mark|||Hi Mark,
It sounds pretty weird. but may be you should:
1. to run dbcc indexdefrag. it will not cause any locks - you can run it
online.
2. if you work on a dev enviorment may be you should take the last backup
of this db and restore it and then try to do what you are doing with the id
column.
thx,
Tomer
"Cuperman" wrote:
> ** Update: There is even a problem with:
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = 1
> This throws the same D.I.E. error... is this a corrupt DB?
> Thanks, Mark
>|||I hate it when a thread just runs out of steam and no solution is ever
given ...luckily I have found the cause of my problem... there was an
update trigger that was causing problems...
To fix this all I needed was:
/*************************************************************/
ALTER TABLE tToBeAltered DISABLE TRIGGER ALL
GO
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
GO
ALTER TABLE tToBeAltered ENABLE TRIGGER ALL
GO
/*************************************************************/
Thanks to all who offered assistance.
Best Regards,
Mark