Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Giving "Invalid column Name " error Expand / Collapse
Author
Message
Posted Sunday, September 07, 2008 10:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917

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
Post #565209
Posted Monday, September 08, 2008 2:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,777, Visits: 12,883
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 »

Add to briefcase

Permissions Expand / Collapse