SQL Server nastiness
SQL Server is sometimes fine, and sometimes a harbinger of evil.
You’d think you could just insert whatever into it and if you give it something it can’t handle, it would belch an appropriate error message back at you, being the enterprise-class software it is. Right?
Or at least, you’d think that if you were to find a case of bogus data that you could feed SQL Server, that it would incorrectly accept and then go corrupt, then that case should be a pretty obscure, oddball case, since SQL Server would of course be tested for all the typical cases … being the enterprise-class software it is. Right?
You certainly wouldn’t expect that case to be a simple NaN (not-a-number) value, would you?
Well, try inserting Double.NaN into a float column using the .Net System.Sql API. Observe how it is merrily accepted, after which:
- simple queries involving that column (or just
*) will fail or return partial result sets - queries with aggregates in them will fail with “Error 3628: A floating point exception occurred in the user process. Current transaction is canceled.”
- Query Analyzer will silently ignore these rows in any query that involves reading that column
- An Enterprise Manager query will manage to display the bogus rows (with
-1.#INDshown for the bogus value), but trying to change them manually results in an error saying another user has updated the row so your view of it is out of date
Once the bogus value is in there, the only remedy I’ve found is to run an UPDATE tblName SET floatCol = @someOkValue WHERE id IN (x,y,z, ...) where I fill in the IN list by hand by eyeballing the bad rows in Enterprise Manager.
Sure, inserting a Double.NaN into SQL server may be an error … but this is no way to handle an error.
I am now manually checking Double.IsNaN() and throwing an exception on true, before every assignment to a SqlDbType.Float parameter in my code. Ojbara.
February 9th, 2005 at 11:00 pm
ég tel mig nú vera frekar sleipan í þessu tölvudrasli… en wtf !…
November 23rd, 2005 at 2:17 am
Thank you so much for this note - I was stuk on the same error and the Update script you put in helped out a bunch!!
November 23rd, 2005 at 2:44 am
After nine-and-a-half months, I’m glad it helped somebody :)
December 9th, 2005 at 9:59 am
Helped me too, Thanks!!
January 4th, 2006 at 2:56 am
Talk about an obscure error. I’ve been programming pages with SQL since version 6.5, and have never run into this one until now. If it wasnt for your blog, I’d be hooped. Thanks for the help.
March 1st, 2006 at 2:51 pm
This helped me - thanks so much!
May 18th, 2006 at 11:14 am
Thanks for this post. Unfortunately, I’m dealing with a database that has rows created with this error. Can I do anything to recover from this? Ideally I’d like to delete the offending rows, and avoid having to drop/recreate the entire table.
May 18th, 2006 at 1:43 pm
As I said, the only remedy I know of is
where you fill in the id list by hand, by eyeballing the bad rows in Enterprise Manager. If your table is very big, then I don’t imagine you’ll enjoy that much. But I don’t know any automated way to match these broken values in a WHERE clause, sorry.
May 23rd, 2006 at 12:53 pm
Thanx a bunch dude!
Ran into this weird error some days ago.. couldn’t make head or tails of the weird “-1,#IND” value in Enterprise Manager…. now I have to go bughunting to see why it inserts Double.Nan..
February 5th, 2007 at 5:34 pm
Try this:
CREATE FUNCTION IsNaN
(
@number float
)
RETURNS int
AS
BEGIN
return isnumeric(convert(varchar(100), @number))
END
GO
and then …
UPDATE tblName SET floatCol = @someOkValue where dbo.IsNan(floatCol) = 1
February 5th, 2007 at 5:50 pm
Sorry! Typo. IsNan is exactly the opposite, and it should take care of nulls… :
CREATE FUNCTION IsNaN
(
@number float
)
RETURNS int
AS
BEGIN
return case isnumeric(convert(varchar(100), coalesce(@number,0))) when 1 then 0 else 1 end
END
GO
February 5th, 2007 at 5:52 pm
Thanks for that … is that just a conjecture, something you’re suggesting I try out, or have you already successfully tested this as a workaround under the same circumstances?
I don’t have a current test case to try this out on, and am disinclined to go creating one now if you can already confirm that this works. :)
May 15th, 2007 at 1:53 pm
Thanks for the hint on creating a function. It worked like a charm.
November 5th, 2007 at 8:18 pm
Everyone, I ran into this error on one of my production databases and was able to successfully resolve the problem. It was caused by index distribution statistics. However, it was fairly difficult to identify at first since all the user would see is the error when the APP attempted to view data on the table.
I ran a trace and narrowed down the affected tables. Next, I ran dbcc show_statistics ‘table name’ and statement consistently failed on one table in particular. I dropped the statistics from the table and recreated the stats using sp_autostats ‘table name’. Afterwards, the error was corrected. The article below led me in the correct direction. It should be mentioned that I DID NOT install SP4 to resolve the problem.
http://support.microsoft.com/kb/816503
Hope this helps.
December 17th, 2007 at 3:00 pm
Hi
Thanks for the Article !!!
In Our System it was Insereting Double.NaN into a float column that cause the problem
You can see the value “NaN” in SQL 2005 ,but not in SQL 2000!
Thanks again
February 25th, 2008 at 12:43 pm
Thanks very much for the article, and a special thanks to Antonio for the nifty little function.
I was banging my head against the wall in SSIS trying to copy data from SQL2000 to SQL2005, with error messages like these:
Description: “OLE DB provider ‘STREAM’ for linked server ‘(null)’ returned invalid data for column ‘[!BulkInsert].column_name’. “.
Turned out it was due to NaN being inserted by a third party. And SSIS did not like them.
Anyhoo… I modified Antonio’s function to return Nulls instead NaN and used them on the offending columns. Works like a charm. So thanks again everybody