BUG: Syscolumns.prec Has Incorrect Value For Some Integers

• Microsoft SQL Server, version 6.0

SYMPTOMS

In a new SQL Server 6.0 installation, all columns of type INT, SMALLINT, and TINYINT that allow NULL values will have the wrong precision specified in the prec column of the syscolumns system table (syscolumns.prec). Also, after an upgrade from SQL Server version 4.21 or 4.21a to SQL Server 6.0, all integer columns that allowed NULL values before the upgrade will have the wrong precision specified.

WORKAROUND

To check which of your columns have a wrong precision specified after the installation or upgrade to version 6.0, run the following script:

   set nocount on
   go
   use master
   go
   declare @db varchar(30), @msg varchar(75), @nl char(2)
   declare c_dbnames cursor for select name from sysdatabases
      where name <> 'tempdb' order by name
   select @nl = char(10)+char(13)
   select @msg = @nl + 'Reporting errors in syscolumns.prec for :'
   print @msg
   open c_dbnames
   fetch next from c_dbnames into @db
   while (@@fetch_status <> -1)
   begin
      if (@@fetch_status <> -2)
      begin
         select @msg = @nl + rtrim(upper(@db)) + @nl
         print @msg
         exec('use ' + @db + ' select tablename=object_name(c.id),
               column=c.name, datatype=t.name, c.prec
               from syscolumns c, systypes t
               where c.usertype=t.usertype
               and c.type in (56,52,48,38) and c.prec in (1,2,4)
               order by object_name(c.id)')
      end
      fetch next from c_dbnames into @db
   end
   deallocate c_dbnames
   go
   set nocount off
   go

   set nocount on
   go
   use master
   go
   sp_configure 'allow updates', 1
   reconfigure with override
   go
   declare @db varchar(30), @msg varchar(75), @nl char(2)
   declare c_dbnames cursor for select name from sysdatabases
      where name <> 'tempdb' order by name
   select @nl = char(10)+char(13)
   select @msg = @nl + 'Correcting syscolumns.prec for :'
   print @msg
   open c_dbnames
   fetch next from c_dbnames into @db
   while (@@fetch_status <> -1)
   begin
      if (@@fetch_status <> -2)
      begin
         select @msg = @nl + rtrim(upper(@db))
         print @msg
         exec('use ' + @db + ' update syscolumns set prec = 3
            where type in(48,38) and prec = 1')
         exec('use ' + @db + ' update syscolumns set prec = 5
            where type in(52,38) and prec = 2')
         exec('use ' + @db + ' update syscolumns set prec = 10
            where type in(56,38) and prec = 4')
      end
      fetch next from c_dbnames into @db
   end
   deallocate c_dbnames
   go
   sp_configure 'allow updates', 0
   reconfigure
   go

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

If this problem is not corrected for the MODEL database, all new databases will inherit this behavior for the integer columns in MODEL that allow NULL values. This problem does not pertain to tables created after the installation or upgrade, except for when you are creating a new table using SELECT INTO and refer to columns with an incorrect value in syscolumns.prec.

The incorrect value in syscolumns.prec does not affect the actual precision used for INT, SMALLINT, and TINYINT, but since this information is often used by client applications to size an input field, it can become necessary to correct this.

The table below shows the actual precision and the value in syscolumns.prec for each of the three integer datatypes as it will appear for columns allowing NULL values after the server is installed or upgraded.

   Datatype      Actual precision       Syscolumns.prec
   --------      ----------------       ---------------
   INT                10                       4
   SMALLINT            5                       2
   TINYINT             3                       1