views:

448

answers:

2

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?

+2  A: 

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

Chris Lively
+2  A: 

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

Eric Rosenberger