Thursday, March 24, 2011

Upgrading to SQL Server 2005: Cannot INSERT QNAN into float column?

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?

From stackoverflow
  • QNAN's are not supported under sql 2005. Neither are NAN's

    Technically speaking they weren't supported under SQL 2000 either. However, they worked.

    You might want to review the following:

    DATA_PURITY Checks under SQL 2005 http://support.microsoft.com/kb/923247

    Another place you might want to visit is: http://www.mssqltips.com/tip.asp?tip=1119

  • 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).

0 comments:

Post a Comment