I have a MSSQL 6.5 stored procedure that I wish to call from MS Access 97/2000
with a pass-thru query. I have done this successfully dozens of times with
other procedures. However, with this particular stored procedure, Access
reports that no records were returned. If I call the stored procedure from
Enterprise Manager's query window or ISQL/w, several records are returned
with data as expected. I have played with the code and have determined that
the problem arises when I execute another stored procedure to populate a
temporary table. I have included the code for both procedures below. (Sorry
for the length.) Any suggestions on what the issue may be would be greatly
appreciated.

This is the problem procedure:
CREATE PROCEDURE cca_CDPackoutCnts
(
@StartDate DATETIME,
@EndDate DATETIME
)

AS

/* Working variables */
DECLARE @CmdParams VARCHAR(255)
DECLARE @SystemName VARCHAR(50)
DECLARE @PeriodStart DATETIME
DECLARE @PeriodEnd DATETIME
--DECLARE @DebugMsg VARCHAR(255)

/* Cursor variables */
DECLARE @Date DATETIME
DECLARE @ShiftIndex TINYINT
DECLARE @Shift TINYINT
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
DECLARE @Duration INT

SELECT @SystemName = 'CDPackoutSaw'

/* Setup parameters for cca_SystemShifts stored procedure */
SELECT @CmdParams = "'" + @SystemName +
"', '" + CONVERT(VARCHAR(10), @StartDate, 101) +
"', '" + CONVERT(VARCHAR(10), @EndDate, 101) +
"'"
--PRINT @CmdParams

CREATE TABLE #ShiftTimes(
Date DATETIME NULL,
ShiftIndex TINYINT NULL,
Shift TINYINT NULL,
StartDateTime DATETIME NULL,
EndDateTime DATETIME NULL,
Duration INT NULL,
)

/*
INSERT INTO #ShiftTimes
EXECUTE ('wwAdmin.cca_SystemShifts ' + @CmdParams)
*/

--SELECT * FROM #ShiftTimes

INSERT INTO #ShiftTimes VALUES ('4/3/2000', 0, 3, '4/2/2000 10:30pm', '4/3/2000
6:30am', 480)
INSERT INTO #ShiftTimes VALUES ('4/3/2000', 1, 1, '4/3/2000 6:30am', '4/3/2000
2:30pm', 480)
INSERT INTO #ShiftTimes VALUES ('4/3/2000', 2, 2, '4/3/2000 2:30pm', '4/3/2000
10:30pm', 480)

/* Create 2 temporary tables for analog history data */
CREATE TABLE #TempAnalogHistory1

(DateTime DATETIME NULL,


CD_Saw_In_CntA INT NULL,
CD_Saw_Out_CntA INT NULL,
CD_Wrap_In_CntA INT NULL,
CD_Wrap_Out_CntA INT NULL,
CD_Shrink_Out_CntA INT NULL
)

CREATE TABLE #TempAnalogHistory3
(Day DATETIME NULL,
ShiftIndex TINYINT NULL,
Shift TINYINT NULL,
Hour TINYINT NULL,
DateTime DATETIME NULL,
CD_Saw_In_CntA INT NULL,
CD_Saw_Out_CntA INT NULL,
CD_Wrap_In_CntA INT NULL,
CD_Wrap_Out_CntA INT NULL,
CD_Shrink_Out_CntA INT NULL
)



/* Declare and open cursor used to get shift info */
SELECT @CmdParams = 'DECLARE ShiftCursor CURSOR FOR ' +
'SELECT Date, ShiftIndex, Shift, StartDateTime, EndDateTime,
Duration ' +
'FROM #ShiftTimes FOR READ ONLY'
EXECUTE (@CmdParams)


OPEN ShiftCursor

/* Initialize the shift info */
FETCH NEXT FROM ShiftCursor INTO
@Date, @ShiftIndex, @Shift, @StartDateTime, @EndDateTime, @Duration


/* Process each shift */
WHILE @@FETCH_STATUS = 0 BEGIN


--SELECT @DebugMsg = CONVERT(VARCHAR(20), @StartDateTime, 109) + ' -
' + CONVERT(VARCHAR(20), @EndDateTime, 109)
--PRINT @DebugMsg

/* Construct parameters to get first set of data */
SELECT @CmdParams = "'" + CONVERT(VARCHAR(20), @StartDateTime) +
"', '" + CONVERT(VARCHAR(20), DATEADD(mi, 1, @EndDateTime))
+
"', 60000, CD_Saw_In_CntA, CD_Saw_Out_CntA, CD_Wrap_In_CntA,
" +
"CD_Wrap_Out_CntA, CD_Shrink_Out_CntA"
--PRINT @CmdParams

/**** This is the troublesome INSERT/EXECUTE statement ****/
INSERT INTO #TempAnalogHistory1
EXECUTE ("master..xp_AnalogWideHistory " + @cmdparams)

SELECT DateTime = DATEADD(mi, -1, DateTime),
CD_Saw_In_CntA,
CD_Saw_Out_CntA,
CD_Wrap_In_CntA,
CD_Wrap_Out_CntA,
CD_Shrink_Out_CntA
INTO #TempAnalogHistory2
FROM #TempAnalogHistory1

INSERT INTO #TempAnalogHistory3
SELECT Day = @Date,
ShiftIndex = @ShiftIndex,
Shift = @Shift,
Hour = ((DATEDIFF(mi, @StartDateTime, t1.DateTime) - 1) /
60) + 1,
t1.DateTime,
CD_Saw_In_CntA = CASE
WHEN t2.CD_Saw_In_CntA >= t1.CD_Saw_In_CntA
THEN
t2.CD_Saw_In_CntA - t1.CD_Saw_In_CntA
ELSE
t2.CD_Saw_In_CntA

END,

CD_Saw_Out_CntA = CASE
WHEN t2.CD_Saw_Out_CntA >= t1.CD_Saw_Out_CntA
THEN
t2.CD_Saw_Out_CntA - t1.CD_Saw_Out_CntA
ELSE

t2.CD_Saw_Out_CntA
END,
CD_Wrap_In_CntA = CASE
WHEN t2.CD_Wrap_In_CntA >= t1.CD_Wrap_In_CntA
THEN
t2.CD_Wrap_In_CntA - t1.CD_Wrap_In_CntA
ELSE
t2.CD_Wrap_In_CntA
END,
CD_Wrap_Out_CntA = CASE
WHEN t2.CD_Wrap_Out_CntA >= t1.CD_Wrap_Out_CntA
THEN
t2.CD_Wrap_Out_CntA - t1.CD_Wrap_Out_CntA
ELSE
t2.CD_Wrap_Out_CntA
END,
CD_Shrink_Out_CntA = CASE
WHEN t2.CD_Shrink_Out_CntA >= t1.CD_Shrink_Out_CntA
THEN
t2.CD_Shrink_Out_CntA - t1.CD_Shrink_Out_CntA
ELSE
t2.CD_Shrink_Out_CntA
END
FROM #TempAnalogHistory1 t1, #TempAnalogHistory2 t2
WHERE t1.DateTime = t2.DateTime



TRUNCATE TABLE #TempAnalogHistory1
DROP TABLE #TempAnalogHistory2

/* Get info for next shift */
FETCH NEXT FROM ShiftCursor INTO
@Date, @ShiftIndex, @Shift, @StartDateTime, @EndDateTime, @Duration



END

SELECT Day,
ShiftIndex,
Shift,
Hour,
SUM(CD_Saw_In_CntA) AS CD_Saw_In_CntA,
SUM(CD_Saw_Out_CntA) AS CD_Saw_Out_Cnt,
SUM(CD_Wrap_In_CntA) As CD_Wrap_In_Cnt,
SUM(CD_Wrap_Out_CntA) As CD_Wrap_Out_Cnt,
SUM(CD_Shrink_Out_CntA) As CD_Shrink_Out_Cnt
FROM #TempAnalogHistory3
GROUP BY Day, ShiftIndex, Shift, Hour


/* Clean up */
DROP TABLE #TempAnalogHistory1
DROP TABLE #TempAnalogHistory3

DROP TABLE #ShiftTimes

DEALLOCATE ShiftCursor


This is the stored procedure used to populate the temp table:

CREATE PROCEDURE cca_SystemShifts
(
@MachineName VARCHAR(50)
@StartDate DATETIME,
@EndDate DATETIME
)

AS

/* Loop related DECLARES */
DECLARE @CurrentDate DATETIME
DECLARE @CurrentStartShift TINYINT
DECLARE @CurrentEndShift TINYINT
DECLARE @CurrentHr REAL
DECLARE @CurrentMin REAL

/* Output variable DECLARES */
DECLARE @ShiftNumber TINYINT
DECLARE @ShiftStartDateTime DATETIME
DECLARE @ShiftEndDateTime DATETIME

/* Cursor related DECLARES */
DECLARE @StartHr1 REAL
DECLARE @StartHr2 REAL
DECLARE @StartHr3 REAL
DECLARE @StartMin1 REAL
DECLARE @StartMin2 REAL
DECLARE @StartMin3 REAL
DECLARE @StartingShift TINYINT
DECLARE @ShiftsPerDay INT

/* Turn off counts */
SET NOCOUNT ON

/* Make sure @StartDate and @EndDate have no time component */
SELECT @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR(2), DATEPART(mm, @StartDate))
+ "/" +
CONVERT(VARCHAR(2), DATEPART(dd, @StartDate))
+ "/" +
CONVERT(VARCHAR(4), DATEPART(yy, @StartDate))
)

SELECT @EndDate = CONVERT(DATETIME, CONVERT(VARCHAR(2), DATEPART(mm, @EndDate))
+ "/" +
CONVERT(VARCHAR(2), DATEPART(dd, @EndDate))
+ "/" +
CONVERT(VARCHAR(4), DATEPART(yy, @EndDate))
)

/* Declare and open cursor used to get shift info */
DECLARE SystemCursor CURSOR FOR
SELECT StartHr1,
StartHr2,
StartHr3,
StartMin1,
StartMin2,
StartMin3,
StartingShift,
ShiftsPerDay
FROM System2
WHERE Name = @MachineName
FOR READ ONLY

OPEN SystemCursor

/* Get the shift info */
FETCH NEXT FROM SystemCursor INTO @StartHr1, @StartHr2, @StartHr3,
@StartMin1, @StartMin2, @StartMin3,
@StartingShift, @ShiftsPerDay

/* If no data returned, clean up cursor and exit */
IF @@FETCH_STATUS <> 0 BEGIN
DEALLOCATE SystemCursor
RETURN
END

/* Create temporary table for shift info */
CREATE TABLE #Shifts(
Shift TINYINT NULL,
StartDateTime DATETIME NULL,
EndDateTime DATETIME NULL
)

/* Initialize @CurrentDate */
SELECT @CurrentDate = @StartDate

WHILE @CurrentDate <= @EndDate BEGIN

/* Initialize shift count */
SELECT @CurrentStartShift = 0
SELECT @ShiftNumber = @StartingShift

WHILE @CurrentStartShift < @ShiftsPerDay BEGIN

/* Determine ending shift */
SELECT @CurrentEndShift = (@CurrentStartShift + 1) % @ShiftsPerDay

/* Calculate shift starting time */
IF @CurrentStartShift = 0 BEGIN
SELECT @ShiftStartDateTime = DATEADD(hh, @StartHr1, @CurrentDate)
SELECT @ShiftStartDateTime = DATEADD(mi, @StartMin1, @ShiftStartDateTime)
END
IF @CurrentStartShift = 1 BEGIN
SELECT @ShiftStartDateTime = DATEADD(hh, @StartHr2, @CurrentDate)
SELECT @ShiftStartDateTime = DATEADD(mi, @StartMin2, @ShiftStartDateTime)
END
IF @CurrentStartShift = 2 BEGIN
SELECT @ShiftStartDateTime = DATEADD(hh, @StartHr3, @CurrentDate)
SELECT @ShiftStartDateTime = DATEADD(mi, @StartMin3, @ShiftStartDateTime)
END

/* Calculate shift ending time */
IF @CurrentEndShift = 0 BEGIN
SELECT @ShiftEndDateTime = DATEADD(hh, @StartHr1, @CurrentDate)
SELECT @ShiftEndDateTime = DATEADD(mi, @StartMin1, @ShiftEndDateTime)
END
IF @CurrentEndShift = 1 BEGIN
SELECT @ShiftEndDateTime = DATEADD(hh, @StartHr2, @CurrentDate)
SELECT @ShiftEndDateTime = DATEADD(mi, @StartMin2, @ShiftEndDateTime)
END
IF @CurrentEndShift = 2 BEGIN
SELECT @ShiftEndDateTime = DATEADD(hh, @StartHr3, @CurrentDate)
SELECT @ShiftEndDateTime = DATEADD(mi, @StartMin3, @ShiftEndDateTime)
END

/* If last shift of the day, have to add a day to shift ending time
*/
IF @CurrentStartShift = (@ShiftsPerDay - 1)
SELECT @ShiftEndDateTime = DATEADD(dd, 1, @ShiftEndDateTime)

INSERT INTO #Shifts VALUES(@ShiftNumber,
@ShiftStartDateTime,
@ShiftEndDateTime
)

/* Increment shift count */
SELECT @CurrentStartShift = @CurrentStartShift + 1

/* Compute next shift number */
SELECT @ShiftNumber = (@ShiftNumber + 1) % @ShiftsPerDay

END

/* Go to the next day */
SELECT @CurrentDate = DATEADD(dd, 1, @CurrentDate)

END

SELECT * FROM #Shifts

/* Clean up */
DROP TABLE #Shifts
DEALLOCATE SystemCursor

RETURN