Prev: kb923247
Next: Creating a view with concatenated columns
From: Imre Ament on 21 Jan 2010 10:56
Hi,

I have to repair overflowed column contents in a SQL Server 2005 database.
I would like to write a C++ program (with OLEDB) for fix the problem (on
future databases also).
I had found http://support.microsoft.com/kb/923247 but the solution does not
work on my environment.

There is a double precision floating point column ('float' in SQL Server),
The query (rewrited to new object names) what should show the damaged rows
does not work.

SELECT IDBilder, Im_IDMeasurement FROM Bilder
WHERE (Im_MicXPos <> 0.0) AND (Im_MicXPos < 2.23E-308 OR Im_MicXPos >
1.79E+308) AND (Im_MicXPos < -1.79E+308 OR Im_MicXPos > -2.23E-308);

Always return the known error message (I try it only in the 'Management
Studio)'
Msg 9100, Level 23, State 2, Line 2
Possible index corruption detected. Run DBCC CHECKDB.

If the (damaged) column arise in the 'WHERE' clause the query failed.
As I see there is no way to collect only the damaged rows in a rowset.
If someone know a solution, please say to me to I can avoid to investigate
all row in the table(s) :-)

Regards,
Imre
From: Dan Guzman on 22 Jan 2010 08:08
> If the (damaged) column arise in the 'WHERE' clause the query failed.

You might need to first drop indexes on the column so that the query doesn't
attempt to use those to locate the problem data. The bad column should only
be referenced in the SET clause of the UPDATE.

> As I see there is no way to collect only the damaged rows in a rowset.

The undocumented TABLERESULTS option of DBCC CHECKDB (see below) will return
the errors in a rowset but I think you will also need to run DBCC PAGE to
identify the row values needed for the UPDATE WHERE clause. This will be a
bit of a pain to do programmatically.

DBCC CHECKDB WITH DATA_PURITY, TABLERESULTS;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"Imre Ament" <ImreAment(a)discussions.microsoft.com> wrote in message
news:2AFBEF38-B203-45E7-A367-58DFEE71511E(a)microsoft.com...
> Hi,
>
> I have to repair overflowed column contents in a SQL Server 2005 database.
> I would like to write a C++ program (with OLEDB) for fix the problem (on
> future databases also).
> I had found http://support.microsoft.com/kb/923247 but the solution does
> not
> work on my environment.
>
> There is a double precision floating point column ('float' in SQL Server),
> The query (rewrited to new object names) what should show the damaged rows
> does not work.
>
> SELECT IDBilder, Im_IDMeasurement FROM Bilder
> WHERE (Im_MicXPos <> 0.0) AND (Im_MicXPos < 2.23E-308 OR Im_MicXPos >
> 1.79E+308) AND (Im_MicXPos < -1.79E+308 OR Im_MicXPos > -2.23E-308);
>
> Always return the known error message (I try it only in the 'Management
> Studio)'
> Msg 9100, Level 23, State 2, Line 2
> Possible index corruption detected. Run DBCC CHECKDB.
>
> If the (damaged) column arise in the 'WHERE' clause the query failed.
> As I see there is no way to collect only the damaged rows in a rowset.
> If someone know a solution, please say to me to I can avoid to investigate
> all row in the table(s) :-)
>
> Regards,
> Imre

 | 
Pages: 1
Prev: kb923247
Next: Creating a view with concatenated columns