Background:
I'm working on migrating from SQL Server 2000 to SQL Server 2005. This is providing DB service for a C++ application that uses SQL Native Client to communicate with SQL Server via ODBC.
Problem:
I'm attempting to insert QNAN into a float column in the database. In my application, this value is stored as a double (value: 1.#QNAN00000000000) and is sent into the database as a parameter. This was not a problem in SQL Server 2000 but the same code gives me the following error in SQL Server 20005:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): 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.
Question:
Is it possible to get SQL Server 2005 to accept QNAN? If so, how?
According to the response to this bug report, SQL Server 2005 does not accept NaN or infinity, and this was apparently by design (due to sorting/comparison issues).