SQL Server Performance

Storing NaN-s under SQL Server 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by dinko, Oct 4, 2006.

  1. dinko New Member

    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.
  2. Roji. P. Thomas New Member

  3. dinko New Member

    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.
  4. Roji. P. Thomas New Member

    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
  5. dinko New Member

Share This Page