Re: Update failure on Sql Server 2008

Tech-Archive recommends: Speed Up your PC by fixing your registry

I do not believe it is at all likely the conversion to SQL 2008 did this. I certainly have never heard of that happening. But I suppose it is possible.

You are right that you cannot do a DATA_PURITY check in SQL 2000. The only way I know to check is to run DBCC PAGE commands to see in hex what is in each row. I'm not sure, it might also work to bcp out the row in Native format an examine the output of that. I suspect the effort is not worth it since I am almost certain you will find the error is actually in your SQL 2000 database, and if it is, then that will not help you fix your SQL 2008 database.

AFAIK, your problem must have been originally caused by updating some table with some process other than normal INSERT or UPDATE statements when you are inserting/updating constants or computed values. Those statements cannot cause this problem (because SQL would verify the data) unless there was some sort of physical I/O error. And if you were getting that many I/O errors, they would have affected many other things instead of only certain columns that are type decimal.

The most common cause I have seen is doing a bcp or BULK INSERT in native mode, and the file that is being input is corrupted in those columns in one or more rows. When you are doing native mode, these processes did not check the data, but just took whatever bit pattern existed for those columns and placed it in the row. Since this could have happened any time since the database was created, it can be almost impossible to detirmine when and why it happened.

Tom

"fhoornaert" <yourdisplayname@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:uxbvn9IzJHA.5032@xxxxxxxxxxxxxxxxxxxxxxx
Hi Tom,

Thank you for the reply. You are 100% right.
I've got a lot of records where I get : "Column "..." value is out of range for data type "decimal"
after running : "DBCC CHECKDB WITH DATA_PURITY"

I wanted to verify this on our SQL 2000 database server to see if it was a problem due to migrating from 2000 to 2008, but
WITH DATA_PURITY is not a valid option on SQL 2000.

Any idea how this invalid data could get into our database ? The database is used by only 1 app, C# which uses ADO.NET to do all the updates.

Kind regards,
Frederic Hoornaert



"Tom Cooper" <tomcooper@xxxxxxxxxxx> wrote in message news:%23H5ISFeyJHA.5964@xxxxxxxxxxxxxxxxxxxxxxx
It was possible in SQL 2000 to get illegal data into columns. That is, not just the wrong data, but "impossible" data. This could happen in any data type that has certain bit patterns that are not allowed. This may be what has happened to you. That is, this row does not really contain 0, but rather an "impossible" value that gets returned as zero. Which may be why you get 0 when you select the row, but no row is found when your where condition includes qtyinvoiced=0.

To check this, run DBCC CHECKDB WITH DATA_PURITY (to check the whole database or DBCC CHECKTABLE ("dbo.SalesLines") WITH DATA_PURITY to check only the dbo.SalesLines table (see BOL on DBCC CHECKDB and DBCC CHECKTABLE). Running this may take some time since every row in the database (or table) must be read.

If it finds data purity errors, you will want to read http://support.microsoft.com/kb/923247 (this KB article say it applies to SQL 2005, but it is referenced by the SQL 2008 BOL and should help you fix it you problems). That article goes into what may have caused those errors, but how to fix them. Unfortunately, fixing them mainly involves finding the rows that have this problem and updating that colum of that row to a valid value.

Tom




.

Relevant Pages