Why this error ?

Question

Why this error ?

Sql Junkie-204042

Hall of Fame

Points: 3874
More actions
August 1, 2005 at 8:01 am

#68235

I am creating a table (tableA) from an existing table (tableB) and then add a new column to tableA. When I first run the script tableA does not exists. After I run the script tableA is created and then I get this error
Invalid column name 'DateDataEntered'.
Here is the script
-- Check if table exists
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[tableA]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
-- Create blank table
  SELECT * INTO tableA FROM tableB
   WHERE 1 = 2
-- Add new column
  ALTER TABLE tableA Add DateDataEntered smalldatetime
    CONSTRAINT tableA_AddDefltDate DEFAULT getdate()
END
ELSE
BEGIN
-- Table exists. Cleanup table
  DELETE FROM tableA
   WHERE DateDataEntered < DATEADD(mi,10,getdate())
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246971 More actions · Answer 1

If you replace your SELECT * INTO and ALTER TABLE statements with a single CREATE TABLE statement containing the new column and constraint, your problem will go away. But why? It seems that the WHERE clause of your DELETE statement is parsed just before the ALTER TABLE statement is executed and at that stage the column is invalid and the error message is displayed.

Maybe someone else here will know why - it seems very strange to me.

Regards.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

This script is executed in DTS and the log file shows this error randomly. It does not generate this error every time. Strange !

Phil Parkin SSC Guru Points: 246971 More actions · Answer 3

Not tried it, but I think that the error would not appear if the script were run when TableA existed and had the extra field.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

noeld SSC Guru Points: 96590 More actions · Answer 4

The fact that tableA does not exists when the delete statement is parsed is what generates the error.

Either

1. Use a normal Table and truncate it when needed

2. Use dynamic SQL to execute the Delete

* Noel

Phil Parkin SSC Guru Points: 246971 More actions · Answer 5

Why does it matter whether it exists, if the DELETE block is not going to be executed?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

noeld SSC Guru Points: 96590 More actions · Answer 6

>>Why does it matter whether it exists, if the DELETE block is not going to be executed? <<

This has to do with the way SQL works. It has to create an execution plan before it does anything. How can the plan be created if the table/Column does not exists ?

* Noel

Phil Parkin SSC Guru Points: 246971 More actions · Answer 7

I suspected that you would say something like that ... but then why does the CREATE TABLE solution work (just tested it)?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

noeld SSC Guru Points: 96590 More actions · Answer 8

CREATE TABLE is a physical operation ( check the execution plan ) -- It will abort batch if one already exists

To compile "Delete" it has to know column type and availability of indexes, statistics, rows etc

* Noel