A Real Sql Server 2005 Bug! A Real Sql Server Bug!

A growing Sql Server 2005 database performs several hours of updates each
night.  This particular region of code has run fine for over a year.  Now we
are getting the folllowing message every few nights causing our processing t
o
abort:

Msg 8630, Level 17, State 52, Procedure  sp_dts_post_activity
, Line 150
Internal Query Processor Error: The query processor encountered an unexpecte
d
error during execution.

This is a simplified version of the query from line 150:

select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate  < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber

When this simplified query is run from the management studio, it fails about
10-20% of the time.

Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table

Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition

Help!

Mike

Mike  wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night.  This particular region of code has run fine for over a year.  Now 
we
> are getting the folllowing message every few nights causing our processing
 to
> abort:
>
> Msg 8630, Level 17, State 52, Procedure  sp_dts_post_activity
, Line 150
> Internal Query Processor Error: The query processor encountered an unexpec
ted
> error during execution.
>
> This is a simplified version of the query from line 150:
>
> select
>     p.PeriodStartDate,
>     p.PeriodType,
>     p.ActivityUserNumber,
>     p.AppNumber,
>     max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
>     max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
>     max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
>     max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
>     max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
>     max( case when ActivityType = 'FUNDED' then 1 else 0 end )
>   from Activity a, xxxPeriod p
>  where a.ActivityDate >= p.PeriodStartDate
>    and a.ActivityDate  < p.PeriodEndDate
>    and a.ActivityUserNumber = p.ActivityUserNumber
>    and a.AppNumber = p.AppNumber
>    and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
>  group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumb
er
>
> When this simplified query is run from the management studio, it fails abo
ut
> 10-20% of the time.
>
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
>   - Removing the group by and max()
>   - Removing one or more max statements
>   - Hard-code the list of activity types in place of the sub-select from
> Lookup table
>
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
>
> Help!
>
> Mike
>
>
>
>
>
>
>

I would suspect a TEMPDB problem.  Lack of space?  Autogrow timeout?


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Mike, please contact Microsoft product support.

Thanks,

Leo



"Mike" <Mike@discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF- FC5A8D309F47@microso
ft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night.  This particular region of code has run fine for over a year.  Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
>
> Msg 8630, Level 17, State 52, Procedure  sp_dts_post_activity
, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
>
> This is a simplified version of the query from line 150:
>
> select
>    p.PeriodStartDate,
>    p.PeriodType,
>    p.ActivityUserNumber,
>    p.AppNumber,
>    max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
>    max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
>    max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
>    max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
>    max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
>    max( case when ActivityType = 'FUNDED' then 1 else 0 end )
>  from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
>   and a.ActivityDate  < p.PeriodEndDate
>   and a.ActivityUserNumber = p.ActivityUserNumber
>   and a.AppNumber = p.AppNumber
>   and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
>
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
>
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
>  - Removing the group by and max()
>  - Removing one or more max statements
>  - Hard-code the list of activity types in place of the sub-select from
> Lookup table
>
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
>
> Help!
>
> Mike
>
>
>
>
>
>
>