Strange SP Problem

[Neil]

I have a strange situation. I have a stored procedure that is hanging upon
execution, but only some machines and not others. The db is an Access 2000
MDB using ODBC linked tables and a SQL 7 back end. The sp is executed as a
pass-through.

The sp is fairly simple:

UPDATE CUSTOMER
SET LastMergeName = [M].[Name]
FROM (CUSTOMER C INNER JOIN MergeItems I ON C.[Index] = I.[Index])
INNER JOIN Merges M ON I.MergeID = M.MergeID
WHERE M.MergeID=@MergeID

On the machines where this hangs, it only hangs when a form which is bound
to the Merges table is open. That form has a subform which is bound to a
query made up of the MergeItems and Customer tables. The subform appears to
be causing the sp to hang when it is open -- but, again, only on certain
computers.

Looking at the situation in Enterprise Manager, it shows two processes of
interest: one a Select statement, and the other an Update statement, with
the Select statement process blocking the Update statement process.
MergeItems is listed under "Locks / Object".

The data in the form is not in an edited state when the sp is run, so
there's no reason it should be locking the table. And, on my development PC
and on one at the client's site I was able to test it on, it runs fine, even
with the form open. But on two others (one Win 98 machine and one Win XP
machine) it hangs when the form is open.

Any ideas?

Thanks.

[Van T. Dinh]

I guess the potential to edit is ...

Try setting the "Record Locks" Property of the SourceObject of the Subform
to No Locks.

If sp hangs, try looking at the Current Activities in EM and identify the
blocking connection and the action of the blocking conection.

--
HTH
Van T. Dinh
MVP (Access)

"Neil" <no****@nospam.net> wrote in message
news:uo***************@newsread2.news.pas.earthlin k.net...

I have a strange situation. I have a stored procedure that is hanging upon
execution, but only some machines and not others. The db is an Access 2000
MDB using ODBC linked tables and a SQL 7 back end. The sp is executed as a
pass-through.

The sp is fairly simple:

UPDATE CUSTOMER
SET LastMergeName = [M].[Name]
FROM (CUSTOMER C INNER JOIN MergeItems I ON C.[Index] = I.[Index])
INNER JOIN Merges M ON I.MergeID = M.MergeID
WHERE M.MergeID=@MergeID

On the machines where this hangs, it only hangs when a form which is bound
to the Merges table is open. That form has a subform which is bound to a
query made up of the MergeItems and Customer tables. The subform appears
to be causing the sp to hang when it is open -- but, again, only on
certain computers.

Looking at the situation in Enterprise Manager, it shows two processes of
interest: one a Select statement, and the other an Update statement, with
the Select statement process blocking the Update statement process.
MergeItems is listed under "Locks / Object".

The data in the form is not in an edited state when the sp is run, so
there's no reason it should be locking the table. And, on my development
PC and on one at the client's site I was able to test it on, it runs fine,
even with the form open. But on two others (one Win 98 machine and one Win
XP machine) it hangs when the form is open.

Any ideas?

Thanks.

[Erland Sommarskog]

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

I have a strange situation. I have a stored procedure that is hanging upon
execution, but only some machines and not others. The db is an Access 2000
MDB using ODBC linked tables and a SQL 7 back end. The sp is executed as a
pass-through.

The sp is fairly simple:

UPDATE CUSTOMER
SET LastMergeName = [M].[Name]
FROM (CUSTOMER C INNER JOIN MergeItems I ON C.[Index] = I.[Index])
INNER JOIN Merges M ON I.MergeID = M.MergeID
WHERE M.MergeID=@MergeID

On the machines where this hangs, it only hangs when a form which is
bound to the Merges table is open. That form has a subform which is
bound to a query made up of the MergeItems and Customer tables. The
subform appears to be causing the sp to hang when it is open -- but,
again, only on certain computers.

Looking at the situation in Enterprise Manager, it shows two processes of
interest: one a Select statement, and the other an Update statement, with
the Select statement process blocking the Update statement process.
MergeItems is listed under "Locks / Object".

The data in the form is not in an edited state when the sp is run, so
there's no reason it should be locking the table. And, on my development
PC and on one at the client's site I was able to test it on, it runs
fine, even with the form open. But on two others (one Win 98 machine and
one Win XP machine) it hangs when the form is open.

Seems like some clients are able to get the result set from Merges
in one go with a client-side cursor, whereas others use a server cursor.

I vaguely recall that there is some threashold in Access around 450
items that can cause this behaviour.

All and all, it seems to mainly be an Access issue. You must somehow
get Access to get all items for the subform to avoid blocking. How you
can to that with linked tables, I have no idea, as I don't know Access.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp