Job Does Not Execute Correctly

Hi everyone,
I have a management job which runs every minute automatically. However recently I discovered that the result was not executed correctly even the job history tells me it runs successfully. On the other hand, the result was executed correctly (i.e. what I expect) when I try to start the job manually.
Any idea what issue does it related to? I doubt if it is related to any security context but cannot prove it yet. Thanks for any idea.Hi,
As you say u execute job every 1 minute, What does the job
do and what is the expected time period of the job
completion.
Regards
Thirumal
www.thirumal.com
>--Original Message--
>Hi everyone,
>I have a management job which runs every minute
automatically. However recently I discovered that the
result was not executed correctly even the job history
tells me it runs successfully. On the other hand, the
result was executed correctly (i.e. what I expect) when I
try to start the job manually.
>Any idea what issue does it related to? I doubt if it is
related to any security context but cannot prove it yet.
Thanks for any idea.
>.
>|||Thirumal,
Thanks for your reply. The job executes within just few seconds, wont' be too long.
The job retreives several records from tables and then insert records into another table based on a if-else condition.
The code is like this:
DECLARE @.thisMin varchar(25)
DECLARE @.table_no varchar(2), @.table_tmp varchar(2)
SET @.thisMin = GETDATE()
SET @.table_no = DATEDIFF(dd, ''01/01/01'', @.thisMin) % 36
SET @.table_tmp = DATEDIFF(dd, ''01/01/01'', DATEADD(dd, -1, @.thisMin)) % 36
EXEC(''
DECLARE temp_cur CURSOR FOR
SELECT DISTINCT alertId FROM z_EventHistoryD''+@.table_no+'' WHERE
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0 OR
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
GROUP BY alertId, severitySortOrder
HAVING COUNT(alertId) = 1
DECLARE @.alertId int, @.thisSSO int, @.lastSSO int, @.auditTime datetime
OPEN temp_cur
FETCH NEXT FROM temp_cur INTO @.alertId
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.thisSSO = NULL
SET @.lastSSO = NULL
SET @.auditTime = NULL
IF DATEPART(hh, '''+@.thisMin+''')=0 AND DATEPART(mi, '''+@.thisMin+''')=0
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_tmp+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
ELSE
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
SELECT @.thisSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0
IF (@.lastSSO IS NULL)
--PRINT ''''-- NEW EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, NULL, @.thisSSO, 1, @.auditTime)
ELSE
IF (@.thisSSO IS NULL)
--PRINT ''''-- ENDING EVENT''''
INSERT INTO alertAudit
SELECT alertId, @.lastSSO, NULL, -1, clearTime FROM alertHistory WHERE alertId = @.alertId
ELSE
IF (@.thisSSO <> @.lastSSO)
--PRINT ''''-- CHANGE SEVERITY EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, @.lastSSO, @.thisSSO, 2, @.auditTime)
ELSE
IF (@.thisSSO = @.lastSSO)
PRINT ''''-- UNCHANGED SEVERITY''''
ELSE
PRINT ''''-- UNKNOWN ERROR''''
/*--DEBUG
PRINT @.alertId
PRINT @.auditTime
PRINT @.lastSSO
PRINT @.thisSSO
--DEBUG*/
FETCH NEXT FROM temp_cur INTO @.alertId
END
CLOSE temp_cur
DEALLOCATE temp_cur
'')
Thanks again
"Thirumal" wrote:
> Hi,
> As you say u execute job every 1 minute, What does the job
> do and what is the expected time period of the job
> completion.
> Regards
> Thirumal
> www.thirumal.com
> >--Original Message--
> >Hi everyone,
> >
> >I have a management job which runs every minute
> automatically. However recently I discovered that the
> result was not executed correctly even the job history
> tells me it runs successfully. On the other hand, the
> result was executed correctly (i.e. what I expect) when I
> try to start the job manually.
> >
> >Any idea what issue does it related to? I doubt if it is
> related to any security context but cannot prove it yet.
> Thanks for any idea.
> >.
> >
>