Microsoft KB Archive/199105

= BUG: ROUND Function with NULL Length Returns TDS Error =

Article ID: 199105

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q199105



BUG #: 53421 (SQLBUG_70)



SYMPTOMS
The Transact-SQL ROUND function may return a Tabular Data Stream (TDS) error if the second argument (length) is passed in as NULL.



WORKAROUND
To avoid the problem, check the second argument before passing it in to the ROUND function. If it is NULL, simply return NULL and do not use the ROUND function at all. You can use the ISNULL function to accomplish this.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



MORE INFORMATION
The syntax for the ROUND function is:

ROUND(numeric_expression, length [,function])

Where:
 * Numeric_expression is the number or expression to be rounded.
 * Length is the desired precision.
 * Function is either 0 or nonzero. The default (0) means to round; nonzero means to truncate.

If the length parameter is NULL and it is supplied either from a table column (as a parameter) or from a result set, the ROUND function results in one of the following TDS errors:

DB-Library: Possible network error: Bad token from SQL Server: Datastream processing out of sync.

Net-Library error 0: (null)

DB-Library: DBPROCESS is dead or not enabled.

-or-

[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server.

For example, following query demonstrates this behavior:

Create table testtable (col1 int not null, col2 int null) go insert into testtable values(1,NULL) go select ROUND(col1,col2)from testtable go declare @param int select @param = NULL select ROUND(1,@param) go select ROUND(1,(select NULL)) go

However, the if the NULL length is passed directly as:

select ROUND(1,NULL)

SQL Server returns error message 8116:

Argument data type void type is invalid for argument 2 of round function.

This is true for sp_dbcmptlevel 65 as well.

In SQL Server 6.5, such a query returns NULL.

Additional query words: err msg

Keywords: kbbug kbpending KB199105

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.