proc hangs on web but executes in query analyzer

[Bob]

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,
Bob

[John Bell]

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

"Bob" <bo*@rwhelan.com> wrote in message
news:a0**************************@posting.google.c om...

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,
Bob

[Bob]

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

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

[Bob]

Thank you all for the replies. The problem was actually in one of the
MTS packages installed on our dev server. The first proc inserted 1
row into table 1 and the last proc selected that row. The last proc
was the one that would hang. One of our COM guys noticed this and
updated and reinstalled the package and everything works fine. Again
thank you all for the quick responses.

Bob