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.

 

[Guest]

Access often locks when there is no need to lock. Most times this is due to
the fact that forms CAN potentially edit data. If you make sure the Access
database is multi-user it stops certain types of contention, but you may
still have issues.

This is not the best forum for this question, IMO, as it is the way Access
handles linked servers that is the issue more than SQL Server. If this were
SQL, I would aim at locking hints, realizing you can end up with some dirty
reads at times if you go too optimistic. I am not sure if you can specify
locking hints in Access linked tables.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[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.

 

[Erland Sommarskog]

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.