INSERT INTO table EXEC (stored_procedure) problems

[Bob Hines]

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

[Jason Rein]

I have had similar problems. The issue I have found is that the row count
displays can cause problems. Try putting the following at the top of the
stored procedure:

SET NOCOUNT ON

This will hide the row count displays.

I hope this helps.

- Jason Rein

"Bob Hines" <bobhi@pectech.com> wrote:
>
>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

[Bob Hines]

BINGO! Thanks a million...

"Jason Rein" <JRein@Oakwoodsys.com> wrote:
>
>I have had similar problems. The issue I have found is that the row count
>displays can cause problems. Try putting the following at the top of the
>stored procedure:
>
> SET NOCOUNT ON
>
>This will hide the row count displays.
>
>I hope this helps.
>
>- Jason Rein
>
>"Bob Hines" <bobhi@pectech.com> wrote:
>>
>>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
>