Re: alter table query not working after working at least once. MS SQL 2000

On Jun 23, 11:00 pm, quincy...@xxxxxxxxx wrote:
On Jun 23, 4:47 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:



 (quincy...@xxxxxxxxx) writes:
Table already exists.
[Microsoft][ODBC SQL Server Driver][SQL Server]There is already an
object named 'PK_begin_call_temp' in the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.

This works the first time then after some period of time gets into a
state where this error is all I get...makes no sense to me.  The
server is MS SQL 2000.

I was not able to reproduce the problem. But as it complains on the
primary key only, and not the table, I would guess that the problem
is that you have another table with the same name for the PK.

Run

   SELECT object_name(parent_obj) FROM sysobjects
   WHERE name = 'PK_begin_call_temp'

to find out.

Ok I think we are on to something here...not sure what...I get back
one cell with begin_call in it for this query.
The application when it works does the following:
We have a table begin_call we want to modify it in some arbitrary
way.  So we do this.
We create begin_call_temp with the structure we want.
Then we transfer existing data from begin_call to begin_call_temp
using inerrt into.
Then we drop begin_call. .
Then we do a sp_rename begin_call_temp begin_call.

This entire sequence has worked at least once.  Then we manually
remove the added fields to begin_call with server management studio
and try again, and it fails.  Most likely something left over from my
choice of how to do the rename.  But not sure what exactly.

Please enlgihten me...if this helps your understanding of what might
be happening.
Thanks,
David





--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

sure enough I manually delete begin_call and begin_call_temp and it
works again the first time. How do I avoid the conflict the second
time?

Thanks
David
.

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: Need urgent help in Database update choice
    ... > update the device db data from a central sql server 2000 db. ... > will be the same for all users(either windows or sql server authentication ... > first time, how to just update the updated data of the server and not the ...
    (microsoft.public.sqlserver.ce)
  • Re: Server no longer on list
    ... Probably the Business Contact Manager that came with Office 2003, ... loads up in first time you use Outlook and you can then cancel it. ... PCs appear as a server. ... If you had a large domain with hundreds of SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: alter table query not working after working at least once. MS SQL 2000
    ... constraint. ... This works the first time then after some period of time gets into a ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
Loading