Knowledge Base

FIX: Sp_columns Returns More Than One Row for a Column

Article ID: 158464

Article Last Modified on 10/3/2003

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q158464

SYMPTOMS

If a table contains a column bound to a default with a larger comment, the system stored procedure sp_columns may return more than one row for each column of a user table. This problem causes error 3191, "Can't define field more than once," in the Microsoft Jet engine if you try to attach such a table.

WORKAROUND

To work around this problem, do one of the following:

Drop and re-create the default without any comments.

-or-
Use default constraints rather than defaults.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following script illustrates the problem:

create table t1(c1 int not null)
go

/****** Object:  Default dbo.dev_zero    Script Date: 10.09.96 18:39:01
******/ 
/****** Object:  Default dbo.dev_zero    Script Date: 10.09.96 18:37:49
******/ 
/****** Object:  Default dbo.dev_zero    Script Date: 10.09.96 18:37:08
******/ 
create default dev_zero as 0
go
exec sp_bindefault dev_zero, 't1.c1'
go
sp_columns t1
go

Additional query words: SQLColumns default

Keywords: kbbug kbfix kbusage KB158464