|
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 |
|
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; 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. |
|
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 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
|
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.