Having trouble with inserting a record into a table. It's a list of names.
But, for some reason, it won't take a particular name. When a user tries to
enter a name into the table, the system hangs (users are using Access 2000
via ODBC linked table).
I went into QA (SQL 7) and tried an Insert Into statement, but I got the
same result from QA: the system just hung. No error message. After 5
minutes, the query was still trying to execute.
There's no problem entering any other name into the table. Also, I have a
copy of the database on my development machine. And on my development
machine copy, there's no problem entering this name.
So something must have happened when the user was entering this name that's
causing the live copy of the database to reject the name, though, since it
works on my machine, there's nothing intrinsically wrong about the name in
terms of violating any rules.
Ideas?
Thanks!
Neil 4 1661
Neil (no****@nospam.net) writes:
Having trouble with inserting a record into a table. It's a list of
names. But, for some reason, it won't take a particular name. When a
user tries to enter a name into the table, the system hangs (users are
using Access 2000 via ODBC linked table).
I went into QA (SQL 7) and tried an Insert Into statement, but I got the
same result from QA: the system just hung. No error message. After 5
minutes, the query was still trying to execute.
There's no problem entering any other name into the table. Also, I have a
copy of the database on my development machine. And on my development
machine copy, there's no problem entering this name.
Check for blocking with sp_who2. Run your INSERT, and then in another
window run sp_who. Check for numbers in the Blk column, that's the spid
of the blocker. Locate the client of that spid, find the form that is
open, and on that form find a combo with many elements. Scroll to the
bottom of that combo. Go back to the INSERT window. The name should now
be inserted.
This happens because when a combo is linked against a column, Access
does not complete the query, and if there are more than approx 450 rows,
some data remains unfetch in SQL Server, causing locks to be remained.
Disclaimer: I have never worked with Access, but I've seen this
discussed on the newsgroups in the past, so this certainly is a bit
of speculation. But I'm quite confident that this is a blocking issue.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Neil (no****@nospam.net) writes:
>Having trouble with inserting a record into a table. It's a list of names. But, for some reason, it won't take a particular name. When a user tries to enter a name into the table, the system hangs (users are using Access 2000 via ODBC linked table).
I went into QA (SQL 7) and tried an Insert Into statement, but I got the same result from QA: the system just hung. No error message. After 5 minutes, the query was still trying to execute.
There's no problem entering any other name into the table. Also, I have a copy of the database on my development machine. And on my development machine copy, there's no problem entering this name.
Check for blocking with sp_who2. Run your INSERT, and then in another
window run sp_who. Check for numbers in the Blk column, that's the spid
of the blocker. Locate the client of that spid, find the form that is
open, and on that form find a combo with many elements. Scroll to the
bottom of that combo. Go back to the INSERT window. The name should now
be inserted.
This happens because when a combo is linked against a column, Access
does not complete the query, and if there are more than approx 450 rows,
some data remains unfetch in SQL Server, causing locks to be remained.
Disclaimer: I have never worked with Access, but I've seen this
discussed on the newsgroups in the past, so this certainly is a bit
of speculation. But I'm quite confident that this is a blocking issue.
--
Thanks, Erland! I found the process that was blocking; but I couldn't
determine whose client that was (all users use the same SQL login through
the linked tables, and the Host column wasn't very helpful. But I did a Kill
Process on the process that was blocking, and then the query completed. So
thanks!
Question, though: when I did Kill Process, the process didn't go away. After
doing a Refresh under Current Activity (in Enterprise Manager), it still
showed the process same as before; but the process was no longer blocking
the other one. Any idea why the process still showed after doing a Kill
Process?
Also, re:
Locate the client of that spid, find the form that is
open, and on that form find a combo with many elements. Scroll to the
bottom of that combo. Go back to the INSERT window. The name should now
be inserted.
When you say "find the form that is open," are you referring to in my Access
app or elsewhere?
Thanks!
Neil
On Thu, 9 Oct 2008 16:54:15 -0500, "Neil" <no****@nospam.netwrote:
>Question, though: when I did Kill Process, the process didn't go away. After doing a Refresh under Current Activity (in Enterprise Manager), it still showed the process same as before; but the process was no longer blocking the other one. Any idea why the process still showed after doing a Kill Process?
Probably doing a ROLLBACK of whatever changes it was in the middle of.
ROLLBACK can take longer than the original change, as it is not as
thoroughly optimized.
Roy Harvey
Beacon Falls, CT This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mosher |
last post by:
Hi all,
I have an issue with php and/or mysql. I have a php form that writes "items"
to a mysql database, including a description of the item. On the mysql
server, "magic_quotes_gpc" is ON.
I...
|
by: Bert |
last post by:
We are using MS Access97. Now by entering a new item to the database
the problem occurs.
We are using a subform and a form, by clicking a field in the subform
the form produces an error regarding...
|
by: Steve Bishop |
last post by:
I have a form with a search text box and a grid that displays the
results. Within the grid, I have edit command links set up to
response.redirect to another page that puts the DataKey value in the...
|
by: Inigo Jimenez |
last post by:
I have an ASP .net web application installed in a Windows 2003 server.
This web application has a webform that has a Datagrid.
This Datagrid is filled with the data of a SQL table.
I have a...
|
by: Miky |
last post by:
I have the following problem:
I am populating a dataset but, sometimes (NOT always), the
NewRow method works like the Add method.
SetRightDetail.RightDetail.Rows.Clear()
For Each Right In...
|
by: Ron |
last post by:
Hi All,
Using Access2000, winXP.
Table 1 = tblClients displayed on frmClients via qryClients. 2nd table =
tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails
shown on...
|
by: cctham |
last post by:
Hi,
I have an issue with php and/or mysql. I have a php form that writes "items"
to a mysql database, including a description of the item.
I am testing it now by putting special characters in...
|
by: deena22 |
last post by:
hello,
i'm using 'Access database' and VB 6.0. My database is named ' timesheet' and it contains a table named 'tabletimesheet'. The table contain the following fields: 'staffname, stafftype,...
|
by: nth |
last post by:
I wan to filter the database show out information by using the user login name. Means that when that user login by entering their names n password, if correct then it will go to the other page by...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |