FIX: Sp_columns Returns More Than One Row for a Column

ID: Q158464

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 15895 (NT, 6.5)

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          : kbusage SSrvStProc kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 4, 1999