Problem Entering Item Into A Table

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

re: Problem Entering Item Into A Table

Neil (nospam@nospam.net) writes: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, esquel@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

re: Problem Entering Item Into A Table

"Erland Sommarskog" <esquel@sommarskog.sewrote in message
news:Xns9B32EF064AE4BYazorman@127.0.0.1...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:
When you say "find the form that is open," are you referring to in my Access
app or elsewhere?

Thanks!

Neil

re: Problem Entering Item Into A Table

On Thu, 9 Oct 2008 16:54:15 -0500, "Neil" <nospam@nospam.netwrote:
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

re: Problem Entering Item Into A Table

Neil (nospam@nospam.net) writes:Presumably someone else connected and got that spid.
Access. The issue I referred to about a combo is an Access issue.


--
Erland Sommarskog, SQL Server MVP, esquel@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