SQL Server locking problem
Hi,
I have a VB 6.0 application that is a front end for an SQL Server database. The problem is than if an instance of the application uses the same form that uses another instance of the same application on another computer, on instance block the other an vice versa. When I update information on the server I use transactions. I tried all the options for cnn.IsolationLevel=X, but no result. If even the aplications tries to query a table that is opened with Entreprise Manager, the application is locked by the Entreprise Manager. If i close the table the problem is solved and the application is unlocked and it can query the table. THE TABLE IS OPEN FOR VIEWING, not for design or update.
Can anyone help me? Thanks.
Re: SQL Server locking problem
I use SQL Server 2000.
Thanks
Re: SQL Server locking problem
If you get the data of the table via recordsets, check the .locktype property of the recordset. Your description of the problem indicates that you use adlockpessimistic. That should be changed to .adlockoptimistic.
HTH
Re: SQL Server locking problem
Hi,
Yes, I use recordset, but LockType = adLockOptimistic
Not this is the problem.
Thanks anyway.
Re: SQL Server locking problem
Are you using a static, dynamic or unspecified cursor?
I have many multi-user apps using VB and SQL Server and I found that using batch updates and a static cursor gives the least problems i.e.
rs1.Open cmd1, , adOpenStatic, adLockBatchOptimistic
Re: SQL Server locking problem
I use adOpenStatic and adLockOptimistic.
Re: SQL Server locking problem
I am not sure,but maybe it happens because user privilege into DB is Lock table on mode exclusive or something so.
oversee user privilege.
Re: SQL Server locking problem