Microsoft KB Archive/141031

{|
 * width="100%"|

BUG: Syscolumns.prec Has Incorrect Value For Some Integers

 * }

Q141031

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

BUG# NT: 12248 (6.00)

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

To correct these errors, you can run the following script:

  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

Additional query words: upgrade sp_columns SQLColumns

Keywords : kbenv kbsetup

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600