FIX: Sp_columns Returns More Than One Row for a Column

• Microsoft SQL Server, version 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