alter table query not working after working at least once. MS SQL2000

[quincy451]

drop table [dbo].[begin_call_temp]
CREATE TABLE [dbo].[begin_call_temp] (
[Event_Datetime] [datetime] NULL ,
[Machine_ID] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Line] [smallint] NULL ,
[Outbound_Line] [smallint] NULL ,
[Call_Number] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Outbound_Call_Number] [varchar](16) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dc_dialstr] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ANI] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNIS] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANI_Name] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Call_Begin_Datetime] [datetime] NULL ,
[identity] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[begin_call_temp] WITH NOCHECK ADD
CONSTRAINT [PK_begin_call_temp] PRIMARY KEY CLUSTERED
([identity]) ON [PRIMARY]

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.

Thanks
David

[Erland Sommarskog]

(qu*******@yahoo.com) 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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[quincy451]

On Jun 23, 4:47*pm, Erland Sommarskog <[e-mail]:

*([e-mail]) 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, [e-mail]

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

[quincy451]

On Jun 23, 11:00*pm, [e-mail] wrote:

On Jun 23, 4:47*pm, Erland Sommarskog <[e-mail]:

*([e-mail]) 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, [e-mail]

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

[Andrew Morton]

quincy451@ wrote:

drop table [dbo].[begin_call_temp]
CREATE TABLE [dbo].[begin_call_temp] (

>
Table already exists.
[Microsoft][ODBC SQL Server Driver][SQL Server]There is already an
object named 'PK_begin_call_temp' in the database.

As far as I've found, the "drop table" doesn't happen until it encounters a
"go" statement. I could of course be completely wrong and there could be
some "drop table NOW" command.

Andrew

[Hugo Kornelis]

On Mon, 23 Jun 2008 21:00:57 -0700 (PDT), qu*******@yahoo.com wrote:

(snip)

>Please enlgihten me...if this helps your understanding of what might
be happening.

Hi David,

This is likely a result of how SQL Server checks correctness of names
when parsing your batches. For table and column names, there are
basically two possibilities:

1. The table exists at the time of parsing. In that case, SQL Server
will go on to check if all referenced columns exist. If they don't, you
get an error message.

2. The table does not exist at the time of parsing. In that case, SQL
Server assumes that it will somehow be created before the parsed command
will actually be executed, so no further checking takes place. This is
called deferred name resolution, as you defer the resolution of column
names from parse time to run time.

The first time you executed the batch, the table didn't exist yet so
deferred name resolution was used. By the time the "prblem" query was
executed, the table had been created and then altered to add the extra
column.

The second execution, the table did exist, so SQL Server wanted to check
the column names - and now you get an error because at parse time, the
ALTER TABLE has not yet been executed so the column is missing.

The best way to solve this is to cut up your SQL script in several
batches by interspersing it with batch seperators (GO) at well-chosen
locations. Parsing is done per batch, so if you make sure that the
"problem" query is in a batch that will be submitted after the batch
containing the ALTER TABLE, you'll have no problems - this batch will
now be parsed after adding the column to the table so you won't get any
errors.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

(qu*******@yahoo.com) writes:

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.

Yes, it works the first time. It does not work the second time, because
apparently you forgot to rename the primary key when you renamed the
table.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[quincy451]

On Jun 24, 3:55*pm, Erland Sommarskog <[e-mail]:

*([e-mail]) writes:

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. *

Yes, it works the first time. It does not work the second time, because
apparently you forgot to rename the primary key when you renamed the
table.

Yes I added
EXEC sp_rename 'begin_call_temp.PK_begin_call_temp',
'PK_begin_call','index';
Before
EXEC sp_rename 'begin_call_temp','begin_call';

And it all seems to work.
Thank you,
David

>
--
Erland Sommarskog, SQL Server MVP, [e-mail]

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