Pass-Through Query w/o Record Locking

[Tony]

Hi All,

Using Access 2K, I have to get at data in a Progress SQL-92 database table
that has more than 255 fields. Some of the data I need is in the 255+
column, so I can't (as far as I know) link the table to get at the fields.
I've created a pass-through query which returns the fields I'm looking for
but am running into record lock problems. All I want to do is display the
data from the Progress table - no adding, deleting, modifying. However, if
someone is editing a record in the table I receive a lock error. Anyone
know if I can modify the following to get around this?

SELECT pem_name AS "NAME", pem_num AS "NUMBER", pem_active AS "empActive"
FROM PUB."pr_employee"
WHERE pem_active = 1
ORDER BY pem_name ASC

I know the SQL works, just not when someone else is in the table...

Thanks & Ciao,

Tony

 

[[MVP] S.Clark]

Can you setup a nightly job writes your needed data to a 'warehouse'(OLTP
vs. OLAP)? Then you can ping that warehouse at will.

Whether you do this with Access or at the 92 level doesn't matter. Just do
it when the other users aren't there.

Total Visual Agent can assist.
http://fmsinc.com/products/agent/index.html

 

[Tony]

Steve,

Thanks for the response. So far from what I've read, that may be what I'll
need to do. I was hoping that I could present the data in real-time, but
I'm not finding any solutions that allow me to do so without Access &
Progress competing for the record lock. I'll keep an eye on this thread to
see if anyone else chimes in...

Thanks again & Ciao,

Tony

 

[Tony]

Hey Steve, if you're following this thread...

Found some documentation on the Progress web site that addresses an SQL
command with the following syntax:

set transaction isolation level [read uncommitted | read committed |
repeatable read | serializable]

The description of the read uncommitted looks like what I need; no record
locks, allows a dirty read. However, I'm not a SQL god by any stretch and
can't find any examples. Any chance you know how/where to insert this?
I've tried adding it to a line before the select statement but with no luck.

Thanks again for the help.

Tony

 

[[MVP] S.Clark]

Access doesn't understand these, and I didn't know that the 92 would, so I
didn't bring it up.

But to learn more about it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_3q0j.asp

Hey Steve, if you're following this thread...

Found some documentation on the Progress web site that addresses an SQL
command with the following syntax:

set transaction isolation level [read uncommitted | read committed |
repeatable read | serializable]

The description of the read uncommitted looks like what I need; no record
locks, allows a dirty read. However, I'm not a SQL god by any stretch and
can't find any examples. Any chance you know how/where to insert this?
I've tried adding it to a line before the select statement but with no
luck.

Thanks again for the help.

Tony

Can you setup a nightly job writes your needed data to a 'warehouse'(OLTP
vs. OLAP)? Then you can ping that warehouse at will.

Whether you do this with Access or at the 92 level doesn't matter. Just
do it when the other users aren't there.

Total Visual Agent can assist.
http://fmsinc.com/products/agent/index.html

 

[Tony]

Steve,

I'll check out the link. Thanks again, you've been a big help.

Tony

Access doesn't understand these, and I didn't know that the 92 would, so I
didn't bring it up.

But to learn more about it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_3q0j.asp

Hey Steve, if you're following this thread...

Found some documentation on the Progress web site that addresses an SQL
command with the following syntax:

set transaction isolation level [read uncommitted | read committed |
repeatable read | serializable]

The description of the read uncommitted looks like what I need; no record
locks, allows a dirty read. However, I'm not a SQL god by any stretch
and can't find any examples. Any chance you know how/where to insert
this? I've tried adding it to a line before the select statement but with
no luck.

Thanks again for the help.

Tony

Can you setup a nightly job writes your needed data to a
'warehouse'(OLTP vs. OLAP)? Then you can ping that warehouse at will.

Whether you do this with Access or at the 92 level doesn't matter. Just
do it when the other users aren't there.

Total Visual Agent can assist.
http://fmsinc.com/products/agent/index.html

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi All,

Using Access 2K, I have to get at data in a Progress SQL-92 database
table that has more than 255 fields. Some of the data I need is in the
255+ column, so I can't (as far as I know) link the table to get at the
fields. I've created a pass-through query which returns the fields I'm
looking for but am running into record lock problems. All I want to do
is display the data from the Progress table - no adding, deleting,
modifying. However, if someone is editing a record in the table I
receive a lock error. Anyone know if I can modify the following to get
around this?

SELECT pem_name AS "NAME", pem_num AS "NUMBER", pem_active AS
"empActive"
FROM PUB."pr_employee"
WHERE pem_active = 1
ORDER BY pem_name ASC

I know the SQL works, just not when someone else is in the table...

Thanks & Ciao,

Tony

 

[Tony]

If anyone is following this thread...

The solution was a lot more simple, in this case, than I ever thought it
would be. Added 'LCK=NONE' to the connection string and can now read with
no record locks...

Tony

Access doesn't understand these, and I didn't know that the 92 would, so I
didn't bring it up.

But to learn more about it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_3q0j.asp

Hey Steve, if you're following this thread...

Found some documentation on the Progress web site that addresses an SQL
command with the following syntax:

set transaction isolation level [read uncommitted | read committed |
repeatable read | serializable]

The description of the read uncommitted looks like what I need; no record
locks, allows a dirty read. However, I'm not a SQL god by any stretch
and can't find any examples. Any chance you know how/where to insert
this? I've tried adding it to a line before the select statement but with
no luck.

Thanks again for the help.

Tony

Can you setup a nightly job writes your needed data to a
'warehouse'(OLTP vs. OLAP)? Then you can ping that warehouse at will.

Whether you do this with Access or at the 92 level doesn't matter. Just
do it when the other users aren't there.

Total Visual Agent can assist.
http://fmsinc.com/products/agent/index.html

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi All,

Using Access 2K, I have to get at data in a Progress SQL-92 database
table that has more than 255 fields. Some of the data I need is in the
255+ column, so I can't (as far as I know) link the table to get at the
fields. I've created a pass-through query which returns the fields I'm
looking for but am running into record lock problems. All I want to do
is display the data from the Progress table - no adding, deleting,
modifying. However, if someone is editing a record in the table I
receive a lock error. Anyone know if I can modify the following to get
around this?

SELECT pem_name AS "NAME", pem_num AS "NUMBER", pem_active AS
"empActive"
FROM PUB."pr_employee"
WHERE pem_active = 1
ORDER BY pem_name ASC

I know the SQL works, just not when someone else is in the table...

Thanks & Ciao,

Tony

 

[Guest]

Tony,
Where exactly did you put the 'LCK=NONE' statememt?

Thanks

Chris

If anyone is following this thread...

The solution was a lot more simple, in this case, than I ever thought it
would be. Added 'LCK=NONE' to the connection string and can now read with
no record locks...

Tony

Access doesn't understand these, and I didn't know that the 92 would, so I
didn't bring it up.

But to learn more about it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_3q0j.asp

Hey Steve, if you're following this thread...

Found some documentation on the Progress web site that addresses an SQL
command with the following syntax:

set transaction isolation level [read uncommitted | read committed |
repeatable read | serializable]

The description of the read uncommitted looks like what I need; no record
locks, allows a dirty read. However, I'm not a SQL god by any stretch
and can't find any examples. Any chance you know how/where to insert
this? I've tried adding it to a line before the select statement but with
no luck.

Thanks again for the help.

Tony

Can you setup a nightly job writes your needed data to a
'warehouse'(OLTP vs. OLAP)? Then you can ping that warehouse at will.

Whether you do this with Access or at the 92 level doesn't matter. Just
do it when the other users aren't there.

Total Visual Agent can assist.
http://fmsinc.com/products/agent/index.html

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi All,

Using Access 2K, I have to get at data in a Progress SQL-92 database
table that has more than 255 fields. Some of the data I need is in the
255+ column, so I can't (as far as I know) link the table to get at the
fields. I've created a pass-through query which returns the fields I'm
looking for but am running into record lock problems. All I want to do
is display the data from the Progress table - no adding, deleting,
modifying. However, if someone is editing a record in the table I
receive a lock error. Anyone know if I can modify the following to get
around this?

SELECT pem_name AS "NAME", pem_num AS "NUMBER", pem_active AS
"empActive"
FROM PUB."pr_employee"
WHERE pem_active = 1
ORDER BY pem_name ASC

I know the SQL works, just not when someone else is in the table...

Thanks & Ciao,

Tony