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

Hi,

Just a small thought..

As you have applied SP4 to your SQL Servers, have you applied SP4 to all of
the development machines where the client tools (EM, QA) are installed, too?

Robert


<stephanie.harrell@stateauto.com> wrote in message
news:1142271519.219796.202000@i40g2000cwc.googlegroups.com...
> 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
>

Yes, all servers have SP4.   As for client tools, I would say the
majority are.  There may be a couple older machines that are not
current, but have not heard from any of those people - if there are any.

(stephanie.harrell@stateauto.com)  writes:
> 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.

To be very blunt, if they can't write change scripts with ALTER TABLE,
they should not be changing tables at all. Performing changes to tables
is an advanced operation, and requires careful understanding, particularly
if you are to do it in a production environment.

The Design Table function in EM has several serious flaws (and all these
flaws are carried over to SQL 2005). I include a list below, from a post
that I made just the other day.

I was not able to reproduce the problem with the table you posted. But
you could get an idea of what's going by requesting getting a change
script from EM. (Which you should do anyway, because of all the bugs
in the Table Designer.) You find this function on the 3rd button from
the left in the toolbar.


Here is a list of know defenciencies with the Table Designer:

1) The transaction scope is wacko. What should be one transaction is
split up into several, which can lead to a table change being only
partly implemented, and your database becomes a mess. Or you just lose
constraints, without knowing it.

2) In the generated script, the transaction spans multiple batches. This
means that if one batch fails with a batch-aborting error, the
transaction is rolled back. The remaining batches in the script are
still carried out which is not likely what you want. (This flaw does
not appear if you save directly, as the Table Designer does not continue
to submit batches if there is an error.)

3) The table designer essentially behaves as there has been no later
version of SQL Server. That is, in many cases where it could use
ALTER TABLE, it instead runs a script where it creates a new table
and copies data over.

4) Say that you have to tables A and B. B has an FK to A. You first make a
change to B, and generate a script. Then you change your mind, and
close without saving anything. Instead you make a change A. When you
generate script (or even worse just press Save), you find that the
change to B is included.

5) All constraints that are reapplied, are reapplied WITH NOCHECK,
which means that they are not trusted as far as the optimizer is
concerned. This have serious impact on performance, not the least
with partitioned views.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thank you for your reply and you are certainly entitled to your opinion
on who should change tables.  Our set up works for us. We support a
large audience of developers at varying levels of expertise and this is
the first time we've encountered any problems with the tool.  I will
pass along your suggestion on generating the change script to our
developers.

(stephanie.harrell@stateauto.com)  writes:
> Thank you for your reply and you are certainly entitled to your opinion
> on who should change tables.  Our set up works for us. We support a
> large audience of developers at varying levels of expertise and this is
> the first time we've encountered any problems with the tool.

That you know of. Some of the problems that I pointed out are of the
kind that you may not notice directly when they cause problems. If you
lose an FK constraint, you may not notice until years later when you find
some junk in the column.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I thought I would let you know that I found the problem.   The
XACT_ABORT option had been turned on server-wide.   The new tools with
SQL 2005 allow you to turn that option off/on in the server properties
and someone had turned it on.  Once I turned it back off, Enterprise
Manager worked like a champ again.  Guess this is why we have
development servers - to learn lessons the hard way.

Thanks all!



Erland Sommarskog  wrote:
> (stephanie.harrell@stateauto.com) writes: 
>
> That you know of. Some of the problems that I pointed out are of the
> kind that you may not notice directly when they cause problems. If you
> lose an FK constraint, you may not notice until years later when you find
> some junk in the column.
>
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx