Enterprise Mgr - alter table fails

My developers just started having this problem a few weeks ago. Nearly
any type of "design" table changes are failing in Enterprise Mgr. This
only seems to be happening on the servers running Enterprise Edition.
Standard Edition and MSDE do not have the problem.
All are running SQL2000 - SP4. Some servers are Win2000 Server SP4 and
others are Win2003 Server - SP4. The Windows version does not seem to
matter. I am not aware of any maintenace applied to the SQL servers,
but all workstations have recently had automatic updates turned on and
have received some updates.

Here is the most common example. The developer needs to change the
length of a field in a table. They use Enterprise Mgr, right click on
the table and select "design". They make their change and when they
hit save, they get the following error. (There is no data in the
table.)

'prob1' table
- Unable to create index 'PK_prob1'.
ODBC error: MicrosoftODBC SQL Server DriverSQL ServerTable 'prob1'
already has a primary key defined on it.
MicrosoftODBC SQL Server DriverSQL ServerCould not create constraint.
See previous errors.


I can not just tell them to switch over and use Query Analyzer. Some
of these people have little or no SQL knowledge and would never make it
through alter table statements. They build tables for their front page
apps and that's as far as they get into SQL.

Any thoughts would be helpful. I've scoured support and newsgroups and
can only find old 6.5 and 7.0 reports. I've included the DDL to create
the table if needed. I get the error just changing F2 from 20 to 25
for example.



if exists (select * from dbo.sysobjects where id =
object_id(N'dbo.prob1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.prob1
GO

CREATE TABLE dbo.prob1 (
f1 char (10) NOT NULL ,
f2 char (20) NULL ,
f3 char (30) NULL
) ON PRIMARY
GO

ALTER TABLE dbo.prob1 ADD
CONSTRAINT PK_prob1 PRIMARY KEY CLUSTERED
(
f1
) ON PRIMARY
GO

.

Relevant Pages

  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Connectivity
    ... SQL Server 2005. ... primary key set up in your linked tables. ... Well, even if you deleted the records, it won't change the database size ...
    (comp.databases.ms-access)
  • Re: how to create primary keys on compound fields?
    ... In the table design high-light each column name you want to make PK and ... ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1, ... > I've successfully imported DBF files into SQL server but I can't ...
    (microsoft.public.sqlserver.msde)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)