Below is my code in an event. if i execute it manually in interactive sql its working fine but if i put it in event ( HAS to be Automated), nothing is happening. Help needed urgently

CREATE EVENT acct_alert
SCHEDULE acct_alert
BETWEEN '8:00AM' AND '6:00PM'
EVERY 2 MINUTES ON
   ('Monday','Tuesday','Wednesday','Thursday','Friday')
HANDLER
   BEGIN
    SELECT xp_write_file(
             'C:\\LIVE\\New Folder\\'||COUNTER || '_' ||ACCT_NO|| '.txt',
             ACCT_NO || ',' || ACCT_NAME || ','
             || BRANCH || ',' || BRANCH || ','
             || OPENED_BY || ',' || ACCT_TYPE || ','
             || DATE_OPENED || ',' || CURRENCY|| ',' || NOTIFIED )
      FROM tbl_etz_notifications
     WHERE NOTIFIED = 'N';
    UPDATE tbl_etz_notifications
       SET NOTIFIED = 'Y'
     WHERE NOTIFIED = 'N'
 END;

Thanks

asked 29 Jul '11, 04:48

Takudzwa's gravatar image

Takudzwa
818813
accept rate: 0%

edited 29 Jul '11, 12:34

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266

1

Please show the Event and Schedule definition. Perhaps you have to define the owner of the table in you select. BTW. I would advise FROM tbl_etz_notifications with (repeatableread). Otherwise it could happen that you miss a row in the update statement.

(29 Jul '11, 06:12) Thomas Dueme...

I just noticed that you are SELECTing the output of xp_write_file in your event... and since there is no place for the output from the SELECT statement to go this is likely causing an error to occur when the event executes (this may have been the point that Ian was trying to make).

Taking Volker's suggestion, I would SELECT the results into a temp table (e.g. SELECT .... INTO #temp FROM ...) and then check the temp table for any non-zero rows.

Example:

CREATE EVENT acct_alert
SCHEDULE acct_alert
BETWEEN '8:00AM' AND '6:00PM'
EVERY 2 MINUTES ON
   ('Monday','Tuesday','Wednesday','Thursday','Friday')
HANDLER
   BEGIN
    SELECT xp_write_file(
             'C:\\LIVE\\New Folder\\'||COUNTER || '_' ||ACCT_NO|| '.txt',
             ACCT_NO || ',' || ACCT_NAME || ','
             || BRANCH || ',' || BRANCH || ','
             || OPENED_BY || ',' || ACCT_TYPE || ','
             || DATE_OPENED || ',' || CURRENCY|| ',' || NOTIFIED )
           as write_file_result
      INTO #TEMP
      FROM tbl_etz_notifications
     WHERE NOTIFIED = 'N';
    IF exists( select 1 from #TEMP where write_file_result != 0 ) THEN
        -- ... do something to report the error ! ... example:
        MESSAGE 'xp_write_file() failed in acct_alert event' to console;
    END IF;
    UPDATE tbl_etz_notifications
       SET NOTIFIED = 'Y'
     WHERE NOTIFIED = 'N'
 END;
permanent link

answered 29 Jul '11, 16:15

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266
accept rate: 40%

edited 02 Aug '11, 08:35

Thanks!!!!!!!! It Worked Just copied the code and pasted and then tried understanding wat you said and made total sense.

Regards, Takudzwa

(30 Jul '11, 06:00) Takudzwa

Check the server message window/console.

Event errors are logged to the database server message log. 
See Logging database server actions.

See here in DCX.

permanent link

answered 29 Jul '11, 08:58

Ian%20McHardy's gravatar image

Ian McHardy
2.8k23048
accept rate: 36%

If the database is running as a service it might be using a userID (like local system) that has fewer permissions than your user account. This is discussed in this question : http://sqlanywhere-forum.sap.com/questions/1163/how-do-i-execute-a-batch-file-using-xp_cmdshell#1167

Also, you might try doing the select first and then outputting the results to the file with the OUTPUT UNLOAD statement, and then updating the table, just to see if the behavior changes.

permanent link

answered 29 Jul '11, 09:41

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

edited 29 Jul '11, 14:36

FWIW: OUTPUT won't work in an event handler (nor in a stored procedure) as it is an ISQL command. You will have to use the UNLOAD statement instead.

I would also try to check the value of the xp_write_file call - it will return a non-zero value in case a failure occurs. (I guess it's quite uncommon to use xp_write_file() in a select list.)

(29 Jul '11, 11:46) Volker Barth
Replies hidden
Comment Text Removed

Good to know, I had never tried to put it in an event so it had never failed

(29 Jul '11, 14:34) Siger Matt
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×2

question asked: 29 Jul '11, 04:48

question was seen: 1,163 times

last updated: 02 Aug '11, 08:35