BUG: Stored Proc. Generates Erroneous 515 Errors w/ Bound Rule

ID: Q125572

The information in this article applies to:

Microsoft SQL Server, version 4.2

BUG# NT: 967 (4.2)

SYMPTOMS

The following stored procedure can generate erroneous 515 errors,

   Attempt to insert the value NULL into column '%.*s',
   table '%.*s'; column does not allow nulls.  Update fails.

when executed with a rule bound to a column on the table.

The table: create table rde_testtab3

   (col1   int
   ,col2   varchar(255)
   ,col3   datetime
   ,col4   bit
   ,col5   timestamp)

The stored procedure: create procedure testparams;1

   (@c1   int=null
   ,@c2   varchar(255)=null
   ,@c3   datetime=null
   ,@c4   tinyint=null
   ,@wh   int)
   as
   BEGIN
      update rde_testtab3
       set col1 = ISNULL(@c1,col1)
       ,set col2 = ISNULL(@c2,col2)
       ,set col3 = ISNULL(@c3,col3)
       ,set col4 = ISNULL(@c4,col4)
       where col1 = @wh
   END

With the rule bound to column 2, similar indications have been seen with a similar rule bound to column 1:

   create rule mr_NotBlank as (ltrim(rtrim(@value)) != '')

The following executions of the stored procedure have produced the 515 error:

   exec testparams;1 @c3='1/5/95', @wh=20668
   exec testparams;1 @c2='A TEST THREE TIMES', @wh=20668

STATUS

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

Additional query words: Windows NT

Keywords          : kbprg SSrvStProc kbbug4.20 SSrvWinNT 
Version           : 4.2 4.21 4.21a 4.21a.sp1
Platform          : WINDOWS

Last Reviewed: May 1, 1997