Scheduleret Stored Procedure indsætter ikke alle records ved INSERT
Hej,Jeg har en stored procedure som er sat til at køre hver 30 min. fra 08:00 til 23:00
Når jeg manuelt kører jobbet, så gemmer den alle data som den bør gøre, men når serveren afvikler jobbet så indsætter den langt fra alle records. Det er sporadisk den mangler at indsætte, det er ikke en samlet klump.
Er der nogen som har nogle idéer til hvad det kan være?
Se nedenstående script :
(Der hvor den hentes data fra : Select * from openquery(TELM98UCCX01, 'xxxx') er en Informix Database som godt kan afvikles andre steder uden tab af data og jeg er derfor sikker på at alt data bliver hentet ud som det skal - men det fejler ved INSERT eller andet sted)
DECLARE @strCalldata TABLE (
sessionid decimal(18,0),
resourcelogin varchar(250),
resourcename varchar(250),
extension int,
startdatetime datetime,
ringstartdatetime datetime,
enddatetime datetime,
ringtime int,
talktime int,
holdtime int,
worktime int,
callhandled int,
calldirection int,
applicationID varchar(50),
originalnumber varchar(250),
enterednumber varchar(250),
SkillID int,
Skillname varchar(250))
INSERT INTO @strCalldata (sessionid, resourcelogin, resourcename, extension, startdatetime, ringstartdatetime, enddatetime, ringtime, talktime, holdtime, worktime, callhandled, calldirection, applicationID, originalnumber, enterednumber, SkillID, Skillname)
Select * from openquery(TELM98UCCX01, '
SELECT ccd.sessionid, r.resourceloginID, r.resourceName, r.extension, ccd.startdatetime, acd.ringstartdatetime, ccd.enddatetime, acd.ringtime, acd.talktime, acd.holdtime, acd.worktime, ccd.contactDisposition, ccd.contactType, ccd.calledNumber,
ccd.originatorDN, ccd.customVariable2, s.skillID, csq.CSQName
FROM ContactCallDetail ccd
LEFT JOIN (SELECT acd.sessionid, acd.sessionSeqNum, acd.nodeID, acd.profileID, acd.resourceID, acd.startdatetime as ringstartdatetime, acd.enddatetime as ringenddatetime, acd.ringtime, acd.talktime, acd.holdtime, acd.worktime
FROM AgentConnectionDetail acd
JOIN (SELECT acd.sessionid, acd.sessionSeqNum, MAX(acd.startdatetime) as startdatetime, MAX(acd.enddatetime) as enddatetime
FROM AgentConnectionDetail acd WHERE acd.startdatetime >= DATE(CURRENT) GROUP BY acd.sessionid, acd.sessionSeqNum) latest_acd
ON acd.sessionid = latest_acd.sessionid AND acd.startdatetime = latest_acd.startdatetime AND acd.enddatetime = latest_acd.enddatetime
WHERE acd.startdatetime >= DATE(CURRENT)) acd
ON acd.sessionID = ccd.sessionID AND acd.sessionSeqNum = ccd.sessionSeqNum AND acd.nodeID = ccd.nodeID AND acd.profileID = ccd.profileID
LEFT JOIN Resource r ON r.resourceID = acd.resourceID
JOIN ContactQueueDetail cqd ON cqd.sessionID = ccd.sessionID AND cqd.sessionSeqNum = ccd.sessionSeqNum AND cqd.nodeID = ccd.nodeID AND cqd.profileID = ccd.profileID
LEFT JOIN ContactServiceQueue csq ON csq.RecordID = cqd.TargetID AND csq.profileID = cqd.profileID
JOIN Skillgroup sg ON csq.skillGroupID = sg.skillGroupID AND csq.profileID = sg.profileID AND sg.SkillID > 0
JOIN Skill s ON sg.skillID = s.skillID AND sg.profileID = s.profileID AND sg.SkillID > 0
WHERE ccd.startdatetime >= DATE(CURRENT)
');
DECLARE @strDailyCalldata TABLE (
CallArrivalTime datetime,
CallRingingTime datetime,
CallAnswerTime datetime,
CallEndTime datetime,
AgentName nvarchar(50),
UserID Uniqueidentifier,
Router varchar(250),
SkillID int,
Skillname varchar(250),
CallResult varchar(250),
CallDirection int,
NumberOriginal varchar(250),
NumberEntered varchar(250),
NumberReference varchar(250),
CallNumberID bigint,
UpdateTime datetime,
Extension int)
INSERT INTO @strDailyCalldata (CallArrivalTime, CallRingingTime, CallAnswerTime, CallEndTime, AgentName, UserID, Router, SkillID, Skillname, CallResult, CallDirection, NumberOriginal, NumberEntered, NumberReference, CallNumberID, UpdateTime, Extension)
SELECT DATEADD(HOUR, CONVERT(int, TELMORE_PhoneSystem.dbo.udf_dst_dates(a.startdatetime)), a.startdatetime) as CallArrivalTime,
DATEADD(HOUR, CONVERT(int, TELMORE_PhoneSystem.dbo.udf_dst_dates(a.ringstartdatetime)), a.ringstartdatetime) as CallRingingTime,
DATEADD(HOUR, CONVERT(int, TELMORE_PhoneSystem.dbo.udf_dst_dates(DATEADD(SECOND, a.ringtime, a.ringstartdatetime))), DATEADD(SECOND, a.ringtime, a.ringstartdatetime)) as CallAnswerTime,
DATEADD(HOUR, CONVERT(int, TELMORE_PhoneSystem.dbo.udf_dst_dates(a.enddatetime)), a.enddatetime) as CallEndTime,
LOWER(a.resourcelogin) as Agentname, up.UserID,
CASE
WHEN a.applicationID = '3200' OR a.applicationID = '7017' THEN 'Reception'
WHEN a.applicationID = '3201' OR a.applicationID = '7100' THEN 'Kundeservice'
WHEN a.applicationID = '3202' THEN 'Tyverispærring'
WHEN a.applicationID = '8001' THEN 'Intern Viderestilling'
ELSE 'Telmore' END as Router, -- Ukendt Router
a.SkillID, a.Skillname,
CASE WHEN a.callhandled = 1 THEN 'Abandoned' ELSE 'Answered' END as CallResult,
CASE
WHEN a.calldirection = 2 THEN 1 --Outbound
WHEN a.calldirection = 5 THEN 5 --Inbound Transfer
ELSE 0 END as CallDirection, --Inbound IVR
CASE WHEN a.originalnumber = '' THEN '?' ELSE a.originalnumber END as NumberOriginal,
CASE WHEN a.enterednumber = '' THEN '?' ELSE a.enterednumber END as NumberEntered,
CASE
WHEN a.originalnumber = '' AND a.enterednumber = '' THEN '?'
WHEN a.originalnumber <> '' AND a.enterednumber = '' THEN a.originalnumber
ELSE a.enterednumber END as NumberReference,
CAST(a.sessionid as bigint) as CallNumberID, Getdate() as UpdateTime, a.extension
FROM @strCalldata a
LEFT JOIN TELMORE_UserSetup.dbo.UserProfiles up ON LOWER(a.resourcelogin) = LOWER(up.Username)
ORDER BY a.startdatetime;
INSERT INTO TELMORE_PhoneSystem.dbo.PhoneSystem_CallData_Daily
SELECT * FROM @strDailyCalldata dc
WHERE dc.CallArrivalTime > (SELECT TOP 1 pcd.CallArrivalTime FROM TELMORE_PhoneSystem.dbo.PhoneSystem_CallData_Daily pcd ORDER BY pcd.CallArrivalTime DESC)
Som sagt så har jeg afviklet den manuelt og alt er som det skal vfære, men ved scheduleret job fejler den. Kan det være fordi der er flere job der bliver afviklet på samme tid?
