June 7, 2005 at 11:14 am
I have a database that appears to have haning stored procedures whenever I change a nested stored procedure. The first time I saw this was a few months ago where one stored procedure was hanging whenever I changed a specific stored procedure called by it. I found if I called the nested stored procedure from query analyzer the problem would go away. Since the problem was in only one place and I'm not officially the DBA I figured I could live with it.
The problem is it's shown up in another place and even with my fix I can't get a DTS script to do anything but hang. The new problem is an SQL script that calls three different stored procedures, of which two changed recently. If I execute the three stored procedures spearately, I can get the script to run in query analyzer. However, if after this I try running it in a DTS package it still fails. Also:
if anything hangs then all of the sub-stored procedures have to be individually reexecuted
the DTS script begins with a 'USE
the database was originally designed in SQL 6.5, it was set to run at level 80 just a couple of weeks ago
Help!
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 11:25 am
Have you tried recompiling the procs right after you modified 'em (making sure that the old plan gets dropped).
June 7, 2005 at 1:48 pm
Thanks for the response.
Although I did add a 'WITH RECOMPILE' line to the two changed stored procedures, I tested the possibility by deleting and then recreating all three of the stored procedures (I'm at the overkill stage) and confirmed that the primary script worked in Query Analyzer, the DTS package still hung. I went through and got all three stored procedures and the scrpit working and recreated the DTS package (fortunately it's a new one) and it still hung at the DTS stage.
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 1:50 pm
Strange... have you see any deadlocks while it hung?
June 7, 2005 at 2:14 pm
Hello. Assuming I understand deadlocks correctly, a deadlock message did not print out and there's nothing in the log.
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 2:19 pm
Sorry for the confusion, I was talking of locks that could be holding back the whole process.
Can you try to recreate the situation and run the proc sp_locks and sp_who2 while it hangs?
June 7, 2005 at 2:28 pm
Hello. I ran the two sprocs while it was haning but I don't know what to look for. The sp_who2 is pretty short:
53 RUNNABLE OCP\ewilsonEWILSON . DiversityINSERT52142243006406/07 13:10:38SQL Query Analyzer53
but the sp_lock is a good 95 lines.
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 2:33 pm
In sp_who2, scan the column BlkBy, anything different than "-" will mean that this process is blocked by the spid in that column.
You can see what that process is running by using this command :
DBCC InputBuffer (spid)
June 7, 2005 at 2:39 pm
OK, the BlkBy column is clear (-) and DBCC InputBuffer is returning the initial script. I ran another script during the problem one just to make sure something was there to be blocked.
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 2:46 pm
Well I'm out of ideas... maybe somebody else will figure it out.
Maybe just for luck, can you post the code of the stored procs?
June 7, 2005 at 2:57 pm
Well, OK. I'll post the three sprocs over three posts. Note that there are a few temporary tables, this is a situation where legibility is more important than speed (that's another story).
CREATE PROCEDURE sprSnapshotPriDepartment
AS
-- set current date variable to midnight, since snapshot need to include Distributions that end on snapshot date
DECLARE@CurrentDatedatetime
Set @CurrentDate = AcadPers.dbo.fnDateSetToMidnight(GETDATE())
CREATE TABLE #HmeDeptPercentages (EMPLOYEE_ID char(9), HME_DEPT_NO char(6), Percentage float)
INSERT INTO #HmeDeptPercentages
SELECT DistDetail.EMPLOYEE_ID, HME_DEPT_NO, SUM(DIST_PERCENT) AS Percentage
FROM
(SELECT DISTINCT tblSG2DIS.EMPLOYEE_ID, DIST_NUM, HME_DEPT_NO,
-- need to account for DOS codes that had a percentage but shouldn't have
-- on 01/11/05 these were the DOS codes BYA and WOS; primary problem was with BYA codes
CASE WHEN ISNULL(tblPPSAuditDOSattributes.ExcludeTotalPercent,0) = 1
THEN 0 ELSE DIST_PERCENT END AS DIST_PERCENT
FROM AcadPers.dbo.tblSG2DIS AS tblSG2DIS
LEFT JOIN AcadPers.dbo.tblPPSAuditDOSattributes AS tblPPSAuditDOSattributes
ON tblSG2DIS.DIST_DOS = tblPPSAuditDOSattributes.Code
-- remove distributions not in total dist %
-- exception made for codes that may appear alone (WOS, BYA)
LEFT JOIN diversity.dbo.qrySnapshotWorkStudyOnly AS qrySnapshotWorkStudyOnly
ON tblSG2DIS.EMPLOYEE_ID = qrySnapshotWorkStudyOnly.EMPLOYEE_ID
INNER JOIN AcadPers.dbo.tblCTLHME AS tblCTLHME
ON tblSG2DIS.DIST_DEPT_CODE = tblCTLHME.HME_DEPT_NO
WHERE ISNULL(tblPPSAuditDOSattributes.ExcludeTotalPercentWF,0) 1
AND PAY_BEGIN_DATE = @CurrentDate
AND (tblSG2DIS.WORK_STUDY_PGM IS NULL OR
-- exception if only Dist is work study (tech. an error)
NOT(qrySnapshotWorkStudyOnly.EMPLOYEE_ID IS NULL))
AND NOT(ISNULL(tblSG2DIS.UCD_ADC_CODE,'') IN('D')))
AS DistDetail
GROUP BY DistDetail.EMPLOYEE_ID, HME_DEPT_NO
SELECT DISTINCT PriHmeDeptNo.EMPLOYEE_ID, PriHmeDeptNo.HME_DEPT_NO,
HME_ABRV_DEPT_NAME, HME_DEPT_NAME, SCH_CODE,
SCH_ABBRV, SCH_SHORT_DESC, SCH_LONG_DESC
FROM
-- if more than one home dept code with greatest percentage and neither equal to the
-- Primary Home Department code then tie breaker is home department code with lowest number
(SELECT PrimaryDepartmentPercentage.EMPLOYEE_ID, MIN(#HmeDeptPercentages.HME_DEPT_NO) AS HME_DEPT_NO
FROM
-- select home department with the highest percentage (first test: home dept with the most percentage)
(SELECT #HmeDeptPercentages.EMPLOYEE_ID, MAX(Percentage) AS MaxPercentage
FROM #HmeDeptPercentages
GROUP BY EMPLOYEE_ID)
AS PrimaryDepartmentPercentage
INNER JOIN #HmeDeptPercentages
ON PrimaryDepartmentPercentage.EMPLOYEE_ID = #HmeDeptPercentages.EMPLOYEE_ID
AND PrimaryDepartmentPercentage.MaxPercentage = #HmeDeptPercentages.Percentage
LEFT JOIN
-- find situations where more than one possible max percentage but one is equal to Primary Title
(SELECT #HmeDeptPercentages.EMPLOYEE_ID, #HmeDeptPercentages.HME_DEPT_NO
FROM
-- select title code with the max percentage
(SELECT EMPLOYEE_ID, MAX(Percentage) AS MaxPer
FROM #HmeDeptPercentages
GROUP BY EMPLOYEE_ID)
AS MaxPercent
INNER JOIN #HmeDeptPercentages
ON MaxPercent.EMPLOYEE_ID = #HmeDeptPercentages.EMPLOYEE_ID
AND MaxPercent.MaxPer = #HmeDeptPercentages.Percentage
INNER JOIN
-- find personnel with more than one TC equal to max percentage
(SELECT EMPLOYEE_ID
FROM
(SELECT DISTINCT #HmeDeptPercentages.EMPLOYEE_ID, #HmeDeptPercentages.HME_DEPT_NO
FROM
-- find title code with greatest percentage
(SELECT EMPLOYEE_ID, MAX(Percentage) AS MaxPer
FROM #HmeDeptPercentages
GROUP BY EMPLOYEE_ID)
AS MaxPercent
INNER JOIN #HmeDeptPercentages
ON MaxPercent.EMPLOYEE_ID = #HmeDeptPercentages.EMPLOYEE_ID
AND MaxPercent.MaxPer = #HmeDeptPercentages.Percentage)
AS PotentialTitleCodes
GROUP BY EMPLOYEE_ID
HAVING COUNT(*) > 1)
AS ProblemPopulation
ON MaxPercent.EMPLOYEE_ID = ProblemPopulation.EMPLOYEE_ID
-- check for match to primary home department code
INNER JOIN AcadPers.dbo.tblSG2PER AS tblSG2PER
ON #HmeDeptPercentages.EMPLOYEE_ID = tblSG2PER.EMPLOYEE_ID
AND #HmeDeptPercentages.HME_DEPT_NO = tblSG2PER.HOME_DEPT)
AS PrimaryHomeDeptInd
ON #HmeDeptPercentages.EMPLOYEE_ID = PrimaryHomeDeptInd.EMPLOYEE_ID
-- Check on PrimaryHomeDeptInd.HME_DEPT_NO only applies if present since primary department
-- test only applied to those with more than one home dept code equal to the max percentage
WHERE PrimaryHomeDeptInd.HME_DEPT_NO IS NULL
OR #HmeDeptPercentages.HME_DEPT_NO = PrimaryHomeDeptInd.HME_DEPT_NO
GROUP BY PrimaryDepartmentPercentage.EMPLOYEE_ID)
AS PriHmeDeptNo
INNER JOIN AcadPers.dbo.tblCTLHME AS tblCTLHME
ON PriHmeDeptNo.HME_DEPT_NO = tblCTLHME.HME_DEPT_NO
LEFT JOIN AcadPers.dbo.tblDVTSCH AS tblDVTSCH
ON SUBSTRING(tblCTLHME.HME_DEPT_MAIL_CODE,1,2) = tblDVTSCH.SCH_CODE
ORDER BY PriHmeDeptNo.EMPLOYEE_ID
DROP TABLE #HmeDeptPercentages
GO
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 2:58 pm
CREATE PROCEDURE sprSnapshotPriTitle
AS
-- set current date variable to midnight, since snapshot need to include Distributions that end on snapshot date
DECLARE@CurrentDatedatetime
Set @CurrentDate = AcadPers.dbo.fnDateSetToMidnight(GETDATE())
-- create temporary table since data build used twice in the next step
CREATE TABLE #TitleCodeData (EMPLOYEE_ID char(9), TITLE_CODE char(40), TotalCurrentDist float)
INSERT INTO #TitleCodeData
SELECT DISTINCT EMPLOYEE_ID, TITLE_CODE, SUM(DIST_PERCENT) AS TotalCurrentDist
FROM
(SELECT DISTINCT EmpDistPercentages.EMPLOYEE_ID, TITLE_CODE, DIST_NUM, DIST_PERCENT
FROM
(SELECT DISTINCT tblSG2DIS.EMPLOYEE_ID, APPT_NUM, DIST_NUM,
-- need to account for DOS codes that had a percentages but shouldn't have
-- on 01/11/05 these were the DOS codes BYA and WOS; primary problem was with BYA codes
CASE WHEN ISNULL(tblPPSAuditDOSattributes.ExcludeTotalPercent,0) = 1
THEN 0 ELSE DIST_PERCENT END AS DIST_PERCENT
FROM AcadPers.dbo.tblSG2DIS AS tblSG2DIS
LEFT JOIN AcadPers.dbo.tblPPSAuditDOSattributes AS tblPPSAuditDOSattributes
ON tblSG2DIS.DIST_DOS = tblPPSAuditDOSattributes.Code
LEFT JOIN qrySnapshotWorkStudyOnly
ON tblSG2DIS.EMPLOYEE_ID = qrySnapshotWorkStudyOnly.EMPLOYEE_ID
-- remove distributions not in total dist % test
-- exception made for codes that may appear alone (WOS, BYA)
WHERE ISNULL(tblPPSAuditDOSattributes.ExcludeTotalPercentWF,0) 1
AND PAY_BEGIN_DATE = @CurrentDate
AND (tblSG2DIS.WORK_STUDY_PGM IS NULL
-- check to see if erroneously only has a work study Distribution
OR NOT(qrySnapshotWorkStudyOnly.EMPLOYEE_ID IS NULL))
AND NOT(ISNULL(tblSG2DIS.UCD_ADC_CODE,'') IN('D')))
AS EmpDistPercentages
INNER JOIN AcadPers.dbo.tblSG2APP AS tblSG2APP
ON EmpDistPercentages.EMPLOYEE_ID = tblSG2APP.EMPLOYEE_ID
AND EmpDistPercentages.APPT_NUM = tblSG2APP.APPT_NUM)
AS InitData
GROUP BY EMPLOYEE_ID, TITLE_CODE
ORDER BY EMPLOYEE_ID, TITLE_CODE
-- create temporary table of datset, data cleanup occurs after this step
CREATE TABLE #PriTitleDataset (EMPLOYEE_ID char(9), TITLE_CODE char(4), TCI_TITLE_NM_ABBRV varchar(30),
APPT_TYPE varchar(1), PERSONNEL_PGM varchar(1))
INSERT INTO #PriTitleDataset
SELECT DISTINCT PriTitle.EMPLOYEE_ID, PriTitle.TITLE_CODE, TCI_TITLE_NM_ABBRV,
-- clean up SAs and TC 9995 set to 5/1 by changing to 4/1. Based on cannot have a 5/1 combination (Academic/PSS)
-- and these title codes should not be in the staff workforce goals (Appt Types 2 and 7)
CASE WHEN APPT_TYPE = '5' AND PERSONNEL_PGM = '1' AND JGT_JOB_GROUP_ID IN('D30','D31')
THEN '4' ELSE APPT_TYPE END AS APPT_TYPE,
PERSONNEL_PGM
FROM
-- if more than one title code with greatest percentage and neither equal to the
-- Personnel primary title then select title code with the lowest number
(SELECT DISTINCT EMPLOYEE_ID, MIN(TITLE_CODE) AS TITLE_CODE
FROM
-- if more than one then check to see if one of the choices is equal to the
-- Personnel primary title
(SELECT #TitleCodeData.EMPLOYEE_ID, #TitleCodeData.TITLE_CODE
FROM
-- limit title codes to those with greatest percentage (first test: select TC with maximum percentage)
(SELECT EMPLOYEE_ID, MAX(TotalCurrentDist) AS MaxPer
FROM #TitleCodeData
GROUP BY EMPLOYEE_ID)
AS MaxPercent
INNER JOIN #TitleCodeData
ON MaxPercent.EMPLOYEE_ID = #TitleCodeData.EMPLOYEE_ID
AND MaxPercent.MaxPer = #TitleCodeData.TotalCurrentDist
LEFT JOIN
-- find situations where more than one possible max percentage but one is equal to Primary Title
(SELECT #TitleCodeData.EMPLOYEE_ID, #TitleCodeData.TITLE_CODE
FROM
-- find max percentage
(SELECT EMPLOYEE_ID, MAX(TotalCurrentDist) AS MaxPer
FROM #TitleCodeData
GROUP BY EMPLOYEE_ID)
AS MaxPercent
INNER JOIN #TitleCodeData
ON MaxPercent.EMPLOYEE_ID = #TitleCodeData.EMPLOYEE_ID
AND MaxPercent.MaxPer = #TitleCodeData.TotalCurrentDist
INNER JOIN
-- find personnel with more than one TC equal to max percentage
(SELECT EMPLOYEE_ID
FROM
(SELECT DISTINCT #TitleCodeData.EMPLOYEE_ID, #TitleCodeData.TITLE_CODE
FROM
-- find title code with greatest percentage
(SELECT EMPLOYEE_ID, MAX(TotalCurrentDist) AS MaxPer
FROM #TitleCodeData
GROUP BY EMPLOYEE_ID)
AS MaxPercent
INNER JOIN #TitleCodeData
ON MaxPercent.EMPLOYEE_ID = #TitleCodeData.EMPLOYEE_ID
AND MaxPercent.MaxPer = #TitleCodeData.TotalCurrentDist)
AS PotentialTitleCodes
GROUP BY EMPLOYEE_ID
HAVING COUNT(*) > 1)
AS ProblemPopulation
ON MaxPercent.EMPLOYEE_ID = ProblemPopulation.EMPLOYEE_ID
-- check for match to primary title
INNER JOIN AcadPers.dbo.tblSG2PER AS tblSG2PER
ON #TitleCodeData.EMPLOYEE_ID = tblSG2PER.EMPLOYEE_ID
AND #TitleCodeData.TITLE_CODE = tblSG2PER.PRIMARY_TITLE)
AS PrimaryTitleInd
ON #TitleCodeData.EMPLOYEE_ID = PrimaryTitleInd.EMPLOYEE_ID
WHERE PrimaryTitleInd.TITLE_CODE IS NULL
OR #TitleCodeData.TITLE_CODE = PrimaryTitleInd.TITLE_CODE
) AS PrimaryDeptTest
GROUP BY PrimaryDeptTest.EMPLOYEE_ID)
AS PriTitle
INNER JOIN AcadPers.dbo.tblSG2APP AS tblSG2APP
ON PriTitle.EMPLOYEE_ID = tblSG2APP.EMPLOYEE_ID
AND PriTitle.TITLE_CODE = tblSG2APP.TITLE_CODE
INNER JOIN AcadPers.dbo.tblSG2DIS AS tblSG2DIS
ON tblSG2APP.EMPLOYEE_ID = tblSG2DIS.EMPLOYEE_ID
AND tblSG2APP.APPT_NUM = tblSG2DIS.APPT_NUM
INNER JOIN AcadPers.dbo.tblCTLTCI AS tblCTLTCI
ON PriTitle.TITLE_CODE = tblCTLTCI.TCI_TITLE_CODE
INNER JOIN AcadPers.dbo.tblCTLJGT AS tblCTLJGT
ON PriTitle.TITLE_CODE = tblCTLJGT.JGT_TITLE_CODE
WHERE PAY_BEGIN_DATE = @CurrentDate
AND NOT(ISNULL(tblSG2DIS.UCD_ADC_CODE,'') IN('D'))
ORDER BY PriTitle.EMPLOYEE_ID
-- if ladder rank title code is secondary title then switch to ladder rank title ********************************************************************
-- Professor in Residence, Professor of Clinical ___, Clinical Professor, Adjunct Professor,
--___ in the AES, Specialist in the Cooperative Extension, Professional Researcher
-- ignore if ladder rank is only WOS or zero percent
UPDATE #PriTitleDataset
Set TITLE_CODE = Updates.TITLE_CODE,
TCI_TITLE_NM_ABBRV = Updates.TCI_TITLE_NM_ABBRV,
APPT_TYPE = Updates.APPT_TYPE,
PERSONNEL_PGM = Updates.PERSONNEL_PGM
FROM #PriTitleDataset
INNER JOIN
(SELECT DISTINCT tblSG2APP.EMPLOYEE_ID, tblSG2APP.TITLE_CODE, tblCTLTCI.TCI_TITLE_NM_ABBRV,
tblSG2APP.APPT_TYPE, tblSG2APP.PERSONNEL_PGM
--,#PriTitleDataset.TITLE_CODE AS CalcTC,tblSG2APP.DIST_PERCENT-- use for analyzing records impacted by change
FROM AcadPers.dbo.tblSG2APP AS tblSG2APP
INNER JOIN AcadPers.dbo.tblSG2DIS AS tblSG2DIS
ON tblSG2APP.EMPLOYEE_ID = tblSG2DIS.EMPLOYEE_ID
AND tblSG2APP.APPT_NUM = tblSG2DIS.APPT_NUM
INNER JOIN AcadPers.dbo.tblAcadPersSeriesCatTC AS tblAcadPersSeriesCatTC
ON tblSG2APP.TITLE_CODE = tblAcadPersSeriesCatTC.TitleCode
LEFT JOIN AcadPers.dbo.tblPPSAuditDOSattributes AS tblPPSAuditDOSattributes
ON tblSG2DIS.DIST_DOS = tblPPSAuditDOSattributes.Code
INNER JOIN #PriTitleDataset
ON tblSG2APP.EMPLOYEE_ID = #PriTitleDataset.EMPLOYEE_ID
INNER JOIN AcadPers.dbo.tblAcadPersSeriesTC AS tblAcadPersSeriesTCDataset
ON #PriTitleDataset.TITLE_CODE = tblAcadPersSeriesTCDataset.TitleCode
INNER JOIN AcadPers.dbo.tblCTLTCI AS tblCTLTCI
ON tblSG2APP.TITLE_CODE = tblCTLTCI.TCI_TITLE_CODE
WHERE PAY_BEGIN_DATE = @CurrentDate
-- issue for people who are not ladder rank
AND (ProfInRes = 'X' OR ProfClin = 'X'
OR ClinProf = 'X' OR AdjProf = 'X'
OR intheAES = 'X' OR SpecCoopExt = 'X'
OR ProfRes = 'X')
-- check to see if person has ladder rank
AND ISNULL(tblAcadPersSeriesCatTC.LadderRank,'') = 'X'
-- check for WOS and zero percent
AND ISNULL(tblPPSAuditDOSattributes.ExcludeTotalPercent,'') 1
AND tblSG2DIS.DIST_PERCENT > 0
AND NOT(ISNULL(tblSG2DIS.UCD_ADC_CODE,'') IN('D')))
AS Updates
ON #PriTitleDataset.EMPLOYEE_ID = Updates.EMPLOYEE_ID
-- scrub records with more than one Appt Type/Prog Code combination *********************************************************************************
-- find records with more than one ApptType/PersPrgCode combination
--remove record(s) with ApptType/ProgCode combination of 5/1
DELETE #PriTitleDataset
FROM #PriTitleDataset
INNER JOIN
-- does multiple records person have non 5/1 record?
(SELECT MultipleRecrods.EMPLOYEE_ID
FROM
-- does perosn have multiple records
(SELECT EMPLOYEE_ID
FROM
-- determine number of records per person
(SELECT EMPLOYEE_ID, COUNT(*) AS Number
FROM #PriTitleDataset
GROUP BY EMPLOYEE_ID)
AS RecordCount
WHERE Number > 1)
AS MultipleRecrods
INNER JOIN #PriTitleDataset
ON MultipleRecrods.EMPLOYEE_ID = #PriTitleDataset.EMPLOYEE_ID
WHERE ISNULL(APPT_TYPE,'5') '5' OR ISNULL(PERSONNEL_PGM,'1') '1')
AS FiveOneIssue
ON #PriTitleDataset.EMPLOYEE_ID = FiveOneIssue.EMPLOYEE_ID
WHERE ISNULL(APPT_TYPE,'') = '5' AND ISNULL(PERSONNEL_PGM,'') = '1'
-- if staff title and one of the records is Appt Type 2 (career) then delete other staff records
DELETE #PriTitleDataset
FROM #PriTitleDataset
INNER JOIN
-- does multiple records person have a staff record wtih an appointment type of 2
(SELECT MultipleRecrods.EMPLOYEE_ID
FROM
-- does perosn have multiple records
(SELECT EMPLOYEE_ID
FROM
-- determine number of records per person
(SELECT EMPLOYEE_ID, COUNT(*) AS Number
FROM #PriTitleDataset
GROUP BY EMPLOYEE_ID)
AS RecordCount
WHERE Number > 1)
AS MultipleRecrods
INNER JOIN #PriTitleDataset
ON MultipleRecrods.EMPLOYEE_ID = #PriTitleDataset.EMPLOYEE_ID
WHERE ISNULL(APPT_TYPE,'') = '2'
AND TITLE_CODE >= '4000')
AS StaffCareerIssue
ON #PriTitleDataset.EMPLOYEE_ID = StaffCareerIssue.EMPLOYEE_ID
WHERE ISNULL(APPT_TYPE,'') '2'
AND TITLE_CODE >= '4000'
-- if staff title, one of the records is Appt Type 7 (partial year/career) and after previous test, then delete other staff records
DELETE #PriTitleDataset
FROM #PriTitleDataset
INNER JOIN
-- does multiple records person have a staff record wtih an appointment type of 7
(SELECT MultipleRecrods.EMPLOYEE_ID
FROM
-- does perosn have multiple records
(SELECT EMPLOYEE_ID
FROM
-- determine number of records per person
(SELECT EMPLOYEE_ID, COUNT(*) AS Number
FROM #PriTitleDataset
GROUP BY EMPLOYEE_ID)
AS RecordCount
WHERE Number > 1)
AS MultipleRecrods
INNER JOIN #PriTitleDataset
ON MultipleRecrods.EMPLOYEE_ID = #PriTitleDataset.EMPLOYEE_ID
WHERE ISNULL(APPT_TYPE,'') = '7'
AND TITLE_CODE >= '4000')
AS StaffCareerPartialYearIssue
ON #PriTitleDataset.EMPLOYEE_ID = StaffCareerPartialYearIssue.EMPLOYEE_ID
WHERE ISNULL(APPT_TYPE,'') '7'
AND TITLE_CODE >= '4000'
-- if student assistant title and one of the records is Appt Type 4 (casual restricted) then delete other stduent asssistant records
-- note that at this point career staff records have been scrubbed
DELETE #PriTitleDataset
FROM #PriTitleDataset
INNER JOIN
-- does multiple records person have a student assistant record wtih an appointment type of 4
(SELECT MultipleRecrods.EMPLOYEE_ID
FROM
-- does perosn have multiple records
(SELECT EMPLOYEE_ID
FROM
-- determine number of records per person
(SELECT EMPLOYEE_ID, COUNT(*) AS Number
FROM #PriTitleDataset
GROUP BY EMPLOYEE_ID)
AS RecordCount
WHERE Number > 1)
AS MultipleRecrods
INNER JOIN #PriTitleDataset
ON MultipleRecrods.EMPLOYEE_ID = #PriTitleDataset.EMPLOYEE_ID
INNER JOIN AcadPers.dbo.tblCTLJGT AS tblCTLJGT
ON #PriTitleDataset.TITLE_CODE = tblCTLJGT.JGT_TITLE_CODE
WHERE ISNULL(APPT_TYPE,'') = '4'
AND JGT_JOB_GROUP_ID IN('D31'))
AS SAissue
ON #PriTitleDataset.EMPLOYEE_ID = SAissue.EMPLOYEE_ID
INNER JOIN AcadPers.dbo.tblCTLJGT AS tblCTLJGT
ON #PriTitleDataset.TITLE_CODE = tblCTLJGT.JGT_TITLE_CODE
WHERE ISNULL(APPT_TYPE,'') '4'
AND JGT_JOB_GROUP_ID IN('D31')
-- return primary title records
SELECT *
FROM #PriTitleDataset
/* -- next section used for editing in query analyzer: problem of multiple Appt Type codes for one person
SELECT #PriTitleDataset.*
FROM
(select EMPLOYEE_ID, COUNT(*) AS Number
from #PriTitleDataset
group by EMPLOYEE_ID)
AS INitDAta
inner join #PriTitleDataset
on InitData.EMPLOYEE_ID = #PriTitleDataset.EMPLOYEE_ID
WHERE Number > 1
order by TITLE_CODE
*/
DROP TABLE #PriTitleDataset
DROP TABLE #TitleCodeData
GO
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 2:58 pm
CREATE PROCEDURE sprSnapshotWorkforceBuild @Type varchar(10) = '', @TableName varchar(8) = NULL AS
/*
@Type used to identify as either an official snapshot, HR update, or as a simple data pull
OfficialBuilds official snapshot table
HRupdatedeletes records from HR WF table and inserts new records
Any other valuereturns dataset
@TableName is an extension of an official snapshot and holds the name to be given to the table
*/
/*
-- next section used for editing in query analyzer
DECLARE@Typevarchar(10),
@TableNamevarchar(8)
Set @Type = 'Official'
Set @TableName = 'wf050519'--NULL
*/
-- declare variables
DECLARE@Queryvarchar(7000)
-- pull primary title code information
CREATE TABLE #PrimaryTitleCode (EMPLOYEE_ID char(9), TITLE_CODE char(4), TITLE_NAME varchar(30),
APPT_TYPE char(1), PERSONNEL_PGM char(1))
INSERT INTO #PrimaryTitleCode
exec sprSnapshotPriTitle
-- pull primary department information
CREATE TABLE #PrimaryDepartment (EMPLOYEE_ID char(9), HomeDeptNo char(6), HomeDeptShortName varchar(15),
HomeDeptName varchar(30), DivisionCode char(2), DivisionAbbrv varchar(12), DivisionShortName varchar(25),
DivisionName varchar(50))
INSERT INTO #PrimaryDepartment
exec sprSnapshotPriDepartment
-- build workforce dataset
CREATE TABLE #Workforce (EID char(9), FullName varchar(50), TitleCode char(4), Title varchar(50), ApptType char(1),
PersPrgmCd char(1), Gender char(1), Ethnicity char(1), CitizenCode char(1), VetStatus char(1),
VetDisabStatus char(1), HandcpStatus char(1), PercentEmplyd float, HireDate datetime, BirthDate datetime,
MaxSal float, HomeDeptNo char(6), JobGroup char(3), orgunit char(4))
INSERT INTO #Workforce
SELECT EMPLOYEE_ID, EMP_NAME, TITLE_CODE, TITLE_NAME, ISNULL(APPT_TYPE,''), ISNULL(PERSONNEL_PGM,''), SEXCODE, ETHNIC_ID,
CITIZEN_CODE, VET_STATUS, VET_DISAB_STAT, HANDICAP_STAT, PercentEmployed, HIRE_DATE, BIRTH_DATE,
MaxSalary, HomeDeptNo, ISNULL(JGT_JOB_GROUP_ID,'') AS JobGroup, orgunit
FROM
(SELECT DISTINCT #PrimaryTitleCode.EMPLOYEE_ID, TITLE_CODE, TITLE_NAME, APPT_TYPE, PERSONNEL_PGM,
EMP_NAME, SEXCODE, ETHNIC_ID, CITIZEN_CODE, VET_STATUS, VET_DISAB_STAT, HANDICAP_STAT,
ISNULL(PercentEmployed,0) AS PercentEmployed, HIRE_DATE, BIRTH_DATE, MaxSalary, HomeDeptNo
FROM #PrimaryTitleCode
INNER JOIN AcadPers.dbo.tblSG2PER AS tblSG2PER
ON #PrimaryTitleCode.EMPLOYEE_ID = tblSG2PER.EMPLOYEE_ID
INNER JOIN AcadPers.dbo.tblSG2PAY AS tblSG2PAY
ON #PrimaryTitleCode.EMPLOYEE_ID = tblSG2PAY.EMPLOYEE_ID
LEFT JOIN AcadPers.dbo.qryAcadPersMaxSalary AS qryAcadPersMaxSalary
ON #PrimaryTitleCode.EMPLOYEE_ID = qryAcadPersMaxSalary.EMPLOYEE_ID
LEFT JOIN qrySnapshotTotalPercentEmplyd AS qrySnapshotTotalPercentEmplyd
ON #PrimaryTitleCode.EMPLOYEE_ID = qrySnapshotTotalPercentEmplyd.EID
LEFT JOIN #PrimaryDepartment
ON #PrimaryTitleCode.EMPLOYEE_ID = #PrimaryDepartment.EMPLOYEE_ID)
AS InitData
LEFT JOIN AcadPers.dbo.tblCTLJGT AS tblCTLJGT
ON InitData.TITLE_CODE = tblCTLJGT.JGT_TITLE_CODE
LEFT JOIN diversity.dbo.homedept AS HomeDeptOrgUnitCode
ON InitData.HomeDeptNo = HomeDeptOrgUnitCode.homedept
ORDER BY EMP_NAME
-- if not an official dataset or an update by HR then return data
--if HR Update then clear HR Workforce table and insert new data
--if Official then build new table, populate the table, and attach triggers to the table
IF @Type = 'HRupdate'
BEGIN
DELETE FROM wfHR
INSERT INTO wfHR
SELECT *
FROM #Workforce
-- Update last time data inserted
UPDATE tblUpdateInformation
Set LastUpdate = GETDATE()
WHERE UpdateName = 'wfHR'
END
ELSE IF @Type = 'Official'
BEGIN
-- Set @TableName if value was not submitted, used by MS Access interface
IF @TableName IS NULL
BEGIN
SELECT @TableName = GenWFSnpshtName
FROM tblMisc
END
-- build table
Set @Query = 'CREATE TABLE ' + @TableName + ' (EID char(9), FullName varchar(50), TitleCode char(4), Title varchar(50),
ApptType char(1), PersPrgmCd char(1), Gender char(1), Ethnicity char(1), CitizenCode char(1),
VetStatus char(1), VetDisabStatus char(1), HandcpStatus char(1), PercentEmplyd float, HireDate datetime,
BirthDate datetime, MaxSal float, HomeDeptNo char(6), JobGroup char(3), orgunit char(4)
CONSTRAINT PK_' + @TableName + ' PRIMARY KEY (EID, ApptType, PersPrgmCd))'
Exec(@Query)
-- populate table
Set @Query = 'INSERT INTO ' + @TableName + ' ' +
'SELECT *
FROM #Workforce'
Exec(@Query)
-- attach triggers for recording changes to workforce table, first build INSERT AND DELETE triggers
DECLARE@TriggerTypevarchar(6),
@QueryTypevarchar(8)
CREATE TABLE #TriggerTypes (TriggerType varchar(6),QueryType varchar(8))
INSERT INTO #TriggerTypes VALUES ('INSERT','INSERTED')
INSERT INTO #TriggerTypes VALUES ('DELETE','DELETED')
DECLARE BuildTriggers CURSOR FOR
SELECT TriggerType, QueryType
FROM #TriggerTypes
OPEN BuildTriggers
FETCH NEXT FROM BuildTriggers INTO @TriggerType, @QueryType
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Query = 'CREATE TRIGGER '+ @TableName + @TriggerType + '
ON dbo.' + @TableName + '
FOR ' + @TriggerType + '
AS
SET CONCAT_NULL_YIELDS_NULL OFF
-- trigger dynamically generated by new workforce snapshot process
DECLARE @TableName varchar(8), @ADC_Type varchar(25), @ActionDate datetime,
@EID char(9), @FullName varchar(50), @TitleCode char(4), @Title varchar(50), @ApptType char(1),
@PersPrgmCd char(1), @Gender char(1), @Ethnicity char(1), @CitizenCode char(1), @VetStatus char(1),
@VetDisabStatus char(1), @HandcpStatus char(1), @PercentEmplyd float, @HireDate datetime, @BirthDate datetime,
@MaxSal float, @HomeDeptNo char(6), @JobGroup char(3), @orgunit char(4)
Set @TableName = ''' + @TableName + '''
Set @ADC_TYPE = ''' + @TriggerType + '''
Set @ActionDate = GETDATE()
SELECT @EID = A.EID, @FullName = A.FullName, @TitleCode = A.TitleCode, @Title = A.Title, @ApptType = A.ApptType,
@PersPrgmCd = A.PersPrgmCd, @Gender = A.Gender, @Ethnicity = A.Ethnicity, @CitizenCode = A.CitizenCode,
@VetStatus = A.VetStatus, @VetDisabStatus = A.VetDisabStatus, @HandcpStatus = A.HandcpStatus,
@PercentEmplyd = A.PercentEmplyd, @HireDate = A.HireDate, @BirthDate = A.BirthDate,
@MaxSal = A.MaxSal, @HomeDeptNo = A.HomeDeptNo, @JobGroup = A.JobGroup, @orgunit = A.orgunit
FROM ' + @QueryType + ' AS A
INSERT INTO tblWorkforceChangesLog (TableName, ADC_Type, ActionDate, EID, FullName, TitleCode, Title, ApptType,
PersPrgmCd, Gender, Ethnicity, CitizenCode, VetStatus, VetDisabStatus, HandcpStatus, PercentEmplyd, HireDate,
BirthDate, MaxSal, HomeDeptNo, JobGroup, orgunit)
VALUES (@TableName, @ADC_Type, @ActionDate, @EID, @FullName, @TitleCode, @Title, @ApptType, @PersPrgmCd,
@Gender, @Ethnicity, @CitizenCode, @VetStatus, @VetDisabStatus, @HandcpStatus, @PercentEmplyd, @HireDate,
@BirthDate, @MaxSal, @HomeDeptNo, @JobGroup, @orgunit)'
Exec(@Query)
FETCH NEXT FROM BuildTriggers INTO @TriggerType, @QueryType
END
CLOSE BuildTriggers
DEALLOCATE BuildTriggers
DROP TABLE #TriggerTypes
-- BUILD UPDATE trigger
Set @Query = 'CREATE TRIGGER '+ @TableName +'UPDATE
ON dbo.' + @TableName + '
FOR UPDATE
AS
SET CONCAT_NULL_YIELDS_NULL OFF
-- trigger dynamically generated by new workforce snapshot process
DECLARE @TableName varchar(8), @ADC_Type varchar(25), @ActionDate datetime,
@EID char(9), @FullName varchar(50), @TitleCode char(4), @Title varchar(50), @ApptType char(1),
@PersPrgmCd char(1), @Gender char(1), @Ethnicity char(1), @CitizenCode char(1), @VetStatus char(1),
@VetDisabStatus char(1), @HandcpStatus char(1), @PercentEmplyd float, @HireDate datetime, @BirthDate datetime,
@MaxSal float, @HomeDeptNo char(6), @JobGroup char(3), @orgunit char(4)
Set @TableName = ''' + @TableName + '''
Set @ADC_TYPE = ''UPDATE-New''
Set @ActionDate = GETDATE()
SELECT @EID = A.EID, @FullName = A.FullName, @TitleCode = A.TitleCode, @Title = A.Title, @ApptType = A.ApptType,
@PersPrgmCd = A.PersPrgmCd, @Gender = A.Gender, @Ethnicity = A.Ethnicity, @CitizenCode = A.CitizenCode,
@VetStatus = A.VetStatus, @VetDisabStatus = A.VetDisabStatus, @HandcpStatus = A.HandcpStatus,
@PercentEmplyd = A.PercentEmplyd, @HireDate = A.HireDate, @BirthDate = A.BirthDate,
@MaxSal = A.MaxSal, @HomeDeptNo = A.HomeDeptNo, @JobGroup = A.JobGroup, @orgunit = A.orgunit
FROM INSERTED AS A
INSERT INTO tblWorkforceChangesLog (TableName, ADC_Type, ActionDate, EID, FullName, TitleCode, Title, ApptType,
PersPrgmCd, Gender, Ethnicity, CitizenCode, VetStatus, VetDisabStatus, HandcpStatus, PercentEmplyd, HireDate,
BirthDate, MaxSal, HomeDeptNo, JobGroup, orgunit)
VALUES (@TableName, @ADC_Type, @ActionDate, @EID, @FullName, @TitleCode, @Title, @ApptType, @PersPrgmCd,
@Gender, @Ethnicity, @CitizenCode, @VetStatus, @VetDisabStatus, @HandcpStatus, @PercentEmplyd, @HireDate,
@BirthDate, @MaxSal, @HomeDeptNo, @JobGroup, @orgunit)
Set @ADC_TYPE = ''UPDATE-Old''
SELECT @EID = A.EID, @FullName = A.FullName, @TitleCode = A.TitleCode, @Title = A.Title, @ApptType = A.ApptType,
@PersPrgmCd = A.PersPrgmCd, @Gender = A.Gender, @Ethnicity = A.Ethnicity, @CitizenCode = A.CitizenCode,
@VetStatus = A.VetStatus, @VetDisabStatus = A.VetDisabStatus, @HandcpStatus = A.HandcpStatus,
@PercentEmplyd = A.PercentEmplyd, @HireDate = A.HireDate, @BirthDate = A.BirthDate,
@MaxSal = A.MaxSal, @HomeDeptNo = A.HomeDeptNo, @JobGroup = A.JobGroup, @orgunit = A.orgunit
FROM DELETED AS A
INSERT INTO tblWorkforceChangesLog (TableName, ADC_Type, ActionDate, EID, FullName, TitleCode, Title, ApptType,
PersPrgmCd, Gender, Ethnicity, CitizenCode, VetStatus, VetDisabStatus, HandcpStatus, PercentEmplyd, HireDate,
BirthDate, MaxSal, HomeDeptNo, JobGroup, orgunit)
VALUES (@TableName, @ADC_Type, @ActionDate, @EID, @FullName, @TitleCode, @Title, @ApptType, @PersPrgmCd,
@Gender, @Ethnicity, @CitizenCode, @VetStatus, @VetDisabStatus, @HandcpStatus, @PercentEmplyd, @HireDate,
@BirthDate, @MaxSal, @HomeDeptNo, @JobGroup, @orgunit)'
Exec(@Query)
END
ELSE
BEGIN
SELECT *
FROM #Workforce
END
-- clean up temporary tables
DROP TABLE #Workforce
DROP TABLE #PrimaryTitleCode
DROP TABLE #PrimaryDepartment
GO
Everett Wilson
ewilson10@yahoo.com
June 7, 2005 at 3:00 pm
Just a thaught, I've learned that temp tables can cause locks in tempdb when the procs are run... Maybe that would be something else to check but I doubt that this is your problem.
June 7, 2005 at 3:04 pm
That would explain why I hadn't seen this issue before coming to my current position. I'll try replacing the temp tables with the code and see what happens. Unfortunately it'll probably be a couple of hours before I can try this.
Thanks for the idea, as much as I hate the thought it does explain things.
Everett Wilson
ewilson10@yahoo.com
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply