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 frquents 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...
gdogdo
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.|||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.|||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
