Lock timeout request period exceeded error while trying to execute stored procedures

  • 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