Locking in MSSQL 7.0

Hi,
    We have developed a helpdesk application to run with MSSQL 6.5,
Oracle 7.3, Oracle 8 and most recently with MSSQL 7.0
The main problem we are facing is the application freezing up on browses
(that display a list of records),due to the new locking scheme in MSSQL
7.0
Our browses use the 'Retrieve as needed' feature .
What happens is that when there are still rows to be fetched (i.e the
cursor is still open in the backend), the browses very often freeze due
to the locks
on the records. We can avoid the locks by fetching all records to the
frontend at one go, but this solution may not always be desirable as it
is
1.) Slower
2.) Uses up more memory

the following is an excerpt from the manual:

     After sending a query to the server, all applications must
immediately fetch all result rows to completion. If an application does
not
fetch all result rows, locks may be left on the tables, blocking other
users. If you are using an application that transparently submits
Transact-SQL statements to the server, the application must fetch all
result rows. If it does not (and if it cannot be configured to do
so), you may be unable to resolve the blocking problem. To avoid the
problem, you can restrict these applications to a reporting or
decision-support database.

The above suggestion is not very helpful and will not blend in with our
design.

Has anyone faced such a problem. Our application works just fine for
Oracle & MSSQL6.5. Its just MSSQL 7.0 that seems to dislike us.
Any help will be appreciated
ram samant

0
Ram
10/5/1999 3:18:24 AM
sybase.powerbuilder.general 62418 articles. 17 followers. Follow

4 Replies
562 Views

Similar Articles

[PageSpeed] 21

Try to use Isolation Level 0 or Read Uncommited if this is desirable for
you.

Ram Samant wrote in message <37F96E00.4CEEA782@datawatch.com.au>...
>
>Hi,
>    We have developed a helpdesk application to run with MSSQL 6.5,
>Oracle 7.3, Oracle 8 and most recently with MSSQL 7.0
>The main problem we are facing is the application freezing up on browses
>(that display a list of records),due to the new locking scheme in MSSQL
>7.0
>Our browses use the 'Retrieve as needed' feature .
>What happens is that when there are still rows to be fetched (i.e the
>cursor is still open in the backend), the browses very often freeze due
>to the locks
>on the records. We can avoid the locks by fetching all records to the
>frontend at one go, but this solution may not always be desirable as it
>is
>1.) Slower
>2.) Uses up more memory
>
>the following is an excerpt from the manual:
>
>     After sending a query to the server, all applications must
>immediately fetch all result rows to completion. If an application does
>not
>fetch all result rows, locks may be left on the tables, blocking other
>users. If you are using an application that transparently submits
>Transact-SQL statements to the server, the application must fetch all
>result rows. If it does not (and if it cannot be configured to do
>so), you may be unable to resolve the blocking problem. To avoid the
>problem, you can restrict these applications to a reporting or
>decision-support database.
>
>The above suggestion is not very helpful and will not blend in with our
>design.
>
>Has anyone faced such a problem. Our application works just fine for
>Oracle & MSSQL6.5. Its just MSSQL 7.0 that seems to dislike us.
>Any help will be appreciated
>ram samant
>


0
Ruben
10/5/1999 3:38:53 PM
I used SQLCA.Lock = 'RU' and it worked for me.
Thanks.

Ruben Sanchez wrote:

> Try to use Isolation Level 0 or Read Uncommited if this is desirable for
> you.
>
> Ram Samant wrote in message <37F96E00.4CEEA782@datawatch.com.au>...
> >
> >Hi,
> >    We have developed a helpdesk application to run with MSSQL 6.5,
> >Oracle 7.3, Oracle 8 and most recently with MSSQL 7.0
> >The main problem we are facing is the application freezing up on browses
> >(that display a list of records),due to the new locking scheme in MSSQL
> >7.0
> >Our browses use the 'Retrieve as needed' feature .
> >What happens is that when there are still rows to be fetched (i.e the
> >cursor is still open in the backend), the browses very often freeze due
> >to the locks
> >on the records. We can avoid the locks by fetching all records to the
> >frontend at one go, but this solution may not always be desirable as it
> >is
> >1.) Slower
> >2.) Uses up more memory
> >
> >the following is an excerpt from the manual:
> >
> >     After sending a query to the server, all applications must
> >immediately fetch all result rows to completion. If an application does
> >not
> >fetch all result rows, locks may be left on the tables, blocking other
> >users. If you are using an application that transparently submits
> >Transact-SQL statements to the server, the application must fetch all
> >result rows. If it does not (and if it cannot be configured to do
> >so), you may be unable to resolve the blocking problem. To avoid the
> >problem, you can restrict these applications to a reporting or
> >decision-support database.
> >
> >The above suggestion is not very helpful and will not blend in with our
> >design.
> >
> >Has anyone faced such a problem. Our application works just fine for
> >Oracle & MSSQL6.5. Its just MSSQL 7.0 that seems to dislike us.
> >Any help will be appreciated
> >ram samant
> >

0
Ram
10/6/1999 1:09:32 AM
Hi Ram,

if you set SQLCA.Lock = 'RU', whole application runs at isolation level 0,
not only your "retrieve as needed" datawindows, doesn't it?

Best Regards,
Alberto De Sandre

Ram Samant wrote in message <37FAA14C.F3FEFA0D@datawatch.com.au>...
>I used SQLCA.Lock = 'RU' and it worked for me.
>Thanks.
>
>Ruben Sanchez wrote:
>
>> Try to use Isolation Level 0 or Read Uncommited if this is desirable for
>> you.
>>
>> Ram Samant wrote in message <37F96E00.4CEEA782@datawatch.com.au>...
>> >
>> >Hi,
>> >    We have developed a helpdesk application to run with MSSQL 6.5,
>> >Oracle 7.3, Oracle 8 and most recently with MSSQL 7.0
>> >The main problem we are facing is the application freezing up on browses
>> >(that display a list of records),due to the new locking scheme in MSSQL
>> >7.0
>> >Our browses use the 'Retrieve as needed' feature .
>> >What happens is that when there are still rows to be fetched (i.e the
>> >cursor is still open in the backend), the browses very often freeze due
>> >to the locks
>> >on the records. We can avoid the locks by fetching all records to the
>> >frontend at one go, but this solution may not always be desirable as it
>> >is
>> >1.) Slower
>> >2.) Uses up more memory
>> >
>> >the following is an excerpt from the manual:
>> >
>> >     After sending a query to the server, all applications must
>> >immediately fetch all result rows to completion. If an application does
>> >not
>> >fetch all result rows, locks may be left on the tables, blocking other
>> >users. If you are using an application that transparently submits
>> >Transact-SQL statements to the server, the application must fetch all
>> >result rows. If it does not (and if it cannot be configured to do
>> >so), you may be unable to resolve the blocking problem. To avoid the
>> >problem, you can restrict these applications to a reporting or
>> >decision-support database.
>> >
>> >The above suggestion is not very helpful and will not blend in with our
>> >design.
>> >
>> >Has anyone faced such a problem. Our application works just fine for
>> >Oracle & MSSQL6.5. Its just MSSQL 7.0 that seems to dislike us.
>> >Any help will be appreciated
>> >ram samant
>> >
>


0
Alberto
10/8/1999 3:04:02 PM
Hi Alberto,
    Yes it does. And am not sure if that is good or bad :-)
regards
ram

Alberto De Sandre wrote:

> Hi Ram,
>
> if you set SQLCA.Lock = 'RU', whole application runs at isolation level 0,
> not only your "retrieve as needed" datawindows, doesn't it?
>
> Best Regards,
> Alberto De Sandre
>
> Ram Samant wrote in message <37FAA14C.F3FEFA0D@datawatch.com.au>...
> >I used SQLCA.Lock = 'RU' and it worked for me.
> >Thanks.
> >
> >Ruben Sanchez wrote:
> >
> >> Try to use Isolation Level 0 or Read Uncommited if this is desirable for
> >> you.
> >>
> >> Ram Samant wrote in message <37F96E00.4CEEA782@datawatch.com.au>...
> >> >
> >> >Hi,
> >> >    We have developed a helpdesk application to run with MSSQL 6.5,
> >> >Oracle 7.3, Oracle 8 and most recently with MSSQL 7.0
> >> >The main problem we are facing is the application freezing up on browses
> >> >(that display a list of records),due to the new locking scheme in MSSQL
> >> >7.0
> >> >Our browses use the 'Retrieve as needed' feature .
> >> >What happens is that when there are still rows to be fetched (i.e the
> >> >cursor is still open in the backend), the browses very often freeze due
> >> >to the locks
> >> >on the records. We can avoid the locks by fetching all records to the
> >> >frontend at one go, but this solution may not always be desirable as it
> >> >is
> >> >1.) Slower
> >> >2.) Uses up more memory
> >> >
> >> >the following is an excerpt from the manual:
> >> >
> >> >     After sending a query to the server, all applications must
> >> >immediately fetch all result rows to completion. If an application does
> >> >not
> >> >fetch all result rows, locks may be left on the tables, blocking other
> >> >users. If you are using an application that transparently submits
> >> >Transact-SQL statements to the server, the application must fetch all
> >> >result rows. If it does not (and if it cannot be configured to do
> >> >so), you may be unable to resolve the blocking problem. To avoid the
> >> >problem, you can restrict these applications to a reporting or
> >> >decision-support database.
> >> >
> >> >The above suggestion is not very helpful and will not blend in with our
> >> >design.
> >> >
> >> >Has anyone faced such a problem. Our application works just fine for
> >> >Oracle & MSSQL6.5. Its just MSSQL 7.0 that seems to dislike us.
> >> >Any help will be appreciated
> >> >ram samant
> >> >
> >

0
Ram
10/11/1999 2:44:39 AM
Reply:

Similar Artilces:

Powerbuilder 5.0/7.0 and MS SQL 7.0 Performance issues
We recently converted our app from Powerbuilder 5.0 to PB 7.0 -- the app in PB 7.0 definitely runs slower on the client PC's than it did in PB 5.0. Now we are migrating the database from MS SQL 6.5 to 7.0 SP2. We successfully converted the databases in a testing environment, but now the app runs terribly slow! I don't think it's a hardware issue -- the test box is a Compaq 8500 quad Xeon 500 w/1GB Ram. We experimented running the app as a PB 5.0 app against MS SQL 7.0 and the problem is the same. When we execute SQL directly against the DB outside of PB, we see no perf...

What Does PowerBuilder 7.0.2 Fix From 7.0?
Where would I find a white paper documenting what bugs PowerBuilder 7.0.2 fixes from 7.0? Thanks... Cayce http://download.sybase.com/software/tools/eastudio/35EBF1/pb702_ebf_8011.htm -- pbm_hopethishelps, Roy Kiesler [TeamSybase] Sybase Developers Network (SDN) - http:///www.sybase.com/sdn "Cayce Cook" <ccook@linnet.ca> wrote in message news:UGpDliNl$GA.298@forums.sybase.com... > Where would I find a white paper documenting what bugs PowerBuilder 7.0.2 > fixes from 7.0? > > > Thanks... > Cayce > > ...

PB 7.0 & MSSql 7.0
Hi there, I have a appl. developed in 5.02 converted to 6.5.1 and now finaly to 7.0. Connectin MSSQL 7.0 via ODBC appl. bombs on mydatastore.DataObject = "somedw" with "pb60.exe error in appl." If I switch to Access db via ODBC all works fine??? I'm using ODBC 3.510.3711.0 and SQLSRV32.DLL 3.70.06.23. What the heck could be wrong here? TIA Dan Current SQL Server 3.70.08.20 Current ODBC 3.520.4403.2 Cueernt PB 7.02.8022 ( First good PB 7.02 build 8011, First good 7.01 7021 ) I know of no connection issues with MSS ODBC issues are only perform...

Downgrade from 7.0.1 or 7.0.2 to 7.0.0
What would be the issues involved, and what steps would be needed to downgrade from either 7.0.1 or 7.0.2 back down to 7.0.0? Am I stuck with offloading the databases to flat files, uninstalling, installing the mandated version, and then re-importing the flat files? Any advice for this Sybase newbie would be much appreciated. Thanks. No issues, easy steps... I often switch between point releases simply by copying/renaming subdirectories of C:\Program Files\Sybase. Each time I install a new upgrade or EBF I make a copy of the subdirectory; on *this* laptop it now looks like ...

PowerBuilder 7.0 & SQL Server 7.0
Hi: I've develop an application that connect to and MS SQL SERVER 7.0 database via ODBC. When i make the .exe file and run it in Win95 or Windows NT Workstation it works fine, but when i run it in a Win98 client the following error apears. SQLSTATE=22005 - "Invalid character value for cast specification." Any clues about this. The exe was build in PowerBuilder 7.0 over a Windows NT Workstation 4.0 Thanks in advance. Jorge, Try adding to the dbparm - CallEscape='No'. Here is an example: SQLCA.DBParm = "Connectstring='DSN=<your ...

PowerBuilder 7.0 & SQL Server 7.0
Hi: I've develop an application that connect to and MS SQL SERVER 7.0 database via ODBC. When i make the .exe file and run it in Win95 or Windows NT Workstation it works fine, but when i run it in a Win98 client the following error apears. SQLSTATE=22005 - "Invalid character value for cast specification." Any clues about this. The exe was build in PowerBuilder 7.0 over a Windows NT Workstation 4.0 Thanks in advance. Jorge, Try adding to the dbparm - CallEscape='No'. Here is an example: SQLCA.DBParm = "Connectstring='DSN=<your ...

Connect MS-SQL 7.0 in PowerBuilder 7.0
In PowerBuilder 6.5, I can connect MS-SQL 6.5 directly not using ODBC, but in PowerBuilder 7.0, how can I connect MS-SQL 7.0 directly ? Please post to the powersoft.public.powerbuilder newsgroups This is dedicated to PB issues when used within Jaguar Masun wrote: > In PowerBuilder 6.5, I can connect MS-SQL 6.5 directly not using ODBC, but > in PowerBuilder 7.0, how can I connect MS-SQL 7.0 directly ? ...

About Datawindow
Hi, I have created a datawindow from a stored procedure created in MSSQL 7.0. I am passing arguments to the stored procedure. When I retrieve data for the datawindow I pass valid values for the arguments, but the datawindow prompts for the arguments again for the second time then shows the result. When it prompts second time for the arguments if I say cancel it still retrieves the data. This happens in the preview mode as well as through the script. What is going wrong ? Thanks in advance. Sorry Guys , I found the problem. Don't worry about it! LNN wrote in messag...

Powerbuilder 9.0 Migration from powerbuilder 7.0.3 problem
Hi all, i was trying an migrate an powerbuilder 7.0.3 application to powerbuilder 9.0(PFC BASED APPLICATION (PFC objects version PB 6.5) ) during migration i am getting this error. tcmborrower.pbl(w_cursor_check).w_cursor_check.open.2: Error C0163: The definition of referenced global variable sqlca was improperly compiled in object trcomps Did anybody faced this kind of error? Thx Ashok JPMORGAN ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp First error message which i got during the migration is : tcmappl.pb...

MSSQL 7.0 and PB 7.0 Stored Procedures...HELP!
Hi: What happens to the return value and OUT parameter values, upon returning to PB, when an SQL statement fails in a stored procedure. I have a DELETE statement (in a stored procedure) which fails due to foreign-key constraints. The OUT parameter and return value are 0 in PB upon return from the procedure(sqlca.sqldbcode is set to 547). If the DELETE doesn't fail, then the correct values are in the PB variables in the script. Is it possible that if an error occurs in the stored procedure, then the return and OUT parameter values are always going to be 0 when trying to...

Error ORA-01460 in PowerBuilder 7.0.2/7.0.3
Hello, I have several applications developed in PowerBuilder 7.0.2 and database Oracle 8.1.6. Now, we upgrade Oracle 8.1.6 to Oracle 8.1.7 and in some application I will appear the error "ORA-01460: unimplemented or unreasonable conversion requested unimplemented or unreasonable conversion requested" when recovering the information of the database. I have upgraded PowerBuilder 7.0.2 to PowerBuilder 7.0.3 (Build 10213) and the problem persists. Any help that you can give me will be welcome Thank you Oscar Can you run the database application in trace mode and ...

Powerbuilder 7.0 database locking problem
We used the default SQLCA transaction object to connect to our database. We open a sheet window which displays data in a read only datawindow. This sheet window in turn, opens a response window which edits the data that is display on the sheet window. The data is changed via an inbeded SQL statement. The problem is that transaction object will not allow the SQL statement to run. Some kind of locking internal to the transaction object is locking the SQL statement. The database is not locked since it can be edited using a difference connections. We where wondering if there is a me...

Web resources about - Locking in MSSQL 7.0 - sybase.powerbuilder.general

Two-phase locking - Wikipedia, the free encyclopedia
It is also the name of the resulting set of database transaction schedules (histories). The protocol utilizes locks , applied by a transaction ...

HOAXES: Facebook Is Not Locking Users’ Accounts March 20 For Profanity Or Discussing Religion
Fear not, Facebook users: Starting March 20, you are still more than welcome to discuss religion or use profanity to your heart’s content, although ...

End Region Locking (@EndRegionLock) on Twitter
Sign in Sign up You are on Twitter Mobile because you are using an old version of Internet Explorer. Learn more here End Region Locking @ EndRegionLock ...

Diferencial Torsen (Autoblocante) / Self-locking Torsen Differential - YouTube
Diferencial tipo TorSen de puente trasero Hecho en Autodesk Inventor 2009. Vean el remake http://www.youtube.com/watch?v=4g2KYO3cBgg&feature=watch_response ...

Teenager thwarts alleged car theft by locking himself in mother's BMW
Police have charged a man who allegedly tried to steal a Perth mum's BMW as her 17-year-old son sat in the passenger seat.

Locking in on space junk
Alan Kohler speaks to Dr Ben Greene, chief executive of Electro Optic Systems, an Australian company generating revenue from interstellar debris. ...

Locking in a fixed rate loan might be a smart move
Even though the Reserve Bank hasn't cut official interest rates since February, the cost of borrowing with fixed-rate home loans continues to ...

Cyber raiders hold Queensland firms to $3000 ransom by locking computer files
ALMOST 30 Australian businesses, including 12 in Queensland, have been held to ransom in the past week by cybercriminals who lock up their computer ...

Locking up indigenous youths not working - WA chief justice
Locking up young Aboriginal offenders in Perth is more expensive than putting them up at a plush hotel, Western Australia's Chief Justice Wayne ...

Lip-locking fun at celebrity spring break
Coachella is one of the world's largest festivals, and it's also the festival of choice for celebrities to go and let their hair down.

Resources last updated: 12/10/2015 12:59:37 AM