Can't add new records during office hours

[Debbie]

I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.

Debbie

[paull]

My best guess would be a record lock. Something in your front end is locking the table so that inserts are being put on hold. It might be an open records set or soething like that.

I would check the locks via Enterprise manager during the day to see what connection has that table locked.

HTH
Paul

------------
Debbie at 7/7/00 11:46:34 AM


I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.

Debbie

[bp margolin]

Debbie,

My first guess would be that the issue is that the table is locked (blocked) by some process or user. You might check out the section "Understanding Locking in SQL Server" in the SQL Server 7.0 Books Online as a first step. After that, check out the section "sp_lock (T-SQL)" (again in Books Online), and see if running sp_lock gives you some insight.

------------
Debbie at 7/7/00 11:46:34 AM


I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.

Debbie

[Steve]

Debbie,

I suggest you, or the network admin, look at what processes are running on that server. Are there other SQL Server databases on the server? Is it running other server apps. like Exchange, SMS, or a web server? The problem you describe sounds like the server is overloaded when everyone is in the office and active.

Steve

------------
Debbie at 7/7/00 11:46:34 AM


I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.

Debbie

[Bob Gross]

I think your problem is Access. If you have a table open in the Access front end, or an open form with the table as the record source, Access is holding a lock on the table. To prove this, after hours when you are the only one online, open the front end as your users do and then try to insert records.
The solution is to write the Access front end as if it were VB, that is open the recordset, populate the form and then close the recordset. To save open the recordset, write the form info to the table and then close the recordset.
(This also works if you have a large,for Access, number of users in an Access database).


------------
Steve at 7/7/00 1:42:36 PM

Debbie,

I suggest you, or the network admin, look at what processes are running on that server. Are there other SQL Server databases on the server? Is it running other server apps. like Exchange, SMS, or a web server? The problem you describe sounds like the server is overloaded when everyone is in the office and active.

Steve

------------
Debbie at 7/7/00 11:46:34 AM


I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.

Debbie