FIX: sp_addtype Ignores NULL For the nulltype Parameter

• Microsoft SQL Server, version 6.0

SYMPTOMS

If the nulltype parameter passsed to sp_addtype is NULL, the user-defined datatype should be nullable by default. This is not the case as can be verified from the allownulls column of systypes. A user-defined datatype has an allownulls value of zero in systypes even though it is defined with NULL parameter to sp_addtype.

WORKAROUND

Enclose the keyword NULL within single quotes. For example:

   exec sp_addtype lname,'char(20)', 'null'

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
   exec sp_addtype lname,'char(20)', null
   go
   create table testaddtype (x lname)
   go
   print ' '
   print 'SHOULD ALLOW NULL TO BE INSERTED BUT DOES NOT'
   print ' '
   insert into testaddtype values  (NULL)
   go
   print ' '
   print 'ALLOWNULLS SHOULD ACTUALLY BE 1 IN THE FOLLOWING RESULT:'
   print ' '
   select name, allownulls from systypes where name = 'lname'
   go