Microsoft KB Archive/87484

{|
 * width="100%"|

The information in this article applies to:
 * Microsoft SQL Server version 4.2 for OS/2

BUG# OS/2: 1405 (4.2)

SYMPTOMS
If you INSERT into a table using a SELECT statement with a WHERE clause containing system functions, such as USER_NAME or OBJECT_NAME, to compare with a null, and if the argument in the system function is a table column that does not allow nulls, then the following error occurs:

Column of type (varchar) does not allow nulls. It may not be compared with nulls. (Msg 221, Level 16, State 1) For example, the following query causes error 221 as shown above: create table t1 (i int not null) create table t2 (c char(30) null) insert t1 values (1000) insert t2 (c) select user_name(i) from t1     where user_name(i) !=null Please note that we are not comparing column i with null. Rather, we are comparing the result of the system function USER_NAME (that takes the nonnull column as the argument) with nulls. The system function might return null even if the argument is not null. However, the same SELECT statement

select user_name(i) from t1 where user_name(i)!=null runs fine by itself. In general, SQL Server allows you to compare the result of a system function and nulls. For example, the following query is valid

select 1 where user_name(1000)=null although this query is not valid: select 1 where 1000=null The problem occurs only when you use the SELECT statement with the null comparison in an INSERT statement.

CAUSE
SQL Server incorrectly handles the comparison of the result of a system function with nulls if you use the comparison in a SELECT statement within an INSERT statement.

WORKAROUND
Use another system function, ISNULL, to force SQL Server to accept the null in the following way:

insert t2 (c) select user_name(i) from t1     where ISNULL(user_name(i),null) != null

STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
 * }

-

Last reviewed: April 28, 1997

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.