Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    14

    Unanswered: gets inserted twice

    Gets inserted twice int TBL_NOTICES??

    ------------------------------------------------------------------------------------
    DECLARE @IDRess integer, @FromDate dateTime, @ToDate dateTime, @LateCounter integer

    SET @FromDate = GETDATE() - 30
    SET @ToDate = GETDATE()

    DECLARE LateCountCursor CURSOR
    FOR
    SELECT DISTINCT IDRess, COUNT(Late) AS LateCount
    FROM TBL_EXTERNAL_ENTRY
    WHERE (DateInvolved BETWEEN GETDATE() - 30 AND GETDATE()) AND (Late = 1)
    GROUP BY IDRess
    ORDER BY IDRess

    OPEN LateCountCursor



    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN

    FETCH NEXT FROM LateCountCursor
    INTO @IDRess, @LateCounter

    IF @LateCounter >= 1
    BEGIN
    DECLARE @late int, @dept int, @sup int, @poste int, @patron int
    SELECT @late = (SELECT ID_NOTICE FROM TBL_NOTICE_TYPE WHERE NoticeName = 'Late')
    DECLARE RessCursor CURSOR
    FOR
    SELECT IDDepartement, IDContremaitre, IDPoste, IDPatron FROM TBL_RESSOURCES WHERE IDInterne = @IDRess
    OPEN RessCursor
    IF @@FETCH_STATUS = 0
    BEGIN

    FETCH NEXT FROM RessCursor
    INTO @dept, @sup, @poste, @patron
    END
    CLOSE RessCursor
    DEALLOCATE RessCursor


    INSERT INTO TBL_NOTICES (IDInterne, IDDepartement, DateInfraction, DateAvis, IDMotif, NotesMotif, IDSuperieur, IDPoste, IDRedacteur, IDPatron, InscDossier, SuspSansSolde, Congediement, Autres)
    VALUES (@IDRess, @dept, @ToDate, @ToDate, @late, 'SQL Server LateCheck Job', @sup, @poste, 99999, @patron, 0, 0, 0, 0)


    DECLARE @msg varchar(100)
    SET @msg = 'Infraction de retard trop fréquents pour employee # ' + @IDRess
    EXEC [master].[dbo].xp_startmail
    EXEC [master].[dbo].xp_sendmail @recipients = 'gdo',
    @message = @msg,
    @subject = 'Infraction [RETARD]'
    END

    END
    CLOSE LateCountCursor
    DEALLOCATE LateCountCursor

    ------------------------------------------------------------------------------------


    Also, does anyone know about a good T-SQL Editor with wich I could step through the execution of a function...

    gdo
    Last edited by gdo; 07-31-02 at 16:46.

  2. #2
    Join Date
    May 2002
    Posts
    10
    gdo

    the query you have posted won't even write two records. It won't actually do anything.

    Your 'FETCH NEXT FROM LateCountCursor ' line comes after your 'WHILE @@FETCH_STATUS = 0' line, so the @@FetchStatus will be -1 (try print @@Fetch_Status to see). Because it is -1, the compiler will not even enter the while loop, therefore none of the code will be performed. Your 'FETCH NEXT FROM LateCountCursor ' line should come before the 'WHILE @@FETCH_STATUS = 0' line to ensure that the code in the while loop is actually implemented.

    As far as I know there is no tool that will allow you to step through TSQL code. SQL Server creates a query plan for the code before it is actually exectuted, and the sequence of events in that query plan does not neccesarily match the sequence of code.

  3. #3
    Join Date
    Jul 2002
    Location
    Silverton, Oregon
    Posts
    5
    Lwaker,
    Actually, @@FETCH_STATUS could be 0 depending on what
    the status of the last FETCH was before gdo's code block was
    started. Since @@FETCH_STATUS is global to all cursors in a
    connection, it might be valid upon entry to tis code block.

    I agree the condition checking of @@FETCH_STATUS needs to
    be changed as you mentioned. Gdo might get two inserts performed
    one call, then zero the next with the current setup.

  4. #4
    Join Date
    Jul 2002
    Posts
    14
    If I understand correctly... 'Fetch Next' is required before the while loop and also inside the while loop after the insert statement.


    thanks,



    By the way, about something else...

    we installed outlook on the server which has SQL Server in order to use SQL Mail.

    Server: Windows 2k and SQL Server 2k (They do not use Exchange Server, don't know if it has something something to do with it...)

    The address book has been imported in outlook but we do not see outlook in the combobox of SQL Mail Configuration?

    Any ideas?

    gdo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •