Compiles but 8630 runtime error

[timmoser]

I have an SP that I am moving from Sybase, where it works. I am able to compile it but I can't run it. I narrowed it down to one part of the code: datepart(year,b.week_date)=datepart(year,z.week_date) I checked the value of the field and they seem to be correct [week_date] [smalldatetime] NOT NULL I looked the error up on Google but seems like none of the answers apply to my situation.

This is the full error message...

Server: Msg 8630, Level 17, State 38, Procedure s_it_tt_getsumytd, Line 8
Internal Query Processor Error: The query processor encountered an unexpected error during execution.


This is the proc.

CREATE PROCEDURE dbo.s_it_tt_getsumytd
@uid VARCHAR(10)
AS
BEGIN

SET NOCOUNT ON

SELECT z.uid, datepart(year,z.week_date) as totyear,
ISNULL(SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday),0) AS total,
holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE (a.activity = 'Holiday' OR a.activity = 'Floating Holiday') AND uid = @uid AND datepart(year,a.week_date)=datepart(year,z.week_date)),0),
vacation = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity b
WHERE b.activity = 'Vacation' AND uid = @uid AND datepart(year,b.week_date)=datepart(year,z.week_date)),0),
ato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity c
WHERE c.activity = 'Authorized Time Off' AND uid = @uid AND datepart(year,c.week_date)=datepart(year,z.week_date)),0),
mato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity d
WHERE (d.activity = 'Management Time Off' OR d.activity = 'Death in Family' OR d.activity = 'Diversity Day' OR d.activity = 'Leave of Absence') AND uid = @uid AND datepart(year,d.week_date)=datepart(year,z.week_date)),0),
illness = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity f
WHERE (f.activity = 'Illness' OR f.activity = 'Short Term Disability' OR f.activity = 'Long Term Disability') AND uid = @uid AND datepart(year,f.week_date)=datepart(year,z.week_date)),0),
internal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity g
WHERE g.acttype = 'DecisionOne Work' AND uid = @uid AND datepart(year,g.week_date)=datepart(year,z.week_date)),0),
externl = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity h
WHERE h.acttype = 'Customer Work' AND uid = @uid AND datepart(year,h.week_date)=datepart(year,z.week_date)),0),
admin = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity i
WHERE i.acttype = 'Administrative' AND uid = @uid AND datepart(year,i.week_date)=datepart(year,z.week_date)),0),
juryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity l
WHERE l.activity = 'Jury Duty' AND uid = @uid AND datepart(year,l.week_date)=datepart(year,z.week_date)),0),
other = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity b
WHERE b.acttype = 'Other' AND b.uid = @uid AND datepart(year,b.week_date)=datepart(year,z.week_date)),0),
itwork = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity d
WHERE d.acttype = 'IT Work' AND d.uid = @uid AND datepart(year,d.week_date)=datepart(year,z.week_date)),0),
presaleswork = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity e
WHERE e.acttype = 'Presales Work' AND e.uid = @uid AND datepart(year,e.week_date)=datepart(year,z.week_date)),0),
nopay = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity f
WHERE (f.activity = 'No Pay' OR f.activity = 'Unauthorized Absence') AND f.uid = @uid AND datepart(year,f.week_date)=datepart(year,z.week_date) ),0),
milduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity g
WHERE g.activity = 'Military Duty' AND g.uid = @uid AND datepart(year,g.week_date)=datepart(year,z.week_date) ),0)
INTO #tempAct1
FROM it_tt_activity z
WHERE z.uid=@uid
GROUP BY z.uid, datepart(year,z.week_date)

SELECT a.totyear, a.uid, a.total, a.holiday, a.vacation, a.ato, a.mato, a.illness,
a.internal, a.externl, a.juryduty,
a.other, a.itwork, a.presaleswork, a.milduty, a.nopay,
admin=(admin-(holiday+vacation+ato+mato+illness+juryduty+milduty+nopay))
FROM #tempAct1 a

END
GO



Thanks for your thoughts!

 

[timmoser]

Please see: thread183-738184