Problem on a Job submitted by an alert

Question

Problem on a Job submitted by an alert

gael80

Old Hand

Points: 318
More actions
October 19, 2005 at 3:35 am

#102873

Hello all of you, I've a problem with a SQL Server job. In my application, a user's action fires a trigger. This trigger produces an error/alert and this alert submits a sql server job.
Everything works fine, except when the trigger is fired as the sql server job is already running (from a previous alert).
The sql server job does not execute a second time.
Anybody has an idea/solution ?
Gael80

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Not knowing exactly what the Trigger is doing - can you check for existence, (it sounds as if the Trigger is altering data as well as sending an error/alert) before running your job?

I wasn't born stupid - I had to study.

gael80 Old Hand Points: 318 More actions · Answer 2

Here's the ddl text for my trigger

TRIGGER mytrigger ON dbo.mytable1 FOR INSERT

AS

declare @numcourant integer

declare @dat datetime

BEGIN

set @numcourant=(select numdem from inserted)

set @dat = getdate()

insert into dbo.mytable2 (NUDEM , DTCRA) values (@numcourant , @dat )

raiserror (80000 , 10 , 1)

END

The sql server job does a lot of things, and before the end of the job, makes an update on the row inserted by the trigger in table mytable2 .

So when at the second time the trigger is fired, the insert is done but the sql server job doesn't run (and the row is not updated) for a second time.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I am a little lost here. The Trigger on the job works every time, but when the job is run twice, the Trigger runs fine and a new record is Inserted into MyTable, but the rest of the job fails to process?

Is there some type of error trapping in the job?

Someone else may be grasping this and can answer you, but I think I need more information. Can you post parts if not all of the job, including any error trapping and/or references to MyTable (please include the section that fires the Trigger and what happens to the table that the Trigger is set upon..).

Thanks.

I wasn't born stupid - I had to study.

gael80 Old Hand Points: 318 More actions · Answer 4

A user , Bill for exemple, makes an insert in table mytable1. The trigger is fired for the first time, and the sqlserver job starts.

Another user, Georges II for exemple, makes another insert in table mytable1. The trigger is fired , but the sql server job doesn't start, because the first execution isn't finished.

At the end, I have my 2 rows in mytable1, my 2 rows in mytable2, but the stuff made by the sql server job for Georges II is not done.

I hope you are less lost !

Thanks in advance

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

I can only guess, (hopefully one of the smarter people on this site will review this as well) but it sounds like a "blocking" issue. Can you wrap your code within Transactions? Or try a wait time?

Obviously, this is not the best solution...

I wasn't born stupid - I had to study.

philcart SSC-Forever Points: 47796 More actions · Answer 6

Two things,

1) Check the "Delay between reposnses..." option on the "Response" tab of the Alert properties dialog. It may be the response delay that is preventing the job from being run.

2) "...sql server job does a lot of things...", presuming #1 is not the issue, unless you can reduce the number of "things" that the job is doing, you're not going to get anywhere. SQL Server will always skip the job execution if it is already running. Maybe you need to re-evaluate the process.

EG: Instead of raising an alert to perform a series of actions, set a flag on the record or something similiar. Then with a job that runs frequently, say every minute, process all the records that have that flag set.

--------------------
Colt 45 - the original point and click interface

gael80 Old Hand Points: 318 More actions · Answer 7

hello,

I've Checked the "Delay between responses..." option on the "Response" tab of the Alert properties dialog. It was set on the value of 1 minute, so i put it at 25 seconds.

As it will be not solve the problem in all cases, i will think of re-evaluate the process, either your solutions with the flag and a scheduled job every minute, or either a solution of messaging-queuing like Microsoft Message Queue ( Is this tool integrated with SQL Server ? )

Thanks a lot to you both