INSERT INTO table EXEC (stored_procedure) problems


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: INSERT INTO table EXEC (stored_procedure) problems

  1. #1
    Bob Hines Guest

    INSERT INTO table EXEC (stored_procedure) problems


    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

  2. #2
    Jason Rein Guest

    Re: INSERT INTO table EXEC (stored_procedure) problems


    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



  3. #3
    Bob Hines Guest

    Re: INSERT INTO table EXEC (stored_procedure) problems


    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

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center