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=@Merg eID

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]

Re: Strange SP Problem

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" <nospam@nospam. net> wrote in message
news:uowBe.3403 $dU3.99@newsrea d2.news.pas.ear thlink.net...[color=blue]
>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=@Merg eID
>
> 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.
>[/color]

[Erland Sommarskog]

Re: Strange SP Problem

Neil (nospam@nospam. net) writes:[color=blue]
> 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=@Merg eID
>
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp