Microsoft KB Archive/88201

{|
 * width="100%"|

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

BUG# OS/2: 1428 (4.2)

SYMPTOMS
Following message appears

Attempt to insert the value NULL into 'col2 ', table 'master.dbo.mytable'; column does not allow nulls. Update fails ( Msg 515, Level 16, State 3 ) when you execute this procedure create proc sp_isnull (@arg1 char(5) = NULL) as: UPDATE mytableV set col2 = isnull(@arg1, col2) where col1 = 'a'

select * from mytable order by col1

WORKAROUND
To ensure that you never encounter this intermittent problem, execute the procedure against the base table, &quot;mytable&quot;, instead of the view, &quot;mytableV&quot;. Another way to avoid this problem is to execute the procedure using the WITH RECOMPILE option after the problem has occurred.

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.

MORE INFORMATION
Mytable and mytableV (the view) are defined as follows:

create table mytable (col1 char(1), col2 char(5)) go  create view  mytableV as select * from mytable go  insert mytable values ('a', 'aaaaa') insert mytable values ('b', 'bbbbb') NOTE: The problem occurs only if you execute sp_isnull without any parameters right after you create the stored procedure; that is: exec sp_isnull If you include parameters, such as exec sp_isnull 'aaa', you will not encounter the problem even if you subsequently execute the procedure without any parameters, as shown above.
 * }

-

Last reviewed: April 28, 1997

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