Internal SQL Server error

16 Mar 2006 5:07 PM

Stanley

Hi, i have searched for my problem within this newsgroup but no found no
solution for it yet. I have this query which results in a "Internal SQL
Server error".

According to MSFT the latest SP should be installed. I did that but without
any results. This is my query:

SELECT P1.Country, P1.LaborCategory,
    SUM(CASE P1.FormulaId WHEN 371 THEN P1.Total ELSE null END) AS [Year 1],
    SUM(CASE P1.FormulaId WHEN 372 THEN P1.Total ELSE null END) AS [Year 2],
    SUM(CASE P1.FormulaId WHEN 373 THEN P1.Total ELSE null END) AS [Year 3],
    SUM(CASE P1.FormulaId WHEN 375 THEN P1.Total ELSE null END) AS [Year 4],
    SUM(CASE P1.FormulaId WHEN 376 THEN P1.Total ELSE null END) AS [Year 5],
    SUM(CASE P1.FormulaId WHEN 377 THEN P1.Total ELSE null END) AS [Year 6],
    SUM(CASE P1.FormulaId WHEN 378 THEN P1.Total ELSE null END) AS [Year 7],
    SUM(CASE P1.FormulaId WHEN 379 THEN P1.Total ELSE null END) AS [Year 8],
    SUM(CASE P1.FormulaId WHEN 380 THEN P1.Total ELSE null END) AS [Year 9],
    SUM(CASE P1.FormulaId WHEN 381 THEN P1.Total ELSE null END) AS [Year 10]
FROM(
    SELECT dbo.CalculationValues.BidId,
        dbo.CountryList.Country,
        dbo.JobTypes.LaborCategory,
        dbo.CalculationValues.FormulaId,
        SUM(CONVERT(decimal(22, 10), dbo.CalculationValues.[Value])) AS Total
    FROM         dbo.CalculationValues INNER JOIN
                     dbo.CountryList ON dbo.CalculationValues.CountryId =
dbo.CountryList.Id INNER JOIN
                    dbo.ProcessStepsT('4/12/2006') ProcessStepsT ON
dbo.CalculationValues.ProcessStepNumber = ProcessStepsT.ProcessStepNumber
INNER JOIN
                      dbo.JobTypes ON ProcessStepsT.JobTypeId =
dbo.JobTypes.Id
    WHERE     (dbo.CalculationValues.BidId = 1) AND
(dbo.CalculationValues.FormulaId = 371 OR
                      dbo.CalculationValues.FormulaId = 372 OR
                      dbo.CalculationValues.FormulaId = 373 OR
                      dbo.CalculationValues.FormulaId = 375 OR
                      dbo.CalculationValues.FormulaId = 376 OR
                      dbo.CalculationValues.FormulaId = 377 OR
                      dbo.CalculationValues.FormulaId = 378 OR
                      dbo.CalculationValues.FormulaId = 379 OR
                      dbo.CalculationValues.FormulaId = 380 OR
                      dbo.CalculationValues.FormulaId = 381)
    GROUP BY dbo.CalculationValues.BidId,
        dbo.CountryList.Country,
        dbo.JobTypes.LaborCategory,
        dbo.CalculationValues.FormulaId,
        dbo.CalculationValues.ProcessStepNumber,
                 ProcessStepsT.ProcessStepNumber) AS P1
GROUP BY P1.Country, P1.LaborCategory

The subquery (first select statement) runs just fine, but when i build a
second select statement around the first select statement, i get this error.

Anyone got an answer for this?

Thanks,

Stanley

17 Mar 2006 11:43 AM

John Bell

Hi

You don't give the error number.

A workaround would be to use a temporary table.

John

Show quoteHide quote

"Stanley" wrote:

> Hi, i have searched for my problem within this newsgroup but no found no
> solution for it yet. I have this query which results in a "Internal SQL
> Server error".
>
> According to MSFT the latest SP should be installed. I did that but without
> any results. This is my query:
>
> SELECT P1.Country, P1.LaborCategory,
>     SUM(CASE P1.FormulaId WHEN 371 THEN P1.Total ELSE null END) AS [Year 1],
>     SUM(CASE P1.FormulaId WHEN 372 THEN P1.Total ELSE null END) AS [Year 2],
>     SUM(CASE P1.FormulaId WHEN 373 THEN P1.Total ELSE null END) AS [Year 3],
>     SUM(CASE P1.FormulaId WHEN 375 THEN P1.Total ELSE null END) AS [Year 4],
>     SUM(CASE P1.FormulaId WHEN 376 THEN P1.Total ELSE null END) AS [Year 5],
>     SUM(CASE P1.FormulaId WHEN 377 THEN P1.Total ELSE null END) AS [Year 6],
>     SUM(CASE P1.FormulaId WHEN 378 THEN P1.Total ELSE null END) AS [Year 7],
>     SUM(CASE P1.FormulaId WHEN 379 THEN P1.Total ELSE null END) AS [Year 8],
>     SUM(CASE P1.FormulaId WHEN 380 THEN P1.Total ELSE null END) AS [Year 9],
>     SUM(CASE P1.FormulaId WHEN 381 THEN P1.Total ELSE null END) AS [Year 10]
> FROM(
>     SELECT dbo.CalculationValues.BidId,
>         dbo.CountryList.Country,
>         dbo.JobTypes.LaborCategory,
>         dbo.CalculationValues.FormulaId,
>         SUM(CONVERT(decimal(22, 10), dbo.CalculationValues.[Value])) AS Total
>     FROM         dbo.CalculationValues INNER JOIN
>                      dbo.CountryList ON dbo.CalculationValues.CountryId =
> dbo.CountryList.Id INNER JOIN
>                     dbo.ProcessStepsT('4/12/2006') ProcessStepsT ON
> dbo.CalculationValues.ProcessStepNumber = ProcessStepsT.ProcessStepNumber
> INNER JOIN
>                       dbo.JobTypes ON ProcessStepsT.JobTypeId =
> dbo.JobTypes.Id
>     WHERE     (dbo.CalculationValues.BidId = 1) AND
> (dbo.CalculationValues.FormulaId = 371 OR
>                       dbo.CalculationValues.FormulaId = 372 OR
>                       dbo.CalculationValues.FormulaId = 373 OR
>                       dbo.CalculationValues.FormulaId = 375 OR
>                       dbo.CalculationValues.FormulaId = 376 OR
>                       dbo.CalculationValues.FormulaId = 377 OR
>                       dbo.CalculationValues.FormulaId = 378 OR
>                       dbo.CalculationValues.FormulaId = 379 OR
>                       dbo.CalculationValues.FormulaId = 380 OR
>                       dbo.CalculationValues.FormulaId = 381)
>     GROUP BY dbo.CalculationValues.BidId,
>         dbo.CountryList.Country,
>         dbo.JobTypes.LaborCategory,
>         dbo.CalculationValues.FormulaId,
>         dbo.CalculationValues.ProcessStepNumber,
>                  ProcessStepsT.ProcessStepNumber) AS P1
> GROUP BY P1.Country, P1.LaborCategory
>
>
> The subquery (first select statement) runs just fine, but when i build a
> second select statement around the first select statement, i get this error.
>
> Anyone got an answer for this?
>
> Thanks,
>
> Stanley

17 Mar 2006 1:16 PM

Stanley

John, thnx for your response. The error number is:

Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

I am using this SP as source for a SQL Report, so i don't think a temp table
will work. I will try to put the inner select query in a view and than do the
second select on that view.

Any other solutions are welcome.

Thanks,

STanley

Show quoteHide quote

"John Bell" wrote:

> Hi
>
> You don't give the error number.
>
> A workaround would be to use a temporary table.
>
> John
>
> "Stanley" wrote:
>
> > Hi, i have searched for my problem within this newsgroup but no found no
> > solution for it yet. I have this query which results in a "Internal SQL
> > Server error".
> >
> > According to MSFT the latest SP should be installed. I did that but without
> > any results. This is my query:
> >
> > SELECT P1.Country, P1.LaborCategory,
> >     SUM(CASE P1.FormulaId WHEN 371 THEN P1.Total ELSE null END) AS [Year 1],
> >     SUM(CASE P1.FormulaId WHEN 372 THEN P1.Total ELSE null END) AS [Year 2],
> >     SUM(CASE P1.FormulaId WHEN 373 THEN P1.Total ELSE null END) AS [Year 3],
> >     SUM(CASE P1.FormulaId WHEN 375 THEN P1.Total ELSE null END) AS [Year 4],
> >     SUM(CASE P1.FormulaId WHEN 376 THEN P1.Total ELSE null END) AS [Year 5],
> >     SUM(CASE P1.FormulaId WHEN 377 THEN P1.Total ELSE null END) AS [Year 6],
> >     SUM(CASE P1.FormulaId WHEN 378 THEN P1.Total ELSE null END) AS [Year 7],
> >     SUM(CASE P1.FormulaId WHEN 379 THEN P1.Total ELSE null END) AS [Year 8],
> >     SUM(CASE P1.FormulaId WHEN 380 THEN P1.Total ELSE null END) AS [Year 9],
> >     SUM(CASE P1.FormulaId WHEN 381 THEN P1.Total ELSE null END) AS [Year 10]
> > FROM(
> >     SELECT dbo.CalculationValues.BidId,
> >         dbo.CountryList.Country,
> >         dbo.JobTypes.LaborCategory,
> >         dbo.CalculationValues.FormulaId,
> >         SUM(CONVERT(decimal(22, 10), dbo.CalculationValues.[Value])) AS Total
> >     FROM         dbo.CalculationValues INNER JOIN
> >                      dbo.CountryList ON dbo.CalculationValues.CountryId =
> > dbo.CountryList.Id INNER JOIN
> >                     dbo.ProcessStepsT('4/12/2006') ProcessStepsT ON
> > dbo.CalculationValues.ProcessStepNumber = ProcessStepsT.ProcessStepNumber
> > INNER JOIN
> >                       dbo.JobTypes ON ProcessStepsT.JobTypeId =
> > dbo.JobTypes.Id
> >     WHERE     (dbo.CalculationValues.BidId = 1) AND
> > (dbo.CalculationValues.FormulaId = 371 OR
> >                       dbo.CalculationValues.FormulaId = 372 OR
> >                       dbo.CalculationValues.FormulaId = 373 OR
> >                       dbo.CalculationValues.FormulaId = 375 OR
> >                       dbo.CalculationValues.FormulaId = 376 OR
> >                       dbo.CalculationValues.FormulaId = 377 OR
> >                       dbo.CalculationValues.FormulaId = 378 OR
> >                       dbo.CalculationValues.FormulaId = 379 OR
> >                       dbo.CalculationValues.FormulaId = 380 OR
> >                       dbo.CalculationValues.FormulaId = 381)
> >     GROUP BY dbo.CalculationValues.BidId,
> >         dbo.CountryList.Country,
> >         dbo.JobTypes.LaborCategory,
> >         dbo.CalculationValues.FormulaId,
> >         dbo.CalculationValues.ProcessStepNumber,
> >                  ProcessStepsT.ProcessStepNumber) AS P1
> > GROUP BY P1.Country, P1.LaborCategory
> >
> >
> > The subquery (first select statement) runs just fine, but when i build a
> > second select statement around the first select statement, i get this error.
> >
> > Anyone got an answer for this?
> >
> > Thanks,
> >
> > Stanley

17 Mar 2006 1:58 PM

John Bell

Hi

Looking at the things that generate this error number
http://search.msdn.microsoft.com/search/results.aspx?View=msdn&p=1&s=1&c=10&st=b&qu=8624&na=32&cm=512

The convert statement may be an issue or possibly the table function (but
you didn't post the code!). I don't think the temporary tables would be an
issue, your report will work off the resultset returned by the procedure; so
if you use SET NOCOUNT ON at the start of the procedure, I think it will
probably work. You could try defining a view for the subquery and/or
replacing the table function with a view or subquery.

John

Show quoteHide quote

"Stanley" wrote:

> John, thnx for your response. The error number is:
>
> Server: Msg 8624, Level 16, State 3, Line 1
> Internal SQL Server error.
>
> I am using this SP as source for a SQL Report, so i don't think a temp table
> will work. I will try to put the inner select query in a view and than do the
> second select on that view.
>
> Any other solutions are welcome.
>
> Thanks,
>
> STanley
>
>
> "John Bell" wrote:
>
> > Hi
> >
> > You don't give the error number.
> >
> > A workaround would be to use a temporary table.
> >
> > John
> >
> > "Stanley" wrote:
> >
> > > Hi, i have searched for my problem within this newsgroup but no found no
> > > solution for it yet. I have this query which results in a "Internal SQL
> > > Server error".
> > >
> > > According to MSFT the latest SP should be installed. I did that but without
> > > any results. This is my query:
> > >
> > > SELECT P1.Country, P1.LaborCategory,
> > >     SUM(CASE P1.FormulaId WHEN 371 THEN P1.Total ELSE null END) AS [Year 1],
> > >     SUM(CASE P1.FormulaId WHEN 372 THEN P1.Total ELSE null END) AS [Year 2],
> > >     SUM(CASE P1.FormulaId WHEN 373 THEN P1.Total ELSE null END) AS [Year 3],
> > >     SUM(CASE P1.FormulaId WHEN 375 THEN P1.Total ELSE null END) AS [Year 4],
> > >     SUM(CASE P1.FormulaId WHEN 376 THEN P1.Total ELSE null END) AS [Year 5],
> > >     SUM(CASE P1.FormulaId WHEN 377 THEN P1.Total ELSE null END) AS [Year 6],
> > >     SUM(CASE P1.FormulaId WHEN 378 THEN P1.Total ELSE null END) AS [Year 7],
> > >     SUM(CASE P1.FormulaId WHEN 379 THEN P1.Total ELSE null END) AS [Year 8],
> > >     SUM(CASE P1.FormulaId WHEN 380 THEN P1.Total ELSE null END) AS [Year 9],
> > >     SUM(CASE P1.FormulaId WHEN 381 THEN P1.Total ELSE null END) AS [Year 10]
> > > FROM(
> > >     SELECT dbo.CalculationValues.BidId,
> > >         dbo.CountryList.Country,
> > >         dbo.JobTypes.LaborCategory,
> > >         dbo.CalculationValues.FormulaId,
> > >         SUM(CONVERT(decimal(22, 10), dbo.CalculationValues.[Value])) AS Total
> > >     FROM         dbo.CalculationValues INNER JOIN
> > >                      dbo.CountryList ON dbo.CalculationValues.CountryId =
> > > dbo.CountryList.Id INNER JOIN
> > >                     dbo.ProcessStepsT('4/12/2006') ProcessStepsT ON
> > > dbo.CalculationValues.ProcessStepNumber = ProcessStepsT.ProcessStepNumber
> > > INNER JOIN
> > >                       dbo.JobTypes ON ProcessStepsT.JobTypeId =
> > > dbo.JobTypes.Id
> > >     WHERE     (dbo.CalculationValues.BidId = 1) AND
> > > (dbo.CalculationValues.FormulaId = 371 OR
> > >                       dbo.CalculationValues.FormulaId = 372 OR
> > >                       dbo.CalculationValues.FormulaId = 373 OR
> > >                       dbo.CalculationValues.FormulaId = 375 OR
> > >                       dbo.CalculationValues.FormulaId = 376 OR
> > >                       dbo.CalculationValues.FormulaId = 377 OR
> > >                       dbo.CalculationValues.FormulaId = 378 OR
> > >                       dbo.CalculationValues.FormulaId = 379 OR
> > >                       dbo.CalculationValues.FormulaId = 380 OR
> > >                       dbo.CalculationValues.FormulaId = 381)
> > >     GROUP BY dbo.CalculationValues.BidId,
> > >         dbo.CountryList.Country,
> > >         dbo.JobTypes.LaborCategory,
> > >         dbo.CalculationValues.FormulaId,
> > >         dbo.CalculationValues.ProcessStepNumber,
> > >                  ProcessStepsT.ProcessStepNumber) AS P1
> > > GROUP BY P1.Country, P1.LaborCategory
> > >
> > >
> > > The subquery (first select statement) runs just fine, but when i build a
> > > second select statement around the first select statement, i get this error.
> > >
> > > Anyone got an answer for this?
> > >
> > > Thanks,
> > >
> > > Stanley