FIX: SELECT * Misleading if char or varchar Field is NULL

• Microsoft SQL Server, version 6.0

SYMPTOMS

If a char or varchar field in a row has a NULL character (ASCII value zero), then a SELECT * on this table shows the fields after this field as blank. A SELECT on the remaining columns works fine.

WORKAROUND

If you know which field has the NULL character, place it last in the SELECT list. For example, in the above scenario, replace the SELECT * with the following:

   SELECT y, z, x
   FROM testnull

STATUS

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

MORE INFORMATION

To replicate this problem:

   use pubs
   go
   create table testnull (x char(20) null, y char(20) null, z int)
   go
   insert into testnull values('problem_field'+char(0), 'microsoft', 20)
   go
   print ' '
   print 'RESULT OF SELECT * :'
   print ' '
   select * from testnull
   go
   print ' '
   print 'RESULT OF SELECT ON REMAINING COLUMNS:'
   print ' '
   select y, z from testnull
   go