Giving "Invalid column Name " error

  • CREATE PROCEDURE CsatDataPull_SP_DP

    AS

    IF EXISTS (SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = 'AllRequiredData_newCSAT')

    DROP TABLE dbarea.dbo.AllRequiredData_newCSAT

    --step 1

    select

    r.responseid as 'responseid_new',r.respid as 'respid_new',interview_start,interview_end,status,oClientServRep,TLName,[name],companyName,projectType,clientServRep,

    clientEmail,mailpd,TLemail,q4Service_1,q4Service_8,q4Service_9,q4Service_2,q4Service_3,q4Service_4,q4Service_6,q4Service_7,

    q1_1,q3_1,q3_2,q3_3,q8_1,q5_1,q6_1,q7,urlvar,projectName,projectcompday,projectcompmonth,projectcompyear,oProjectDate,

    oClientDevRep,projectvalue_1,team,GMName,GMemail,teamemail,csatsent,notsendinvite,surveyresult,TeamLeader,topclient,

    cWeek,allfive,q4Service_5,q4Service_10,q4Service_11HAP,q4Service_12HAP,q4Service_13HAP,q4Service_14HAP,q4Service_15HAP,

    q4Service_16HAP,q4Service_17HAP,pdlowcount,RankPd_1,projecttypeattributes,postivecount,Ptagiven,FIArespid,positivecount_1,

    avgscore_1,projectgroup

    into dbarea.dbo.AllRequiredData_newCSAT

    from CF9SQLa.survey_p19268007.dbo.response0 r ,

    CF9SQLa.survey_p19268007.dbo.response_control rc,

    CF9SQLa.survey_p19268007.dbo.response1 r1

    where r.respid=rc.respid

    AND R1.RESPID=R.RESPID

    and status='complete'

    --step 2

    begin tran

    alter table dbarea.dbo.AllRequiredData_newCSAT

    add responseid int identity,

    respid int,

    sp_score smallint,

    dp_score smallint

    commit

    ---step 3

    begin tran

    update dbarea.dbo.AllRequiredData_newCSAT

    set respid=responseid

    from dbarea.dbo.AllRequiredData_newCSAT

    commit

    ------------------------------------------------------------------------

    Above is my sp script , i m sorry i cant give you table schema or data

    But

    Problem is that :

    step 3 is get executed BEFORE step 2 ...which results in error ...

    Can this happen becusee of large amount amount data is inserted into step 2 ?

    Please Help me

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh,

    SQL Server detects that the columns are missing before running the code, but doesn't see that the new columns are added to the table in step 2.

    However, you don't need step 2:

    [font="Courier New"]SELECT  IDENTITY (INT, 1, 1) AS responseid,

       CAST(NULL AS INT) AS respid,

       CAST(NULL AS smallint) AS sp_score,

       CAST(NULL AS smallint) AS dp_score,          

       r.responseid AS 'responseid_new',r.respid AS 'respid_new',interview_start,interview_end,status,oClientServRep,TLName,[name],companyName,projectType,clientServRep,          

       clientEmail,mailpd,TLemail,q4Service_1,q4Service_8,q4Service_9,q4Service_2,q4Service_3,q4Service_4,q4Service_6,q4Service_7,          

       q1_1,q3_1,q3_2,q3_3,q8_1,q5_1,q6_1,q7,urlvar,projectName,projectcompday,projectcompmonth,projectcompyear,oProjectDate,          

       oClientDevRep,projectvalue_1,team,GMName,GMemail,teamemail,csatsent,notsendinvite,surveyresult,TeamLeader,topclient,          

       cWeek,allfive,q4Service_5,q4Service_10,q4Service_11HAP,q4Service_12HAP,q4Service_13HAP,q4Service_14HAP,q4Service_15HAP,          

       q4Service_16HAP,q4Service_17HAP,pdlowcount,RankPd_1,projecttypeattributes,postivecount,Ptagiven,FIArespid,positivecount_1,          

       avgscore_1,projectgroup          

    INTO dbarea.dbo.AllRequiredData_newCSAT                  

    FROM CF9SQLa.survey_p19268007.dbo.response0 r ,          

       CF9SQLa.survey_p19268007.dbo.response_control rc,          

       CF9SQLa.survey_p19268007.dbo.response1 r1                  

    WHERE r.respid=rc.respid                  

       AND R1.RESPID=R.RESPID            

       AND status='complete'  

    [/font]

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

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