Problem with one table - Connection Timeout

[Julia]

Hello everyone,
I have an Access 2003 (2000 format) front end application with SQL 2000
back end that is being used by about 20 users on a daily basis (using
local copy of .mdb file). Ever since we migrated the data to the
company's SQL server we've been having problems with one particular
table (tblContact). We were using linked tables and ODBC connection
originally. When we tried to make any changes (additions/edits/deletes
using SQL update statements) to records in this table through Access,
there was a long
pause and then we would get the error:
ODBC--update on a linked table 'TABLENAME' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired. Once we got this
error, the table would lock and not allow for any changes. Usually it
would clear itself up after several minutes, or if we asked everyone to
log out.
So, I figured there had to be some issue with the ODBC / Jet engine and
I rewrote all the forms that added/edited contacts to use ADO
connection instead. Unfortunatelly, the problem still exists and now we
get the OLE DB error message: Connection Timeout.
Not only did it not solve the problem, but it seems like it made it
even worst as it happens now several times during the day and I hear
people complainig all the time. It drives me crazy and I have no idea
why this is happening. I guess there must be some locking going on with
this table, but why this one and how to fix it. What makes it even
worst is that I don't have the admin access to the SQL Server to run
some tools, and the lady who does administer it is a piece of work and
does not want to help at all. She says it is the application's fault,
and she can't help me. So, what am I supposed to do? Please, someone
help me, or I'll shoot myslef if I don't fix this soon (and I have 2
children at home :)

[Terry Kreft]

If you don't know what the problem is you can't fix it.

You don't know what the problem is because you can't get on the server to
investigate.

Talk to the DBA again. Point out that you accept it is the application but
you need her help and some server time to investigate the problem. If she
won't help then you have to escalate it to your boss/her boss.

If you don't then you're the one who ends up looking stupid not her.

--

Terry Kreft
"Julia" <ju******@netscape.net> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

Hello everyone,
I have an Access 2003 (2000 format) front end application with SQL 2000
back end that is being used by about 20 users on a daily basis (using
local copy of .mdb file). Ever since we migrated the data to the
company's SQL server we've been having problems with one particular
table (tblContact). We were using linked tables and ODBC connection
originally. When we tried to make any changes (additions/edits/deletes
using SQL update statements) to records in this table through Access,
there was a long
pause and then we would get the error:
ODBC--update on a linked table 'TABLENAME' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired. Once we got this
error, the table would lock and not allow for any changes. Usually it
would clear itself up after several minutes, or if we asked everyone to
log out.
So, I figured there had to be some issue with the ODBC / Jet engine and
I rewrote all the forms that added/edited contacts to use ADO
connection instead. Unfortunatelly, the problem still exists and now we
get the OLE DB error message: Connection Timeout.
Not only did it not solve the problem, but it seems like it made it
even worst as it happens now several times during the day and I hear
people complainig all the time. It drives me crazy and I have no idea
why this is happening. I guess there must be some locking going on with
this table, but why this one and how to fix it. What makes it even
worst is that I don't have the admin access to the SQL Server to run
some tools, and the lady who does administer it is a piece of work and
does not want to help at all. She says it is the application's fault,
and she can't help me. So, what am I supposed to do? Please, someone
help me, or I'll shoot myslef if I don't fix this soon (and I have 2
children at home :)

[Julia]

Thank you Terry,
I did escalate it to my boss and we are finally getting some help from
her. She run sp-who and sp_locks and there is deffinitely some blocking
going on, possibly deadlocks. I've checked the current indexes and
changed them as the primary field was indexed three times (once as a
primary key by default - clustered, then again explicitly, and yet
again as a part of a composit index). I don't know if that did the
trick, but today we had no problems using the db. So, I keep my fingers
crossed. If someone has some other ideas, then please shere them with
me as I'm affraid this problem may come back tomorrow.
Thank you once again.

[Julia]

Thank you Terry,
I did escalate it to my boss and we are finally getting some help from
her. She run sp-who and sp_locks and there is deffinitely some blocking
going on, possibly deadlocks. I've checked the current indexes and
changed them as the primary field was indexed three times (once as a
primary key by default - clustered, then again explicitly, and yet
again as a part of a composit index). I don't know if that did the
trick, but today we had no problems using the db. So, I keep my fingers
crossed. If someone has some other ideas, then please shere them with
me as I'm affraid this problem may come back tomorrow.
Thank you once again.

[Julia]

Database still working ok and it's been almost a week. I guess all
there was to it was wrong indexes set on the table.