BUG: ISNULL Function Causes Update Through View to Fail

• Microsoft SQL Server version 4.2 for OS/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 )

   create proc sp_isnull (@arg1 char(5) = NULL)

   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, "mytable", instead of the view, "mytableV". 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')

   exec sp_isnull