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