title: Stored procedure execution in sql server agent job
body: <p>I would like to execute a stored procedure which populates data to table using nightly job.</p>
  
  <pre><code>sp_WarehouseAttendance_New:
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE PROCEDURE [dbo].[sp_WarehouseAttendance_New]
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  ---------delete existing warehouse table------------------------------
  IF OBJECT_ID('dbo.WarehouseAttendance_New', 'U') IS NOT NULL
    DROP TABLE dbo.WarehouseAttendance_New; 
  --------Create New Table--------------------------------------------------
  ---Weeks------------------------------------------------------------------
  With weeks as 
  (select VDC_week_no, VDC_cal_year, min(VDC_day_date) as WeekCommence
  from sql10.ng.dbo.Vdaily_calender
  where VDC_avail = 'T'
  group by VDC_week_no, VDC_cal_year),
  
  ---TTWeeks----------------------------------------------------------------
  TTWeeks as
  
  (SELECT VDC_Cal_Year as REMSYear, [VDC_week_no] as WholeYearWeekNo, WeekCommence,ROW_NUMBER() OVER(PARTITION BY vdc_cal_year ORDER BY VDC_week_no) as TermTimeWeekNo FROM weeks)
  
  ---Main----------------------------------------------------------------------
  SELECT    CASE WHEN STYR_Age_end_Aug &lt; 16 THEN '1416' WHEN STYR_Age_end_Aug &lt; 19 THEN '1618' ELSE '19+' END AgeBand,
  REGT_Year, RTRIM(PRPH_ML1) AS PRPH_ML1, 
  RTRIM(PRPH_ML2) AS PRPH_ML2, 
  ML2.GNCD_Description AS [Curriculum Area], 
  RTrim(PRPH_ML2) AS Section, 
  q.GNCD_Description AS Section_Name, LEFT(q.GNCD_Description, 3) AS Dept, 
  rtrim(REGT_Provision_Code) as REGT_Provision_Code, rtrim(PRPH_Title) as PRPH_Title, 
  REGT_Student_ID, STEN_Student_ID, STEN_Funding_Stream, STYR_Age_end_Aug, REGS_Session_No, 
  rtrim(REGH_Class_Register) as REGH_Class_Register, RTRIM(REGH_Register_Title) as REGH_Register_Title, REGH_Day, 
  CASE WHEN REGH_Day = '1' THEN 'Sunday' WHEN REGH_Day = '2' THEN 'Monday' 
  WHEN REGH_Day = '3' THEN 'Tuesday' WHEN REGH_Day = '4' THEN 'Wednesday'
  WHEN REGH_Day = '5' THEN 'Thursday' WHEN REGH_Day = '6' THEN 'Friday' 
  WHEN REGH_Day = '7' THEN 'Saturday' END AS Register_Day, [REGH_Start_Time] ,[REGH_End_Time],
  CASE WHEN (REGTrgstudt.REGT_Provision_Code LIKE '27%' OR
  REGT_Provision_Code LIKE 'MA27%' OR REGT_Provision_Code LIKE 'FS%') 
  THEN 'FunctionalSkill' 
  WHEN REGT_Provision_Code LIKE '16%' THEN 'GCSE' WHEN REGT_Provision_Code LIKE '%/F%' OR REGT_Provision_Code LIKE '%/D%' OR
  REGT_Provision_Code LIKE '%/E%' OR
  REGT_Provision_Code LIKE '%/X%' THEN 'Main' ELSE 'Addition' END AS CourseType, 
  CASE WHEN isnull(RGAT_Present,'X') IN ('N', 'Y','X') and REGS_Session_Date&lt;GETDATE()
  THEN REGS_Duration - isnull(REGD_Mins_Late,0) ELSE 0 END AS Mins_Poss, 
  CASE WHEN isnull(RGAT_Present,'X') = 'Y' and REGS_Session_Date&lt;GETDATE() THEN 
  REGS_Duration - REGD_Mins_Late ELSE 0 END AS Mins_Att, 
  CASE WHEN RGAT_Present = 'Y' and REGS_Session_Date&lt;GETDATE() THEN REGD_Mins_Late ELSE 0 END AS Mins_Late, 
  [REGS_Session_Date] as Session_Date,
  TermTimeWeekNo, REGH_ISN, PRPH_ISN, STUD_Surname, 
  STUD_Forename_1, REGD_Attendance_Mark,
  REGT_start_date, REGT_End_date, WeekCommence,
  STFM_LearnFAMCode,
  CASE 
  WHEN STFM_LearnFAMCode=1 THEN '14-15 year old learner is eligible for free meals'
  WHEN STFM_LearnFAMCode=2 THEN '16-19 year old learner is eligible for and in receipt of free meals'
  ELSE 'N/A'
  END AS FreeMealsIndicator,
  CASE 
  STFM_LearnFAMCode=36 THEN 'Care to Learn (C2L) (EFA funded only)'
  STFM_LearnFAMCode=55 THEN '16-19 Bursary Fund - learner member of a vulnerable group (EFA funded only)' 
  WHEN STFM_LearnFAMCode=56 THEN '16-19 Bursary Fund - learner awarded discretionary bursary (EFA funded only)'   
  WHEN STFM_LearnFAMCode=57 THEN 'Residential support (EFA funded only)'
  WHEN STFM_LearnFAMCode=58 THEN '19+ Hardship (Skills Funding Agency funded learners only)'  
  WHEN STFM_LearnFAMCode=59 THEN '20+ Childcare (Skills Funding Agency funded learners only)' 
  WHEN STFM_LearnFAMCode=60 THEN 'Residential Access Fund (Skills Funding Agency funded learners only)'   
  WHEN STFM_LearnFAMCode IN (61, 62, 63, 64, 65) THEN 'Unassigned'    
  ELSE 'N/A'
  END AS Bursary, CASE WHEN STUD_Gender='M' THEN 'Male'
  WHEN STUD_Gender='F' THEN 'Female'
  END AS Gender, 
  CASE 
  WHEN STUD_Ethnicity= 31 THEN 'White - English / Welsh / Scottish / Northern Irish / British'
  WHEN  STUD_Ethnicity= 32 THEN 'White - Irish'
  WHEN  STUD_Ethnicity= 33 THEN 'White - Gypsy or Irish Traveller'
  WHEN  STUD_Ethnicity= 34 THEN 'White - Any Other White background'
  WHEN  STUD_Ethnicity= 35 THEN 'Mixed / Multiple Ethnic group - White and Black Caribbean'
  WHEN  STUD_Ethnicity= 36 THEN 'Mixed / Multiple Ethnic group - White and Black African'
  WHEN  STUD_Ethnicity= 37 THEN 'Mixed / Multiple Ethnic group - White and Asian'
  WHEN  STUD_Ethnicity= 38 THEN 'Mixed / Multiple Ethnic group - Any Other Mixed / multiple ethnic background'
  WHEN  STUD_Ethnicity= 39 THEN 'Asian / Asian British - Indian'
  WHEN  STUD_Ethnicity= 40 THEN 'Asian/ Asian British - Pakistani'
  WHEN  STUD_Ethnicity= 41 THEN 'Asian / Asian British - Bangladeshi'
  WHEN  STUD_Ethnicity= 42 THEN 'Asian / Asian British - Chinese'
  WHEN  STUD_Ethnicity= 43 THEN 'Asian / Asian British - Any other Asian background'
  WHEN  STUD_Ethnicity= 44 THEN 'Black / African / Caribbean / Black British - African'
  WHEN  STUD_Ethnicity= 45 THEN 'Black / African / Caribbean / Black British - Caribbean'
  WHEN  STUD_Ethnicity= 46 THEN 'Black / African / Caribbean / Black British - Any other Black / African / Caribbean background'
  WHEN  STUD_Ethnicity= 47 THEN 'Other ethnic group - Arab'
  WHEN  STUD_Ethnicity= 98 THEN 'Any Other'
  WHEN  STUD_Ethnicity= 99 THEN 'Not provided'
  END AS Ethnicity,
  CASE 
  WHEN g.GNUC_Flag_1 = 1 THEN 'Yes'
  WHEN g.GNUC_Flag_1 = 0 THEN 'No'
  END AS [Looked After Child],
  CASE 
  WHEN SUB1.GNUC_Type = 'STYR' THEN 'Yes'
  WHEN SUB1.GNUC_Type != 'STYR' THEN 'No'
  END AS ALS
  INTO WarehouseAttendance_New                    
  FROM        
  sql10.ng.dbo.REGTrgstudt 
  INNER JOIN
  sql10.ng.dbo.REGSrgsessn ON REGT_REGH_ISN = REGS_REGH_ISN  
  inner join sql10.ng.dbo.Vdaily_calender
  ON REGS_Session_Date=VDC_day_date      
  INNER JOIN TTWeeks on REGT_year=TTWeeks.REMSYear
  and VDC_week_no=WholeYearWeekNo and VDC_cal_year=REMSYear
  INNER JOIN
  sql10.ng.dbo.REGHrghdr ON REGH_ISN = REGT_REGH_ISN 
  INNER JOIN
  sql10.ng.dbo.PRPHProvisionHeader ON REGT_Provision_Code = PRPH_Code 
  INNER JOIN
  sql10.ng.dbo.GNCDgncodes AS ML2 ON ML2.GNCD_General_Code = PRPH_ML2 AND ML2.GNCD_Code_Type = 'M2' 
  INNER JOIN
  sql10.ng.dbo.STEN ON STEN_Student_ID = REGTrgstudt.REGT_Student_ID AND STEN_Provision_Code = REGTrgstudt.REGT_Provision_Code AND
  STEN_Provision_Instance = REGT_Provision_Instance 
  LEFT OUTER JOIN  sql10.ng.dbo.STFMLearnerFAM ON STFM_Year = STEN_Year AND STFM_Student_ID = STEN_Student_ID 
  INNER JOIN
  sql10.ng.dbo.STYRstudentYR ON STYR_Student_ID = REGT_Student_ID AND STYR_Year = REGT_Year   
  INNER JOIN (SELECT GNUC_Type, GNUC_Year, GNUC_Entity_ISN FROM sql10.ng.dbo.GNUCustom 
  WHERE (GNUC_Year IN ('2013', '2014', '2015'))) AS SUB1 ON REGT_Year = SUB1.GNUC_Year 
  AND STYR_ISN = SUB1.GNUC_Entity_ISN
  INNER JOIN
  (SELECT     ACYR_College_Year
  FROM          sql10.ng.dbo.ACYR
  WHERE      (ACYR_College_Year in (2015, 2014, 2013, 2012))) AS SUB ON REGT_Year = SUB.ACYR_College_Year
  INNER JOIN sql10.ng.dbo.STUDStudent on STYR_Student_ID=STUD_Student_ID
  JOIN sql10.ng.dbo.GNUCustom g ON STUD_ISN = g.GNUC_Entity_ISN AND g.GNUC_Type = 'STUD'       
  INNER JOIN sql10.ng.dbo.REGDropin ON REGT_REGH_ISN = REGD_REGH_ISN AND REGT_Student_ID = REGD_Student_ID 
  AND 
  REGD_Session_No = REGS_Session_No
  INNER JOIN
  sql10.ng.dbo.RGATAttendance ON REGD_Attendance_Mark = RGAT_Attendance_Code 
  INNER JOIN
  sql10.ng.dbo.PRPIProvisionInstance AS pit ON pit.PRPI_Code = REGT_Provision_Code 
  and pit.prpi_instance = REGT_Provision_Instance
  INNER JOIN
  (SELECT     GNCD_General_Code, GNCD_Description
  FROM          sql10.ng.dbo.GNCDgncodes
  WHERE      (GNCD_Code_Type = 'M2')) AS q ON q.GNCD_General_Code =  RTrim(PRPH_ML2) 
  END
  GO
  </code></pre>
  
  <p>I want to make sure I am using correct SQL in SQL Server Agent -> Steps</p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[sp_WarehouseAttendance_New]
  
  SELECT  'Return Value' = @return_value
  </code></pre>
  
  <p>I would like to know if above query deletes existing data in the table or just appends to existing data which should not be the case.</p>
  
  <p>Also, whenever I execute this procedure either using sql job or in query, it throws Lock timeout request error as below.</p>
  
  <p><a href="https://i.stack.imgur.com/S9VGO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/S9VGO.png" alt="Lock Timeout error"></a></p>
  
  <p>Kindly let me know how this can be resolved.</p>
  
  <p>Thanks,
  Ar</p>
  
  <p>Execution Plan below(Source Server - SQL10.NG):</p>
  
  <p><a href="https://i.stack.imgur.com/iG42g.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iG42g.png" alt="Plan1"></a></p>
  
  <p><a href="https://i.stack.imgur.com/vTuSk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vTuSk.png" alt="Plan2"></a></p>
  
  <p><a href="https://i.stack.imgur.com/vMYLj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vMYLj.png" alt="Plan3"></a></p>
  
  <p><a href="https://i.stack.imgur.com/AHzCc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AHzCc.png" alt="Plan 4"></a></p>
  
  <p><a href="https://i.stack.imgur.com/jnT8b.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jnT8b.png" alt="Plan 5"></a></p>
  
  <p>Client Statistics:</p>
  
  <p><a href="https://i.stack.imgur.com/fGAOz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fGAOz.png" alt="Client Stats"></a></p>