Periodically run an SQL update statement
Periodically run an SQL update statement
(OP)
I need to run an update query periodically.
It basically set's audit dates to 000000. these dates are set as and when people up date stock records or when someone runs the 'update cycle count dates'. So I need it to run at night, ready for the morning's counts.
I could probably do this with access but it would be nice to keep it all within pervasive somehow and not having to install access run-time etc.
It basically set's audit dates to 000000. these dates are set as and when people up date stock records or when someone runs the 'update cycle count dates'. So I need it to run at night, ready for the morning's counts.
CODE --> SQL
UPDATE INVENTORY_MST2 INNER JOIN INVENTORY_MSTR ON INVENTORY_MST2.PART = INVENTORY_MSTR.PART SET INVENTORY_MST2.DATE_CYCLE = '000000' WHERE ((Not (INVENTORY_MSTR.CODE_ABC)='A' And Not (INVENTORY_MSTR.CODE_ABC)='B'));
I could probably do this with access but it would be nice to keep it all within pervasive somehow and not having to install access run-time etc.
RE: Periodically run an SQL update statement
For example, you could save your SQL in a file called "ResetAuditDates.sql" with your SQL (tested before hand to make sure it doesn't do something wrong). You would then create the scheduled task pointing to the pvddl.exe with the correct command line parameters. I tested running pvddl.exe with the following command line (didn't create a task but that should be easy) and it updated the database:
CODE
Mirtheil
http://www.mirtheil.com
RE: Periodically run an SQL update statement
That will work perfectly!
I will need to specify which database to run the script on?
we have a number of database's, apologises I'm very new to PSQL and only really have experience with MS access -_-
With access I just connect to the relevant database and then run my script/s...
RE: Periodically run an SQL update statement
Mirtheil
http://www.mirtheil.com
RE: Periodically run an SQL update statement
This is what I'm trying:
CODE -->
GLOBALSEL is the serverDSN (see attached image)
I still get:
pvddl: A table or view name must be specified for update.
My sql syntax is wrong somehow?
RE: Periodically run an SQL update statement
Mirtheil
http://www.mirtheil.com
RE: Periodically run an SQL update statement
CODE --> SQL
I'm specifying the table after the update statement, seems to be correct.
https://docs.actian.com/psql/psqlv11/wwhelp/wwhimp...
RE: Periodically run an SQL update statement
Mirtheil
http://www.mirtheil.com
RE: Periodically run an SQL update statement
The SQL statement works in access with the PSQL tables linked.
I've run it in the PCC SQL editor, see attached image, from that image you can see the tables exist and I'm logged into that database.
Thanks for any help, i'm very new to this!
Edit:
This might work. I'm just doing a backup and I'll test it out!
CODE --> SQL
RE: Periodically run an SQL update statement
Mirtheil
http://www.mirtheil.com