Storing NaN-s under SQL Server 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Storing NaN-s under SQL Server 2005

Hi, We have a problem storing NaN-s in an SQL Server 2005 DB. When a NaN value is supplied for a field in an SQL INSERT statement, the server responds with the following error: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@val"): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. We have been storing NaN-s in the DB under SQL Server 2003 without any problem. Is this behavior intended in 2005, is it a bug, or is it configurable? Any suggestions will be greatly appreciated.
Why can’t you store them as NULL? Roji. P. Thomas
http://toponewithties.blogspot.com
quote:Originally posted by Roji. P. Thomas Why can’t you store them as NULL?

Because of an implementation quirk on the client side — short story is that we distinguish between NULL and NaN, and we’re stuck with this for the near future.
quote:Originally posted by dinko
We have been storing NaN-s in the DB under SQL Server 2003 without any problem

What is SQL server 2003? SQL server does not support the NaN value, (or infinity for that matter) for float data type. It seems that, in sQL server 2000, it was poosible to assign a Double.NaN constant to a parameter and SQL server successfully performs the insert, but retrieving that particular row throws an Error. Refer to the following links. http://fugato.net/2005/02/08/sql-server-nastiness/
http://groups.google.com/group/micr…&q="SQL Server"+"Nan"&rnum=5#9572e4e4a34ffcd1 http://groups.google.com/group/micr…&q="SQL Server"+"Nan"&rnum=3#a1e8d6b226a6df4c
Roji. P. Thomas
http://toponewithties.blogspot.com
quote:What is SQL server 2003?

SQL Server 2000, sorry.

This explains a lot, especially the first link. Thank you.
]]>