RE: [sqr-users] SQR locking table

["Leonard, Kyle" <KyleLeonard@texashealth.org>]

I don't think my posts were clear...it is not a select which locks the
table, but a stored procedure which is called. The table which is
updated or inserted into by the stored proc is being locked down.

The stored proc takes less than a second to run, but the table it
updates is locked until the SQR is finished (as much as ten minutes
later).

 

-----Original Message-----
From: sqr-users-bounces+kyleleonard=texashealth.org@sqrug.org
[mailto:sqr-users-bounces+kyleleonard=texashealth.org@sqrug.org] On
Behalf Of Dwight Schrute
Sent: Tuesday, April 17, 2007 1:00 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] SQR locking table

You can try using the -LOCK flag.  The default setting is Read
Repeatability 
(RR) which is exclusive.  You can use the Cursor Stability (CS) which
locks 
that row until the cursor "moves" from that row or Read Only (RO) which 
eliminates any deadlocks.  The forth option is Release Lock (RL) which
locks 
data only long enough to read.

BEGIN-SELECT -LOCKCS
END-SELECT

I remember reading this somewhere in the past but I don't know if it is 
available in SQR with SQL Server.


>From: "Leonard, Kyle" <KyleLeonard@texashealth.org>
>Reply-To: "This list is for discussion about the SQR database 
>reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
>To: "This list is for discussion about the SQR database
reportinglanguage 
>fromHyperion Solutions." <sqr-users@sqrug.org>
>Subject: RE: [sqr-users] SQR locking table
>Date: Tue, 17 Apr 2007 12:15:24 -0500
>
>Thanks for the reply.
>
>The nolock would work for the individual SQR but there are two
problems:
>       1. Nolock introduces the possiblity of selecting bad data
>       2. The real problem is that this SQR is running and locking
>other processes (BQYs, Java Apps) from selecting or updating the table.
>
>How do we get the SQR to update and release the lock while the SQR
>continues to perform its other processing?
>
>Thanks
>
>
>-----Original Message-----
>From: sqr-users-bounces+kyleleonard=texashealth.org@sqrug.org
>[mailto:sqr-users-bounces+kyleleonard=texashealth.org@sqrug.org] On
>Behalf Of ashwin reddy
>Sent: Tuesday, April 17, 2007 12:04 PM
>To: This list is for discussion about the SQR database reporting
>languagefrom Hyperion Solutions.
>Subject: Re: [sqr-users] SQR locking table
>
>try putting a nolock in your sql and i hope it will work for  u .....
>
>   SELECT * FROM tablename WITH (NOLOCK)
>
>
>"Leonard, Kyle" <KyleLeonard@texashealth.org> wrote:
>   Using SQR 8.5 against a MS SQL Server 2000 database.
>
>
>
>We have an SQR utility which uses a table to identify and run reports.
>The SQR calls a stored procedure which updates or inserts into a table
>to kick off other processes to run. Recently we have noticed that the
>table is locked until the SQR finishes processing keeping the other
>processes from running.
>
>
>
>We've added Commits to both the SQR and the stored procedure without
any
>effect. Searching the archive has not lead to a solution.
>
>
>
>Can anyone shed some light on what else might be occurring and how we
>might be able to remove the lock immediately after the update/insert?
>
>
>
>Thanks
>
>
>
>_________________________________________
>
>Kyle J. Leonard
>
>Sr. Programmer Analyst
>
>Financial Services
>
>Texas Health Resources
>
>
>
>
>
>
>
>The information contained in this message and any attachments is
>intended only for the use of the individual or entity to which it is
>addressed, and may contain information that is PRIVILEGED,
CONFIDENTIAL,
>and exempt from disclosure under applicable law. If you are not the
>intended recipient, you are prohibited from copying, distributing, or
>using the information. Please contact the sender immediately by return
>e-mail and delete the original message from your system.
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users
>
>
>
>---------------------------------
>  Yahoo! Mail is the world's favourite email. Don't settle for less,
sign
>up for your freeaccount today.
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users
>
>
>
>
>The information contained in this message and any attachments is
intended 
>only for the use of the individual or entity to which it is addressed,
and 
>may contain information that is PRIVILEGED, CONFIDENTIAL, and exempt
from 
>disclosure under applicable law.  If you are not the intended
recipient, 
>you are prohibited from copying, distributing, or using the
information.  
>Please contact the sender immediately by return e-mail and delete the 
>original message from your system.
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users

_________________________________________________________________
Mortgage refinance is Hot. *Terms. Get a 5.375%* fix rate. Check savings

https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&se
arch=mortgage_text_links_88_h2bbb&disc=y&vers=925&s=4056&p=5117


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users




The information contained in this message and any attachments is intended only 
for the use of the individual or entity to which it is addressed, and may 
contain information that is PRIVILEGED, CONFIDENTIAL, and exempt from 
disclosure under applicable law.  If you are not the intended recipient, you 
are prohibited from copying, distributing, or using the information.  Please 
contact the sender immediately by return e-mail and delete the original message 
from your system.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users