Issue: A corrupt table (and corrupt backup, which somehow dbcc did not pick up) was kicking out errors at scan time. BCP failed at abotu 600,000 out of 800,000 rows. We could bcp past the error (sometimes you can bcp to the error, then bcp from there forwards.
So, I did something ugly:
1) build a list of all table IDs into a temp table (this can be done with an index scan, the index was not corrupt).
2) using a cursor, walk that list, and request those rows one at a time, logging into the temp table the result of the request
When I was done, I found that the rows that were inaccessable via a table scan were accessible via index traversal. We got lucky.
NOTE: I'd have written prettier code if I know it was going into my blog.
Hope this helps somebody out.
declare @curRID int, @char varchar(10)
while 1 = 1
begin
select @curRID
= min(RoomId) from RoomIDList where processed = 0
if @@rowcount
= 0 break
update
RoomIDList set processed = 1 where RoomId = @curRID
insert
RoomTemp select * from web_app.Room where RoomId = @curRID
update RoomIDList set processed = 2 where
RoomId = @curRID
select @char =
convert(varchar(10),@curRID)
print @char
