Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL scripts

Status
Not open for further replies.

Sal2K

Technical User
Jun 22, 2001
2
CA
I have run these 2 update statements twice a week:
Update table1 set field4 =0 where field5 <25
Update table1 set field4 =field5-25 and field5>26

How can I automate these statements so that they run at a certain time every week and get a log feed back how many
records were effected.

I would really appreciate any tip.

 
I dont know what you mean by SQL scripts.. to be specific..

I would use the following command for your requirement..
.. for
Update table1 set field4 =0 where field5 equals or < 25
Update table1 set field4 =field5-25 and field5>25

REPLACE ALL field4 WITH IIF(field5<25,0,field5-25)

As regards automation, use a timer trigger event for that.
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK :)
 
How can I automate these statements so that they run at a certain time every week and get a log feed back how many records were effected.

After each statement, store the _tally to memory variables.

At the end of your program, write out the contents of the variables, either to a DBF that serves as your log file or to a text file (using STRTOFILE, for example).

Compile this program into an EXE.

Schedule the EXE using Windows' (Win2K, NT) task scheduler. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:
 
Update table1 set field4 =field5-25 and field5>26

I presume a typo above.

You can use the Windows Task Scheduler. It was discussed in another thread, do a keyword search. Basically, create a PRG with the commands you want to perform:

PROCEDURE MyPRG
lcDateTime=TTOC(DATETIME())
Update table1 set field4 =0 where field5 equals or < 25
lcMessage=TRANS(_TALLY)+' Records set to 0'
STRTOFILE(lcDateTime+': '+lcMessage,'C:\my.log',.T.)

_TALLY=0
Update table1 set field4 =field5-25 where field5>=25
lcMessage=TRANS(_TALLY)+' Records calculated'
STRTOFILE(lcDateTime+': '+lcMessage,'C:\my.log',.T.)

QUIT
ENDPROC

Schedule a task to execute the prg at the specified time. FYI - If the target machine doesnt have VFP installed, you'll need compile the PRG into an EXE and install it. Jon Hawkins
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top