You are correct, it is executed twice, first time to get the definition of the result set (DESCRIBE) and second time to fetch the data. Unfortunately this cannot be changed because the SQL is executed as (24x7 has no idea whether it executes a regular SELECT or a stored procedure (no SQL parsing and translation is performed) There are two workarounds for this problem. If you can modify the stored procedure, make it to save results into some table so in the script you can do DatabaseExecute "EXEC your_procedure_name_here", rows
DatabaseRetrieve "SELECT * FROM result_table", rows If you cannot modify the original procedure here is another workaround:
Create your own procedure that will call the original procedure and save time of the result set in some 1 row table with a column for the system time. That second procedure logic could be like the following: If the table has the time value which is more than a couple of seconds old, just return an empty result set matching by definition the result set of the original procedure and then update the time value, otherwise call the original procedure. If you need help with this procedure please let me know. In the second approach you will NOT need to change original procedure and the only change in the job code is to use your own procedure name. : Hello, : My question is : when I execute stored procedure in JAL script for example:
: DatabaseRetrieve("execute sp_name",rows)
: DatabaseSave("c:\\temp\\file_name.csv","CSV",rows )
: ....
: DatabaseDisconnect : Based from the trace file and database audit table
: It has been executed twice. : Is any particular problem(s) there?
: How to avoid multiply execution for particular job at a time ? : Thanks,
: Gennadiy
|
