Post reply

Why this error ?

  • 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

  • August 1, 2005 at 8:45 am

    #578551

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • August 1, 2005 at 8:47 am

    #578552

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

  • August 1, 2005 at 8:53 am

    #578557

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • August 1, 2005 at 9:55 am

    #578588

    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

  • August 1, 2005 at 10:39 am

    #578619

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • August 1, 2005 at 11:00 am

    #578623

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

  • August 1, 2005 at 11:14 am

    #578627

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • August 1, 2005 at 11:20 am

    #578630

    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

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

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