Internal SQL Server error when using variable

[Carl Ganz]

In the code below I get an Internal SQL Server error in Query
Analyzer. Ultimately I want to get thos code working in a function.

DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SET @Comp = (SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate)
But this code runs fine:
DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate
The only difference is that here I'm not setting @Comp to the return
value of the SELECT. This code also runs fine when I sum other
columns. Any ideas?

[Muhd]

I didn't actually try this but it looks like you want this instead:

SELECT @Comp = SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate

Instead of trying to select into a set statement you simply use the select
statement to set your own variable.

Best,
Muhd
"Carl Ganz" <se************@verizon.net> wrote in message
news:a1**************************@posting.google.c om...

In the code below I get an Internal SQL Server error in Query
Analyzer. Ultimately I want to get thos code working in a function.

DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SET @Comp = (SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate)
But this code runs fine:
DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate
The only difference is that here I'm not setting @Comp to the return
value of the SELECT. This code also runs fine when I sum other
columns. Any ideas?

[Carl Ganz]

Thanks for the feedback. Your solution did work. Microsoft tech
support believes my initial problem to be a bug and suggested
INSERTING into an in-memory table.

Thanks

Carl Ganz

"Muhd" <ea*@joes.com> wrote in message news:<pQ_5d.546211$gE.52683@pd7tw3no>...

I didn't actually try this but it looks like you want this instead:

SELECT @Comp = SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate

Instead of trying to select into a set statement you simply use the select
statement to set your own variable.

Best,
Muhd
"Carl Ganz" <se************@verizon.net> wrote in message
news:a1**************************@posting.google.c om...

In the code below I get an Internal SQL Server error in Query
Analyzer. Ultimately I want to get thos code working in a function.

DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SET @Comp = (SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate)
But this code runs fine:
DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate
The only difference is that here I'm not setting @Comp to the return
value of the SELECT. This code also runs fine when I sum other
columns. Any ideas?