BUG:After ALTER TABLE to Add Identity Col, type_name Incorrect

• Microsoft SQL Server version 6.0

SYMPTOMS

If you create a table with an int identity field included in the CREATE TABLE statement sp_columns shows type_name correctly as "int identity." However, if you create the table without an identity field and then ALTER TABLE to add an int identity column, sp_columns shows type_name for the identity field as "int" and not "int identity." This can also be viewed from 16-bit ODBC Test using SQLColumns which is equivalent to sp_columns.

STATUS

Microsoft has confirmed this to be a problem in 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

To reproduce the problem, run the following script:

create table ram1 (x int, y int identity)
create table ram2 (x int)
go
alter table ram2 add y int identity
go
print "FOR THIS ONE, y CORRECTLY SHOWS UP AS INT IDENTITY->"
exec sp_columns ram1
go
print ' '
print "FOR THIS ONE, y SHOWS UP AS INT->"
exec sp_columns ram2
go 

Additional query words: sql6 winnt

Keywords          : kbprg SSrvProg kbbug6.00 
Version           : 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 24, 1999