BUG: Error 530 When Selecting NULL CHAR() Local Variables

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, versions 4.21 and 4.21a

       NT:  210 (4.2)

SYMPTOMS

When executing a query that includes a local variable of type CHAR() in the results set, SQL Server repeatedly returns error 530 to the client application.

CAUSE

If a local variable of type CHAR() is included in the results set of a query involving a worktable, and if the value stored in the local variable is NULL, SQL Server will return error 530 "attempt to insert NULL value in worktable" for each row in the results set. Queries involving worktables typically involve GROUP BY, ORDER BY, and DISTINCT clauses, although worktables may be used by the query optimizer in other situations.

The following script illustrates one case where erroneous 530 errors will be returned:

   create table test_table( col1 int )
   go
   insert into test_table values ( 1 )
   go
   /* this query will fail */
   declare @var char( 5 )
   select @var = null
   select col1, @var from test_table order by col1
   go

WORKAROUND

If the local variable is declared as type VARCHAR( n ), the query will work correctly.

STATUS

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