Using ODBC for mass updates

Yahoo Archive
1
I assume so. Please note that I have *not* done this myself - I was
setting up ODBC read-only access to 9.05 yesterday and had just reviewed
the 6.1 process. (We're live on 6.1, testing 9.05).



General SQL GRANT permissions - I assume this works for Progress:

SELECT -- read only

UPDATE -- read/write existing records

INSERT -- add new records

DELETE



<insert standard be very, very careful language here - make backups & do
in test environment first>



Brian.



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of mahsangilbert
Sent: Thursday, December 09, 2010 10:11 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Using ODBC for mass updates





Thanks for the info Brian. Do I just need to edit "61ODBCsecurity.txt" ?
What syntax is used to grant WRITE access?
Todd
--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Brian Roberts" <broberts@...> wrote:
>
> In Vantage 6.1, ODBC access to the Progress database must be enabled
> separately, and that install process sets up read only access. The
> document I have describing this process is named
"Implement_ODBC61.doc",
> and it includes a script named "61ODBCsecurity.txt" that grants
> select-only (=read-only) access like this:
>
>
>
> CREATE USER 'odbcuser','odbcuser';
>
> COMMIT;
>
> GRANT SELECT ON pub.ABCCode TO odbcuser;
>
> GRANT SELECT ON pub.AlertGroup TO odbcuser;
>
> Etc for all tables
>
>
>
> This setup will give you the error message below.
>
>
>
> You could use this as a template to grant more access to a different
> username for ODBC. <insert standard be very careful language here>.
>
>
>
> Brian.
>
>
>
> ________________________________
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
> Of mahsangilbert
> Sent: Thursday, December 09, 2010 9:23 AM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: Using ODBC for mass updates
>
>
>
>
>
> I've tried out all of the options for the ODBC Default Isolation Level
> but continue to get an error message "update on link table failed"
> "[PROGRESS] permission denied (7677)".
>
> I assume there must be a database permission restriction coming into
> play here. Any ideas where/how to change such a setting?
>
> Todd
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ,
> Randall Weber <weber.randy@> wrote:
> >
> > Todd,
> >
> > Have you checked to see if the ODBC connection is set up for
> read-write
> > rather than the usual read-uncommitted?
> >
> > If your's is read-uncommitted, I recommend making a separate ODBC
> connection
> > called VantageWRITE so there is no doubt that you are in that
> dangerous
> > zone.
> >
> > FYI: Though Epicor recommends against this, I have used ODBC several
> times
> > to do mass updates.
> >
> > --
> > Randy Weber
> > weber.randy@
> > (651) 263-1811
> >
> > http://randallweber.com/
> >
> >
> > [Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
I'm working with a standalone Vantage test database that I want to try out some new planning configurations to see how they behave with respect to MRP. At a previous company, I was able to do mass updates to the database through ODBC. However, it appears to not allow such updates to the database anymore.

Is there setting I can change that will allow this or is there a better way to perform such updates?

Thanks,
Todd Gilbert
GCM, Inc.
3
I do updates via ODBC all the time to Vantage. Just be careful that you know what you are updating..I've done a few wrong ones before and caused some corruption..it can be a little scary. You have to go into the ODBC setup on your PC and make sure the isolation level is not read uncommitted. I use serializable, as it is the fastest due to the fact that it locks the entire table during an update. Once you set that, it should let you push updates in.

Adam Whipp

--- In vantage@yahoogroups.com, "mahsangilbert" <tgilbert@...> wrote:
>
> I'm working with a standalone Vantage test database that I want to try out some new planning configurations to see how they behave with respect to MRP. At a previous company, I was able to do mass updates to the database through ODBC. However, it appears to not allow such updates to the database anymore.
>
> Is there setting I can change that will allow this or is there a better way to perform such updates?
>
> Thanks,
> Todd Gilbert
> GCM, Inc.
>
4
Todd,

Have you checked to see if the ODBC connection is set up for read-write
rather than the usual read-uncommitted?

If your's is read-uncommitted, I recommend making a separate ODBC connection
called VantageWRITE so there is no doubt that you are in that dangerous
zone.

FYI: Though Epicor recommends against this, I have used ODBC several times
to do mass updates.

--
Randy Weber
weber.randy@...
(651) 263-1811

http://randallweber.com/


[Non-text portions of this message have been removed]
5
It seems just viewing information while in a writeable isolation mode,
my users will experience lag. Therefore, I take put myself in
repeatable read after making any changes like this.



This is not proven, but just some voodoo I follow.



Bruce Butler, IS Manager



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of adam.whipp
Sent: Tuesday, December 07, 2010 5:19 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Using ODBC for mass updates





I do updates via ODBC all the time to Vantage. Just be careful that you
know what you are updating..I've done a few wrong ones before and caused
some corruption..it can be a little scary. You have to go into the ODBC
setup on your PC and make sure the isolation level is not read
uncommitted. I use serializable, as it is the fastest due to the fact
that it locks the entire table during an update. Once you set that, it
should let you push updates in.

Adam Whipp

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"mahsangilbert" <tgilbert@...> wrote:
>
> I'm working with a standalone Vantage test database that I want to try
out some new planning configurations to see how they behave with respect
to MRP. At a previous company, I was able to do mass updates to the
database through ODBC. However, it appears to not allow such updates to
the database anymore.
>
> Is there setting I can change that will allow this or is there a
better way to perform such updates?
>
> Thanks,
> Todd Gilbert
> GCM, Inc.
>





[Non-text portions of this message have been removed]
6
I've tried out all of the options for the ODBC Default Isolation Level but continue to get an error message "update on link table failed" "[PROGRESS] permission denied (7677)".

I assume there must be a database permission restriction coming into play here. Any ideas where/how to change such a setting?

Todd

--- In vantage@yahoogroups.com, Randall Weber <weber.randy@...> wrote:
>
> Todd,
>
> Have you checked to see if the ODBC connection is set up for read-write
> rather than the usual read-uncommitted?
>
> If your's is read-uncommitted, I recommend making a separate ODBC connection
> called VantageWRITE so there is no doubt that you are in that dangerous
> zone.
>
> FYI: Though Epicor recommends against this, I have used ODBC several times
> to do mass updates.
>
> --
> Randy Weber
> weber.randy@...
> (651) 263-1811
>
> http://randallweber.com/
>
>
> [Non-text portions of this message have been removed]
>
7
Why dont you automate the stopping and starting of App servers overnight?




________________________________
From: mahsangilbert <tgilbert@...>
To: vantage@yahoogroups.com
Sent: Thu, 9 December, 2010 14:23:08
Subject: [Vantage] Re: Using ODBC for mass updates

Â
I've tried out all of the options for the ODBC Default Isolation Level but
continue to get an error message "update on link table failed" "[PROGRESS]
permission denied (7677)".


I assume there must be a database permission restriction coming into play here.
Any ideas where/how to change such a setting?

Todd

--- In vantage@yahoogroups.com, Randall Weber <weber.randy@...> wrote:
>
> Todd,
>
> Have you checked to see if the ODBC connection is set up for read-write
> rather than the usual read-uncommitted?
>
> If your's is read-uncommitted, I recommend making a separate ODBC connection
> called VantageWRITE so there is no doubt that you are in that dangerous
> zone.
>
> FYI: Though Epicor recommends against this, I have used ODBC several times
> to do mass updates.
>
> --
> Randy Weber
> weber.randy@...
> (651) 263-1811
>
> http://randallweber.com/
>
>
> [Non-text portions of this message have been removed]
>







[Non-text portions of this message have been removed]
8
In Vantage 6.1, ODBC access to the Progress database must be enabled
separately, and that install process sets up read only access. The
document I have describing this process is named "Implement_ODBC61.doc",
and it includes a script named "61ODBCsecurity.txt" that grants
select-only (=read-only) access like this:



CREATE USER 'odbcuser','odbcuser';

COMMIT;

GRANT SELECT ON pub.ABCCode TO odbcuser;

GRANT SELECT ON pub.AlertGroup TO odbcuser;

Etc for all tables



This setup will give you the error message below.



You could use this as a template to grant more access to a different
username for ODBC. <insert standard be very careful language here>.



Brian.



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of mahsangilbert
Sent: Thursday, December 09, 2010 9:23 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Using ODBC for mass updates





I've tried out all of the options for the ODBC Default Isolation Level
but continue to get an error message "update on link table failed"
"[PROGRESS] permission denied (7677)".

I assume there must be a database permission restriction coming into
play here. Any ideas where/how to change such a setting?

Todd

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
Randall Weber <weber.randy@...> wrote:
>
> Todd,
>
> Have you checked to see if the ODBC connection is set up for
read-write
> rather than the usual read-uncommitted?
>
> If your's is read-uncommitted, I recommend making a separate ODBC
connection
> called VantageWRITE so there is no doubt that you are in that
dangerous
> zone.
>
> FYI: Though Epicor recommends against this, I have used ODBC several
times
> to do mass updates.
>
> --
> Randy Weber
> weber.randy@...
> (651) 263-1811
>
> http://randallweber.com/
>
>
> [Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
9
Thanks for the info Brian. Do I just need to edit "61ODBCsecurity.txt" ? What syntax is used to grant WRITE access?
Todd
--- In vantage@yahoogroups.com, "Brian Roberts" <broberts@...> wrote:
>
> In Vantage 6.1, ODBC access to the Progress database must be enabled
> separately, and that install process sets up read only access. The
> document I have describing this process is named "Implement_ODBC61.doc",
> and it includes a script named "61ODBCsecurity.txt" that grants
> select-only (=read-only) access like this:
>
>
>
> CREATE USER 'odbcuser','odbcuser';
>
> COMMIT;
>
> GRANT SELECT ON pub.ABCCode TO odbcuser;
>
> GRANT SELECT ON pub.AlertGroup TO odbcuser;
>
> Etc for all tables
>
>
>
> This setup will give you the error message below.
>
>
>
> You could use this as a template to grant more access to a different
> username for ODBC. <insert standard be very careful language here>.
>
>
>
> Brian.
>
>
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of mahsangilbert
> Sent: Thursday, December 09, 2010 9:23 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Using ODBC for mass updates
>
>
>
>
>
> I've tried out all of the options for the ODBC Default Isolation Level
> but continue to get an error message "update on link table failed"
> "[PROGRESS] permission denied (7677)".
>
> I assume there must be a database permission restriction coming into
> play here. Any ideas where/how to change such a setting?
>
> Todd
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> Randall Weber <weber.randy@> wrote:
> >
> > Todd,
> >
> > Have you checked to see if the ODBC connection is set up for
> read-write
> > rather than the usual read-uncommitted?
> >
> > If your's is read-uncommitted, I recommend making a separate ODBC
> connection
> > called VantageWRITE so there is no doubt that you are in that
> dangerous
> > zone.
> >
> > FYI: Though Epicor recommends against this, I have used ODBC several
> times
> > to do mass updates.
> >
> > --
> > Randy Weber
> > weber.randy@
> > (651) 263-1811
> >
> > http://randallweber.com/
> >
> >
> > [Non-text portions of this message have been removed]
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>