BUG: Error 515 with Concatenated NULLs in WHERE Clause and ORDER BY

• Microsoft SQL Server version 7.0

SYMPTOMS

A query may return error 515, which is the following:

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

• A string concatenation is done in the WHERE clause against a character column.



• This column is used in an ORDER BY clause.



• This column contains a NULL.



• This column is not indexed.



• The concat_null_yields_null option is not set for this connection or this database. This condition can be met by any one of the following:
  
  
  • Explicitly issuing the command set concat_null_yields_null off.
  
  
  
  • Using a client application that uses DB-Library or an ODBC driver older than version 3.70.0623.
  
  
  
  • Running the database in SQL Server 6.x compatibility mode.
  
  
  

WORKAROUND

To work around this problem, eliminate one of the conditions listed above that are known to cause it. For example, create an index on the column, or eliminate the NULL value(s).

Usually, the best workaround is to index the column used in the ORDER BY clause. In addition to resolving this problem, in most cases, a query gains performance from indexing the columns used with the ORDER BY clause.

STATUS

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

Additional query words:

Keywords : SSrvTran_SQL kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug