Access SQL Server Locking

S

SteveDev

We have a product which has an Access 2000 front-end and a SQL Server backend
database. We are having a problem with locking in the Access front end
database as follows: if a user has the details of a record open and another
user is trying to create a new related record, if the first user changes some
details of the main record, sometime this record update will fail. An error
message appears stating "Error 3157 - ODBC update on linked table failed
timeout"
"[Microsoft][ODBC SQL Server Driver]Timeout expired(#0)". We have found with
two sets of SQL Server data that this problem starts to occur only after a
certain number of records are present in the table in which the record to be
updated exists.
 
P

Peter Yang [MSFT]

Hello Steve,

Thank you for your post!

It appears that this issue is related to your custom database and it is a
development-related request that would be best addressed in the developer
newsgroups. The developer newsgroups are located at:

http://msdn.microsoft.com/newsgroups/default.asp.

For further assistance on this issue, you may want to contact our Develop
Support Services by telephone so that a dedicated Support Professional can
assist you further with your request. To obtain the phone numbers for
specific technology request please take a look at the web site listed below:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS.

Your understanding on this will be greatly appreciated.

Having said that, I'd like to provide some general suggestions to
troubleshoot this type of issues.

1. You may want to create 2 new tables/forms with same relationship to test
if the issue occurs.

2. Check if the issue occurs with a new forntend/backend database that
importing the tables from original ones.

3. Use SQL profiler to capture the query running when the issue occurs. At
the same time, run "sp_lock" and following script to get details of
information of process/lock information on the server when the issue
occurs.


WHILE 1=1
BEGIN
Select * from master.dbo.sysprocesses
WAITFOR DELAY '00:00:05'
END
GO

You may want to check which kind of lock/blocking occurs on which objects
(table/index key/page/row).

4. Check the code related to update action on the form to see if there is
any related issue.

5. Check index/primary key/foreign key on tables to make sure they are
correct.

I have included the following articles for your reference:

822101 The waittype and lastwaittype columns in the sysprocesses table in
SQL
http://support.microsoft.com/?id=822101

Q224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/support/kb/articles/q224/5/87.asp

Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/?id=323630

TechNet Support WebCast:performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622

Please let us know if you have further concerns on the issue. Have a great
day!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=/international.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: Access SQL Server Locking
thread-index: AcYoTn2D7+ktBaCeQi2WpDpxtY+WrA==
X-WBNR-Posting-Host: 203.143.235.222
From: "=?Utf-8?B?U3RldmVEZXY=?=" <[email protected]>
Subject: Access SQL Server Locking
Date: Thu, 2 Feb 2006 15:15:00 -0800
Lines: 14
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.adp.sqlserver
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.adp.sqlserver:23534
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

We have a product which has an Access 2000 front-end and a SQL Server backend
database. We are having a problem with locking in the Access front end
database as follows: if a user has the details of a record open and another
user is trying to create a new related record, if the first user changes some
details of the main record, sometime this record update will fail. An error
message appears stating "Error 3157 - ODBC update on linked table failed
timeout"
"[Microsoft][ODBC SQL Server Driver]Timeout expired(#0)". We have found with
two sets of SQL Server data that this problem starts to occur only after a
certain number of records are present in the table in which the record to be
updated exists.
 
D

Dave Patrick

This one is highly recommended for tracking down a blocking situation.

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hello Steve,
|
| Thank you for your post!
|
| It appears that this issue is related to your custom database and it is a
| development-related request that would be best addressed in the developer
| newsgroups. The developer newsgroups are located at:
|
| http://msdn.microsoft.com/newsgroups/default.asp.
|
| For further assistance on this issue, you may want to contact our Develop
| Support Services by telephone so that a dedicated Support Professional can
| assist you further with your request. To obtain the phone numbers for
| specific technology request please take a look at the web site listed
below:
|
| http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS.
|
| Your understanding on this will be greatly appreciated.
|
| Having said that, I'd like to provide some general suggestions to
| troubleshoot this type of issues.
|
| 1. You may want to create 2 new tables/forms with same relationship to
test
| if the issue occurs.
|
| 2. Check if the issue occurs with a new forntend/backend database that
| importing the tables from original ones.
|
| 3. Use SQL profiler to capture the query running when the issue occurs. At
| the same time, run "sp_lock" and following script to get details of
| information of process/lock information on the server when the issue
| occurs.
|
|
| WHILE 1=1
| BEGIN
| Select * from master.dbo.sysprocesses
| WAITFOR DELAY '00:00:05'
| END
| GO
|
| You may want to check which kind of lock/blocking occurs on which objects
| (table/index key/page/row).
|
| 4. Check the code related to update action on the form to see if there is
| any related issue.
|
| 5. Check index/primary key/foreign key on tables to make sure they are
| correct.
|
| I have included the following articles for your reference:
|
| 822101 The waittype and lastwaittype columns in the sysprocesses table in
| SQL
| http://support.microsoft.com/?id=822101
|
| Q224587 INF: Troubleshooting Application Performance with SQL Server
| http://support.microsoft.com/support/kb/articles/q224/5/87.asp
|
| Resolving blocking problems that are caused by lock escalation in SQL
Server
| http://support.microsoft.com/?id=323630
|
| TechNet Support WebCast:performance troubleshooting and analysis in
| Microsoft SQL Server 2000
| http://support.microsoft.com/?id=838622
|
| Please let us know if you have further concerns on the issue. Have a great
| day!
|
| Best Regards,
|
| Peter Yang
| MCSE2000/2003, MCSA, MCDBA
| Microsoft Online Partner Support
|
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
|
| =====================================================
|
| Business-Critical Phone Support (BCPS) provides you with technical phone
| support at no charge during critical LAN outages or "business down"
| situations. This benefit is available 24 hours a day, 7 days a week to all
| Microsoft technology partners in the United States and Canada.
|
| This and other support options are available here:
|
| BCPS:
| https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
|
| Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
|
| If you are outside the United States, please visit our International
| Support page:
| http://support.microsoft.com/default.aspx?scid=/international.aspx.
|
| =====================================================
|
| This posting is provided "AS IS" with no warranties, and confers no
rights.
 
V

Vadim Rapp

Hello Peter,
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 03
Feb 2006 05:45:35 GMT:

PYM> It appears that this issue is related to your custom database and it
PYM> is a development-related request that would be best addressed in the
PYM> developer newsgroups. The developer newsgroups are located at:

PYM> http://msdn.microsoft.com/newsgroups/default.asp.

Peter, for your information, this newsgroup _is_ developer newsgroup, and
the link you have posted is for the same newsgroups as on this server, only
with web interface (and with this newsgroup excluded for some reason). Next
time you post, you might want to get an idea where you are posting.

PYM> For further assistance on this issue, you may want to contact our
PYM> Develop Support Services by telephone so that a dedicated Support
PYM> Professional can assist you further with your request. To obtain the
PYM> phone numbers for specific technology request please take a look at
PYM> the web site listed below:

PYM> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS.

PYM> Your understanding on this will be greatly appreciated.

I believe the poster already has a very good understanding of where to get
help and where not to.


Vadim Rapp
 
P

Peter Yang [MSFT]

Hello Vadim,

Sorry, my fault. I missed this thread with another one in private
newsgroup. Poster is in the right place and community here shall be able
to help him. :)

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
From: "Vadim Rapp" <[email protected]>
References: <[email protected]>
Subject: Re: Access SQL Server Locking
Date: Sun, 5 Feb 2006 12:10:49 -0600
Lines: 31
MIME-Version: 1.0
Content-Type: text/plain;
format=flowed;
charset="Windows-1252";
reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
FL-Build: Fidolook 2002 (SL) 6.0.2800.94 - 17/2/2005 21:04:59
X-Comment-To: Peter Yang [MSFT]
Message-ID: <eqiH#[email protected]>
Newsgroups: microsoft.public.access.adp.sqlserver
NNTP-Posting-Host: adsl-68-72-23-128.dsl.chcgil.ameritech.net 68.72.23.128
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.adp.sqlserver:23576
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

Hello Peter,
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 03
Feb 2006 05:45:35 GMT:

PYM> It appears that this issue is related to your custom database and it
PYM> is a development-related request that would be best addressed in the
PYM> developer newsgroups. The developer newsgroups are located at:

PYM> http://msdn.microsoft.com/newsgroups/default.asp.

Peter, for your information, this newsgroup _is_ developer newsgroup, and
the link you have posted is for the same newsgroups as on this server, only
with web interface (and with this newsgroup excluded for some reason). Next
time you post, you might want to get an idea where you are posting.

PYM> For further assistance on this issue, you may want to contact our
PYM> Develop Support Services by telephone so that a dedicated Support
PYM> Professional can assist you further with your request. To obtain the
PYM> phone numbers for specific technology request please take a look at
PYM> the web site listed below:

PYM> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS.

PYM> Your understanding on this will be greatly appreciated.

I believe the poster already has a very good understanding of where to get
help and where not to.


Vadim Rapp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top