FIX: A View With an Outer Join Can Cause An Access Violation

ID: Q141542

The information in this article applies to:
BUG# NT: 12004 (6.00)

SYMPTOMS

A SELECT against a view can result in the following error message:

Msg 530, level 18, State 0
Attempt to insert NULL value into column %d in work table (table id);
column does not allow NULLS, UPDATE fails.

The connection will be terminated and the SQL Server error log will show a language exec error and an access violation.


CAUSE

For this bug to occur, the view needs to meet the following conditions:

  1. The SELECT clause needs to have a DISTINCT in it.


  2. The query being performed by the SELECT must use an outer join.


  3. A column in one of the tables must be a bit field.


When SQL Server forms the outer join work table, it mistakenly puts NULLS in the work table where the bit field is. By definition, a bit field must be either 0 or 1, and in the case of a NULL it should default to 0; however, in this case it does not.


WORKAROUND

You can circumvent the problem by avoiding any one of the three conditions that must be present to cause it.


STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.

Additional query words: sql6 views tsql 


Keywords          : kbusage SSrvGen SSrvTran_SQL kbbug6.00 kbfix6.00.sp2 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 23, 1999