What would cause an event not to fire?

[Former Member]

I have an event set to run every 5 minutes. When I trigger it manually, it works perfectly. But it doesn't seem to want to fire on its own schedule.

CREATE EVENT "e_update_page_id"
SCHEDULE "e_update_page_id_1" START TIME '12:02' EVERY 5 MINUTES START DATE '2003-07-07'
HANDLER
BEGIN
set option background_priority = "on";
call dba.sp_update_page_table();
commit;
END

;

The only thing I can think of is that it doesn't finish in 5 minutes, so it doesn't queue itself up for another go.

The procedure the event calls (dba.sp_update_page_table) runs in about 68 seconds when I run it from ISQL. Changing the background priority seems to have no impact on the time.

Do events run at a lower priority than a user connection?

I'm running SQLA 9.0.2.3850

[Breck_Carter]

This has nothing to do with your question, but BEWARE the SET OPTION statement.

As coded, this statement

set option background_priority = "on";

permanently changes the option setting for the current user id, which is probably DBA, and that setting will take effect next time that user makes a connection.

I think you might have wanted to do this

set temporary option background_priority = "on";

which sets the option for the duration of the current connection only.

[Breck_Carter]

Didya set all of production to run in the background? 🙂 It's probably a really good thing that, in spite of the fact I blathered on about background_priority in my book, I don't think it has any real effect in Version 9. Glenn and company can correct me on that.

[Former Member]

interesting... thank you. The event runs under the id of the creator, and yes, that ID is running at background priority. good catch!

[VolkerBarth]

@Breck: I have used background_priority = "on" for doing database extractions for SQL Remote with ASA 8.0.3, and that did have an effect... Without that option, interactive users were really slowed down - with the option, the could work as usual.

[Breck_Carter]

No offense intended, but how do you know it's not running?

Are there any error messages appearing in the database console log? Events don't have "clients" so that's where exceptions get documented.

I suggest you put at least one MESSAGE ... TO CONSOLE statement in the event to debug.

The following seemed to work OK in 9.0.2.3575; I changed it to a 1 minute interval because I'm impatient, and changed the CALL to a MESSAGE:

CREATE EVENT "e_update_page_id"
SCHEDULE "e_update_page_id_1" START TIME '12:02' EVERY 1 MINUTES START DATE '2003-07-07'
HANDLER
BEGIN
set option background_priority = "on";
MESSAGE STRING ( CURRENT TIMESTAMP, ' hello' ) TO CONSOLE;
commit;
END

2010-04-02 15:05:00.031 hello
2010-04-02 15:06:00.078 hello
2010-04-02 15:07:00.015 hello
2010-04-02 15:08:00.062 hello
2010-04-02 15:09:00.078 hello
2010-04-02 15:10:00.031 hello

[Former Member]

How do I know it's not running? No offense taken, it's a good question... truth is, I rushed my diagnosis. What I do know is that the store proecedure is not running as a result of this event, but I don't know if the event itself is at fault, or the procedure. The procedure has not been changed since 2003, so I wasn't suspecting that as the source of the problem.

However, I changed the event to include the messages to the console via SQL Central. I triggered the event, and the messages appeared on the console. Five mins later, nothing. 10 mins later. Still nothing.

[Former Member]

So, I'm confident the event isn't being triggered.

Next, I deleted the scheduling bit entirely (via SQL Central), setting to manual trigger, then subsequently recreated the schedule (without a start date), then I put some pieces of a dead chicken between two slices of bread and then waved it over the screen. And now it appears to be working on schedule again.

[Former Member]

And no, until I put some messages to the console at your suggestion, there were no other messages in there pertaining to this event.