SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Giving "Invalid column Name " error


Giving "Invalid column Name " error

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 4076
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;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16150 Visits: 19540
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search