Problem Entering Item Into A Table

Neil

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

Oct 9 '08 #1

Erland Sommarskog

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

Oct 9 '08 #2

Neil

"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

Oct 9 '08 #3

Roy Harvey (SQL Server MVP)

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

Oct 9 '08 #4

Erland Sommarskog

Neil (no****@nospam.net) writes:

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?

Presumably someone else connected and got that spid.

When you say "find the form that is open," are you referring to in my
Access app or elsewhere?

Access. The issue I referred to about a combo is an Access 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

Oct 10 '08 #5