sql server (alternate): proc hangs on web but executes in query analyzer -- sql server (alternate)

proc hangs on web but executes in query analyzer

bob NO[at]SPAM rwhelan.com
7/30/2003 4:26:56 AM

sql server (alternate):

I'm currently updating one of our web sites and have encountered a
strange problem. The page that is giving me the problem is written in
ASP and hits a SQL 2K DB. When I click submit I have 4 procs that
need to be executed. I always get a sql server timeout error. I
opened up Profiler and traced the events. The page hangs on the last
proc called which is basically a select * where id = @id. If I run
the same proc that hangs in query analyzer, literally copy from
profiler into query analyzer the proc runs no problem but when I'm
hitting it from the web it hangs. The proc takes in 1 argument which
I see when I view the trace in profiler. The id getting passed to the
proc is correct. I don't think this is a web problem because the COM
object that runs the last 2 procs works on other production
applications with no problems. I was wondering if anyone has any
suggestions. I'm not a dba but would like to know what the problem is
for future reference. I pasted the proc below just for the hell of
it. FYI, I changed the name of the proc, table and columns for
security purposes.

CREATE PROCEDURE [dbo].[spName]
@TNum integer
AS
SELECT M.*
FROM tblName M
WHERE (M.[idColumn] = @TNum)
GO

Thanks in advance,

Re: proc hangs on web but executes in query analyzer

bob NO[at]SPAM rwhelan.com
7/30/2003 10:21:29 AM

John,

Thanks for the quick reply. I can execute the 4 procs in a batch and
it goes through. I did notice that when I run profiler the proc that
hangs has a lock:timeout - then lock:cancel - then it releases the
lock. The problem is that it doesn't release the lock until after the
timeout has expired. I was wondering if there is anyway I could
release any or all locks associated with table A directly in the proc?

Thanks again

[quoted text, click to view]

Re: proc hangs on web but executes in query analyzer

John Bell
7/30/2003 12:39:43 PM

Hi

Have you checked out sp_who2 to see if there is any blocking, and have you
tried running all the procs together rather than just the one?

John

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.