March 21, 2008 at 10:29 am
A client of ours keeps running into report problems. There is a view we have, tblSMDRData, which collects data from a phone system. Here's how it looks there:
select s.userId ,s.employeeId ,s.Location ,s.Extension ,s.Date ,s.Time ,s.Line ,s.CallDuration
,s.CallType ,s.DNIS ,s.ANI ,s.AccountCode ,s.City ,s.State ,s.isShortCall ,s.importFileName
,s.LineNumber ,s.ImportDate ,s.ImportTime ,s.AnsweredAtExtension ,s.RingDuration
,s.Service ,s.Cost ,s.CallDateStamp ,s.CallTimeStamp
,sourceid = case
when s.sourceid > 0 then
s.sourceid
else
case
when x.sourceid is null or x.sourceid = 0 then 0
when x.activesources > 1 then 0
else x.sourceid
end
end
,s.CallID
,clientID = case
when s.clientid > 0 then
s.clientid
else
case
when x.clientid is null or x.clientid = 0 then 0
when x.activesources > 1 then 0
else x.clientid
end
end
,locationid=null
from executone..tblSMDRdata s
left join (
select s1.*, s2.activesources from tblsource s1 join (
select dnis, max(sourceid) sourceid, count(*) activesources from tblsource where isactive = 1 group by DNIS
) s2 on s1.sourceid = s2.sourceid
) x on x.dnis = s.dnis
This view is then called from another view which processes data to generate the report:
SELECT smdr.Date as CallDate
,smdr.DNIS
,Campaign = CASE WHEN S.Campaign IS NULL THEN 'UNKNOWN: No sources for DNIS'
WHEN S.ActiveSources > 1 THEN 'UNKNOWN: Multiple Sources for DNIS'
ELSE S.Campaign
END
,CampaignID = CASE WHEN S.Campaign IS NULL THEN 0
WHEN S.ActiveSources > 1 THEN 0
ELSE S.CampaignID
END
,campaignActive = CASE WHEN S.Campaign IS NULL THEN 1
WHEN S.ActiveSources > 1 THEN 1
ELSE C.isActive
END
,Client= ISNULL(Client, 'None')
,ClientID= ISNULL(CL.ClientID, 0)
,ClientActive= ISNULL(CL.isActive, 0)
,LocationID = ISNULL(smdr.LocationID, 0)
,LocationActive = ISNULL(L.isActive,1)
--fyi the spaces here are used for sorting - don't remove them
,CallHour = case when datepart(hour, smdr.Time) = 12 then ' 12:00pm'
when datepart(hour, smdr.Time) = 00 then ' 12:00am '
when datepart(hour, smdr.Time) < 13 then
case when len(datepart(hour, smdr.Time)) < 2 then ' 0' + cast(datepart(hour, smdr.Time) as varchar) + ':00am '
else ' ' + cast(datepart(hour, smdr.Time) as varchar) + ':00am'
end
else
case when len(datepart(hour, smdr.Time) - 12) < 2 then ' 0' + cast((datepart(hour, smdr.Time) - 12) as varchar) + ':00pm'
else ' ' + cast((datepart(hour, smdr.Time) - 12) as varchar) + ':00pm'
end
etc...
Running this as a query generates the following message:
SqlDumpExceptionHandler: Process 106 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
If I substitute locationid=null for locationid=0, I can run the query fine, even though the LocationID = ISNULL(smdr.LocationID, 0) syntax should set the value to 0 by default. Another oddity is that when the query does work (using a limited date/time range), I get formatting errors in the CallHour column - the datepart doesn't generate data for any times other than 12am or 12 pm.
I'm running SP3 so it may be worth my while to upgrade to SP4, but any reasons why this might be happening?
Thanks,
Mike
March 31, 2008 at 9:03 pm
May this link help.
April 1, 2008 at 6:34 am
Thanks for the input. The database is actually SQL 2000 sp3. I resolved the issue by changing the LocationID=null to LocationID=0 in the view. I'm not entirely sure why this worked since we use LocationID=null for our other clients who don't store locationID in any of their other tables, but with this view it made all the difference.
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply