Strange SP Problem

microsoft.public.sqlserver.programming

14 Jul 2005 4:28 PM

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.

14 Jul 2005 5:12 PM

Cowboy (Gregory A. Beamer) - MVP

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!
***************************

Show quote

"Neil" wrote:

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

14 Jul 2005 9:27 PM

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)

Show quote

"Neil" <nospam@nospam.net> wrote in message
news:uowBe.3403$dU3.99@newsread2.news.pas.earthlink.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.
>

14 Jul 2005 9:47 PM

Erland Sommarskog

Neil (nospam@nospam.net) writes:
Show quote

> 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, esq***@sommarskog.se

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