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

• Microsoft SQL Server, version 6.0

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.

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.

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.