Hi Scott,
If you're experiencing an exceed row limit error during an UpdateBatch(),
try to use a client side cursor instead of server side cursor. The server
side cursor has a solid limitation of 2080 bytes in a batch update. If the
data is too much, that error is occurred. A client side cursor doesn't have
that limitation.
In ADO .NET, doesn't use a DataAdapter to update the database from a
DataSet. The DataSet is a dump of the database on the client computer. It
works differently from ADO. So, it doesn't have the problem with the
UpdateBatch method. However, the problem with MaxLocksPerFile might also
happen in ADO .NET, because it is a problem related with Jet Engine. not
the ADO itself.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
--------------------
| Content-Class: urn:content-classes:message
| From: "Scott" <***@implan.com>
| Sender: "Scott" <***@implan.com>
| References: <046001c37d5f$68efd8a0$***@phx.gbl>
<r6R#***@cpmsftngxa06.phx.gbl>
<71a501c37df4$702019b0$***@phx.gbl>
<***@cpmsftngxa06.phx.gbl>
| Subject: RE: Getting File Sharing Lock Count Exceeded
| Date: Fri, 19 Sep 2003 06:47:54 -0700
| Lines: 231
| Message-ID: <10fe01c37eb4$a0706780$***@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcN+tKBwAvFJr9CyTmykuAUVbYplMw==
| Newsgroups: microsoft.public.vb.database.ado
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.vb.database.ado:90557
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.vb.database.ado
|
| Hi Again - thanks for the response. This particular
| application has a defined row count to process the data. I
| tried to do the batch update by moving the updatebatch
| command outside the loop, but it told me I exceeded the
| row limit. I have not found any info on what the limit is
| exactly. If I knew the limit I could updatebatch during
| the loops. Its working at the moment by setting the
| MaxLocksPerFile to 30,000 but that is not a very elegant
| solution. When I have time, I am going to try to figure
| out the limit and updatebatch prior to that. I thought
| that using Optimistic Locking would release the record
| when I was done with it, but maybe since its a query its
| locking all rows.
|
| Does ADO .Net have this problem?
|
| Scott...
|
|
|
| >-----Original Message-----
| >Hi Scott,
| >
| >Locks are for the system to reduce the collision, so that
| the data in the
| >database will not be confused by parallel operations. All
| the locking and
| >unlocking are finished by the system. So you cannot
| release lock manually.
| >Is it possible to update the records in a batch or using
| a update query
| >instead of one by one?
| >
| >Try not to select unneeded rows and columns when
| recordset opens to reduce
| >the size of it.
| >
| >Kevin Yu
| >=======
| >"This posting is provided "AS IS" with no warranties, and
| confers no
| >rights."
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Scott" <***@implan.com>
| >| Sender: "Scott" <***@implan.com>
| >| References: <046001c37d5f$68efd8a0$***@phx.gbl>
| ><r6R#***@cpmsftngxa06.phx.gbl>
| >| Subject: RE: Getting File Sharing Lock Count Exceeded
| >| Date: Thu, 18 Sep 2003 07:52:10 -0700
| >| Lines: 140
| >| Message-ID: <71a501c37df4$702019b0$***@phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Thread-Index: AcN99HAg6QKtpuNOTh2G0BwPCtNOmg==
| >| Newsgroups: microsoft.public.vb.database.ado
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.vb.database.ado:90524
| >| NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
| >| X-Tomcat-NG: microsoft.public.vb.database.ado
| >|
| >| Thank you for your response. I am using Access (forgot
| to
| >| mention that). I did increase the MaxLocksPerFile and
| got
| >| it working. I am the only one accessing the database. I
| >| thought that the rs.update would release the lock when
| you
| >| are using optimistic locking. Is there another way to
| >| manually release the lock? Or is there a better method
| to
| >| connect so I do one record at a time?
| >|
| >| Scott
| >|
| >|
| >| >-----Original Message-----
| >| >Hi Scott,
| >| >It seems that you are using an Access (.mdb) file as
| the
| >| database. Please
| >| >try the following suggestions to see if they can
| resolve
| >| the problem:
| >| >
| >| >1. Look for the presence of an *.LDB file in the same
| >| folder as the *.MDB
| >| >file. When the last person gets out of the database
| this
| >| should
| >| >automatically be deleted.
| >| >
| >| >When you open an Access database file (.mdb) in shared
| >| mode, Microsoft
| >| >Access also creates a locking information file (.ldb)
| >| with the same file
| >| >name (for example, Northwind.ldb) in the same folder
| as
| >| the database file.
| >| >This locking information file stores the computer name
| >| (such as mypc) and
| >| >security name (such as Admin) of each shared user of
| the
| >| database.
| >| >Microsoft Access uses this information to control
| >| concurrency. In most
| >| >cases, Microsoft Access automatically deletes the
| locking
| >| information file
| >| >when the last user closes the database file.
| >| >
| >| >If this file is not being deleted, it could mean that
| >| people are not
| >| >gracefully shutting down the database or the proper
| >| permissions are not
| >| >there for the file to be deleted. For more
| information,
| >| please refer to
| >| >http://support.microsoft.com/default.aspx?scid=kb;en-
| >| us;208778.
| >| >
| >| >If you have the necessary permissions needed to delete
| >| the LDB, but it is
| >| >not being deleted, you can gain insight into who
| has/had
| >| the database open
| >| >and whether or not they left the database in a
| suspicious
| >| state using Jet
| >| >UserRoster. For more information refer to Q198755
| >| ACC2000: Checking Who
| >| >Logged into Database with Jet UserRoster located at
| >| >http://support.microsoft.com/default.aspx?scid=kb;en-
| >| us;198755.
| >| >
| >| >2. If 1 doesn't work. Try to increase the number of
| >| MaxLocksPerFile in the
| >| >registry. It is located in
| >| >HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\<Version
| >| (3.5/4.0)>\Engines\Jet
| >| ><Version 3.*/4.0>\
| >| >
| >| >For more information, please refer to
| >| >http://support.microsoft.com/default.aspx?scid=KB;EN-
| >| US;198633
| >| >
| >| >If anything is unclear, please feel free to reply to
| the
| >| post.
| >| >
| >| >Kevin Yu
| >| >=======
| >| >"This posting is provided "AS IS" with no warranties,
| and
| >| confers no
| >| >rights."
| >| >
| >| >--------------------
| >| >| Content-Class: urn:content-classes:message
| >| >| From: "Scott" <***@implan.com>
| >| >| Sender: "Scott" <***@implan.com>
| >| >| Subject: Getting File Sharing Lock Count Exceeded
| >| >| Date: Wed, 17 Sep 2003 14:05:23 -0700
| >| >| Lines: 29
| >| >| Message-ID: <046001c37d5f$68efd8a0$***@phx.gbl>
| >| >| MIME-Version: 1.0
| >| >| Content-Type: text/plain;
| >| >| charset="iso-8859-1"
| >| >| Content-Transfer-Encoding: 7bit
| >| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| >| Thread-Index: AcN9X2jvCe2/q/gyS/GY73go52HY7A==
| >| >| X-MimeOLE: Produced By Microsoft MimeOLE
| V5.50.4910.0300
| >| >| Newsgroups: microsoft.public.vb.database.ado
| >| >| Path: cpmsftngxa06.phx.gbl
| >| >| Xref: cpmsftngxa06.phx.gbl
| >| microsoft.public.vb.database.ado:90496
| >| >| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| >| >| X-Tomcat-NG: microsoft.public.vb.database.ado
| >| >|
| >| >| Hi - I am using ADO in a VB .Net data handling
| program.
| >| I
| >| >| am getting a File Sharing Lock Count Exceeded Error
| >| that's
| >| >| telling me to increase the MaxLocksPerFile in the
| >| registry.
| >| >|
| >| >| The code is this:
| >| >|
| >| >| With rs
| >| >| .Open(sql, tCn, ADODB.CursorTypeEnum.adOpenDynamic,
| >| >| ADODB.LockTypeEnum.adLockOptimistic)
| >| >| .MoveFirst()
| >| >| For CountyIndex = 1 To CountyCount -
| 1 'CountyCount=160
| >| >| For i = 0 To ReisCount - 1 'ReisCount = 110
| >| >| .Fields("Personal Income").Value =
| >| >| tCountyREIS.PIncome(CountyIndex, i)
| >| >| .Fields("Personal Income Disclosure
| Code").Value =
| >| >| tCountyREIS.PIncomeDisc(CountyIndex, i)
| >| >| .MoveNext()
| >| >| .Update()
| >| >| Next i
| >| >| Next CountyIndex
| >| >| End With
| >| >|
| >| >| It dies at countycount = 87. Its locking record by
| >| record,
| >| >| but apparently the lock is not released. Any
| comments
| >| >| would be helpful.
| >| >|
| >| >| Thanks
| >| >|
| >| >| Scott
| >| >|
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|