Giving "Invalid column Name " error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Giving "Invalid column Name " error

Giving "Invalid column Name " error

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Sunday, September 07, 2008 10:43 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #565209

ChrisM@Work

Posted Monday, September 08, 2008 2:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

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:

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'

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
Exploring Recursive CTEs by Example Dwain Camps

Post #565261

« Prev Topic | Next Topic »

Permissions