Internal SQL Server error

Question

Post reply

Internal SQL Server error

Teresita Castro

SSCommitted

Points: 1568
More actions
June 7, 2006 at 10:31 am

#366367

Hi !
I have the next query and I got the next error and I don't know why.
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.
select
CLA_EMPRESA, NOM_EMPRESA,
CLA_PUESTO, NOM_PUESTO,
count(cla_trab) as PerActual,
count ( case when noCurEmple=totCurEmp then 1 else 0 end ) as PerCurOblig,
sum ( isnull(totCalif,0)) as TotCalifPue
from
(
select CLA_EMPRESA, NOM_EMPRESA,
CLA_PUESTO, NOM_PUESTO, cla_trab,
count (cla_trab) noCurEmple,
sum ( case when fecha_caduocidad>=CONVERT(DATETIME, '2006-05-11 00:00:00', 102 )
                and CALIFICACION is not null
          then 1 else 0 end) totCurEmp,
sum( calificacion) totCalif
from
(
SELECT vwCurso_Emp.CLA_EMPRESA, vwCurso_Emp.NOM_EMPRESA,
  vwCurso_Emp.CLA_PUESTO, vwCurso_Emp.NOM_PUESTO,
vwCurso_Emp.cla_trab, RH_CURSO_TRAB.CALIFICACION,
        RH_CURSO_TRAB.FECHA_ULT_CAMBIO
  , cast ( vwCurso_Emp.nombre_corto as int) x,
case when cast ( vwCurso_Emp.nombre_corto as int) <> 0 then
fecha_ult_cambio+ (365*cast ( vwCurso_Emp.nombre_corto as int))
else
CONVERT(DATETIME, '2006-05-11 00:00:00', 102 )+1
end as fecha_caduocidad
FROM          vwCurso_Emp
LEFT OUTER JOIN
RH_CURSO_TRAB ON vwCurso_Emp.CLA_EMPRESA =   RH_CURSO_TRAB.CLA_EMPRESA AND
                 vwCurso_Emp.CLA_TRAB =   RH_CURSO_TRAB.CLA_TRAB AND
   vwCurso_Emp.CLA_CURSO =   RH_CURSO_TRAB.CLA_CURSO AND
  vwCurso_Emp.status_TRAB='A' AND
           RH_CURSO_TRAB.FECHA_ULT_CAMBIO <= CONVERT(DATETIME, '2006-05-11 00:00:00', 102 )AND
RH_CURSO_TRAB.folio_curso= ( select max(r.folio_curso) from RH_CURSO_TRAB r
                                where r.cla_empresa= RH_CURSO_TRAB.cla_empresa and
                                r.cla_trab= RH_CURSO_TRAB.cla_trab and
                                r.cla_curso= RH_CURSO_TRAB.cla_curso and
                               (r.fecha_ult_cambio < CONVERT(DATETIME, '2006-05-11 00:00:00', 102) ) )
WHERE   ( vwCurso_Emp.CLA_EMPRESA = 1) -- AND ( vwCurso_Emp.CLA_TRAB = 48377)
)tbEco
group by
CLA_EMPRESA, NOM_EMPRESA,
CLA_PUESTO, NOM_PUESTO, cla_trab
)tbFin
group by
CLA_EMPRESA, NOM_EMPRESA,
CLA_PUESTO, NOM_PUESTO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

I am not sure why you get this error.

But complex nested queries can degrade performance. It's difficult to maintain and easy to get wired errors.

It's better to use temp tables (table variables) to save the subquery results, and use the temp table to join with other tables in the main select.

Jason Adams SSC Eights! Points: 843 More actions · Answer 2

I have come across this issue in the past (pre sp4). From what I have been able to tell, it is unable to generate the execution plan due to the complexity of all the subqueries. Also have experienced the same issue with an indexed view that just had a left outer join and it was resolved onced I was able to remove that join. What SP are you using? I also believe you can re-write that query with out being as complex and if not, I would either use temp tables/table variables.

Teresita Castro SSCommitted Points: 1568 More actions · Answer 3

I read in http://support.microsoft.com/kb/830466/en-us that was a bug and will be correct if I use the SP4, but that solution did not works, so I will do what you said trying to restructure my query.

Thanks.