March 30, 2016 at 2:19 am
Hi All,
I am trying to execute stored procedure which populates data into the table. It was working fine for few days and not it just throws
Lock timeout request period exceeded error 1222 every morning when I try to open Tables and Views in the database.
Please let me know how to avoid this:
CREATE PROCEDURE [dbo].[sp_WarehouseAttendance_New]
AS
BEGIN
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 < 16 THEN '1416' WHEN STYR_Age_end_Aug < 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<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<GETDATE() THEN
REGS_Duration - REGD_Mins_Late ELSE 0 END AS Mins_Att,
CASE WHEN RGAT_Present = 'Y' and REGS_Session_Date<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
WHEN STFM_LearnFAMCode=36THEN 'Care to Learn (C2L) (EFA funded only)'
WHEN STFM_LearnFAMCode=55THEN '16-19 Bursary Fund - learner member of a vulnerable group (EFA funded only)'
WHEN STFM_LearnFAMCode=56THEN '16-19 Bursary Fund - learner awarded discretionary bursary (EFA funded only)'
WHEN STFM_LearnFAMCode=57THEN 'Residential support (EFA funded only)'
WHEN STFM_LearnFAMCode=58THEN '19+ Hardship (Skills Funding Agency funded learners only)'
WHEN STFM_LearnFAMCode=59THEN '20+ Childcare (Skills Funding Agency funded learners only)'
WHEN STFM_LearnFAMCode=60THEN '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
INNER 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 JOINsql10.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
SQL Server Agent -> Steps->
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_WarehouseAttendance_New]
SELECT'Return Value' = @return_value
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply