How to delete row 169441? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to delete row 169441?

Author

Topic

rlassiter
Starting Member

11 Posts

Posted - 2006-05-16 : 17:09:37

MSSQL database table DATE field has corruption. The error message says it is at row 169441. I cannot finish an export, browse to the record or delete it. I got the bad record below by exporting to a text file and this was the last record before it stopped.

Is there an SQL command like DELETE ROW 169441 from ....

This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"

This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-16 : 17:29:11

1.Copy the good data into a new table:
Copy row 1 through row BadRecord-1.
Copy row BadRecord+1 through the end.
2. Create the constraints and indexes on this new table
3. Drop the old table
4. Rename the new table to your old table

BTW, there is a data corruption forum here. You might want to post your DBCC CHECKDB results in that forum so that MS engineers can assist you.

Tara Kizer
aka tduggan

rlassiter
Starting Member

11 Posts

Posted - 2006-05-16 : 17:49:53

How do you refer to row numbers in the SQL? Can you give me a sample?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-16 : 17:52:16

SET ROWCOUNT 169440

INSERT INTO ...
SELECT ...
FROM YourTable
ORDER BY YourClusteredIndex

Tara Kizer
aka tduggan

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-16 : 21:14:05

fyi, this will insert 169440 rows
set rowcount will set the number of rows to be affected by the succeeding tsql unless you set it again to 0 which means all, or to another value

quote:
Originally posted by tkizer

SET ROWCOUNT 169440

INSERT INTO ...
SELECT ...
FROM YourTable
ORDER BY YourClusteredIndex

Tara Kizer
aka tduggan

--------------------
keeping it simple...

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-16 : 22:06:08

I recommend you run DBCC CHECKDB first (do not specify any repairs yet) and then post the output in the data corruption forum.

-ec

rlassiter
Starting Member

11 Posts

Posted - 2006-05-17 : 02:59:51

This worked. Thank-you.

quote:
Originally posted by tkizer

SET ROWCOUNT 169440

INSERT INTO ...
SELECT ...
FROM YourTable
ORDER BY YourClusteredIndex

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 06:38:49

quote:
Originally posted by jen

fyi, this will insert 169440 rows
set rowcount will set the number of rows to be affected by the succeeding tsql unless you set it again to 0 which means all, or to another value

quote:
Originally posted by tkizer

SET ROWCOUNT 169440

INSERT INTO ...
SELECT ...
FROM YourTable
ORDER BY YourClusteredIndex

Tara Kizer
aka tduggan

--------------------
keeping it simple...

RowCount wont affect Insert statement. Only Updates and Deletes will be affected


declare @t table(i int)

set rowcount 2
insert into @t select 1 union all select 6
set rowcount 0

select * from @t

set rowcount 2
update @t set i=100
set rowcount 0

select * from @t

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 06:51:48

quote:
RowCount wont affect Insert statement

Well then, how do you explain this?

declare @t table(i int)

set rowcount 1
insert into @t select 1 union all select 6
set rowcount 0

select * from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 07:06:06

Well. I think I didnt express it clearly. Jen told that setting Rowcount will insert as many rows as specified there if Insert statement is followed by RowCount.

Set RowCount 10
Insert into @t(i) Select 1
Set RowCount 0

Although Rowcount is set to 10, it wont insert the value 1 ten times in a table. But if you use Update or Delete then 10 rows will be affected(if any)

Set RowCount 10
Update @t set i=100
Set RowCount 0

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 07:31:49

Okay - I got ya

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources