BUG: Syscolumns.prec Has Incorrect Value For Some Integers
ID: 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 SSrvGen SSrvInst kbbug6.00
Version : 6.0
Platform : WINDOWS
Issue type :