Progress Update Script

Yahoo Archive
1
Epicor has a tool called Service Connect.
It will update the tables for you.

Or, use a window's script editor - that does keystroke emulation.
I have done this on a small population <1,000 records and it worked out
okay. I created a quick search and retrieved all parts that needed to be
updated, ran the script and checked the parts via a BAQ. It worked out ok.

Bruce


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
scottkreel
Sent: Wednesday, June 17, 2009 11:03 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Progress Update Script

How does one go about writing a script to update data in the Progress
database?

We want to update a single field in the parts table. The value will be
derived from another field in the parts table.

I'm new to Progress. If we were running MSSQL I could easily write a sql
update query.



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links





[Non-text portions of this message have been removed]
2
How does one go about writing a script to update data in the Progress database?

We want to update a single field in the parts table. The value will be derived from another field in the parts table.

I'm new to Progress. If we were running MSSQL I could easily write a sql update query.
3
--- In vantage@yahoogroups.com, "scottkreel" <skreel@...> wrote:
>
> How does one go about writing a script to update data in the Progress database?
>
> We want to update a single field in the parts table. The value will be derived from another field in the parts table.
>
> I'm new to Progress. If we were running MSSQL I could easily write a sql update query.
>


Its pretty simple..

make a batch file (.bat .cmd whatever)

Here is what the batch file should include

Your paths may be different, servername is the actual server name (path to it without \\)
----------------------------------------------------------
set dlc=D:\epicor\oe101b
call D:\epicor\oe101b\bin\sqlexp mfgsys -H servername -S 8350 -user sysprogress -password passwordGoesHere -infile D:\scripts\change_bpm_paths.txt -outfile D:\scripts\result.txt -sqlverbose

------------------------------------------------------------

In D:\scripts\change_bpm_paths.txt I have this sql

for changing paths from the production server to a live server after a backup/restore (from servername1 to servername2 etc)

-------------------------------------------------------------------
UPDATE MFGSYS.PUB.BpTextStorage SET BpTextStorageCode = 'ActWorkflowCall_SCUserKey_LIB-SRV4' WHERE Company = '30' AND BpTextStorageGroupCode = '' AND BpTextStorageCode = 'ActWorkflowCall_SCUserKey_LIB-SRV3' AND Data = 'QOdysWH8rAw=';
UPDATE MFGSYS.PUB.BpTextStorage SET Data = 'LIB-SRV4' WHERE Company = '30' AND BpTextStorageGroupCode = '' AND BpTextStorageCode = 'ActWorkflowCall_ServersKey0' AND Data = 'LIB-SRV3';
UPDATE MFGSYS.PUB.SysAgent SET FileRootDir = '\\lib-srv4\epicor$\MfgSysData,D:\epicor\MfgSysData,\\lib-srv4\epicor$\mfgsys803\Server' WHERE AgentID = 'SystemTaskAgent';
UPDATE PUB.workstation SET WebServiceUrl = 'http://lib-srv4/issfreightservice/issfreightservice400.asmx';


COMMIT;
------------------------------------------------------------------

Run the batch file, schedule it, whatever.

You can also use JDBC sql tools (squirrel, razorSQL) to do something like this as well in a more graphical type format. I created a tutorial and its in the file section on how to connect to Progress using them.