473,728 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Jul 20 '05 #1
3 2839
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.co m> wrote in message
news:a0******** *************** ***@posting.goo gle.com...
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

Jul 20 '05 #2
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

Jul 20 '05 #3
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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2292
by: CSDunn | last post by:
Hello, I have a combo box (Combo7) that needs to call a function during the After Update event of the combo box. The function resides in an Access 2000 ADP Module called MMAnswerData_code. The following is the code of the function: Public Function SubFormRS(FrmTarget As Object) Forms!frmAD_OpeningForm!FrmTarget.RecordSource = "EXEC dbo.AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter
8
2081
by: CSDunn | last post by:
Hello, I have a situation in which I need to address three SQL Server 2000 Stored Procedure parameters in the OnClick event of an Option Group. The Option Group lives on an Access 2000 ADP form. In another situation where I had to set the RowSource of a combo box based on a parameter value that was delivered to a proc from another combo box, I did this: Private Sub FirstCombo_AfterUpdate()
0
4197
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS Excel 2003, I have VBA code that creates and executes queries using the Access database, and returns the results to an Excel sheet. The first time the query is executed, results are returned to Excel in usually less than 10 seconds. However, if the...
14
3651
by: aaron kempf | last post by:
I find that ADP does not support any Stored Procedures that use the 'CREATE PROC spHAPPY' syntax. CREATE PROC syntax is listed in books online. This syntax should be supported Here is a scenario: 1) create proc using query analyzer and CREATE PROC spHAPPY syntax 2) open this proc in ADP
4
3055
by: Learner | last post by:
Hi there, I have a storec proc that schedules a Sql job and finally it returns 0 then it was successfull and if it returns 1 then its unsuccessful. Now when i run the stored proc in the query analyzer its returning SQLServerAgent is not currently running so it cannot be notified of this action. Stored Procedure: aisinspect.dbo.SP_ChangeInspectionDateNew
2
2689
by: Pierre Rouleau | last post by:
Hi all, I have a consistent test case where os.popen3() hangs in Windows. The system hangs when retrieving the lines from the child process stdout. I know there were several reports related to os.popen3() hanging under Windows in this group before. I stumbled on a case where a piece of code works in some occasions and hangs consistently given different data. It performs exactly the same on 3 different computers running Windows:
1
2292
by: central_scrutinizer | last post by:
I have an ASP that has been working fine for several months, but it suddenly broke. I wonder if windows update has installed some security patch that is causing it. The problem is that I am calling a stored procedure via an ASP (classic, not .NET) , but nothing happens. The procedure doesn't work, and I don't get any error messages. I've tried dropping and re-creating the user and permissions, to no avail. If it was a permissions...
2
1639
by: dbanalyst | last post by:
I have a VB6 app that executes an SQL stored procedure that returns a recordset that I loop through 1 record at a time. During the looping process, I am attempting to insert contents from the recordset to another SQL table. This seems to work with small recordsets, but hangs the app if the recordset is large. When I use the SQL Analyzer tool and execute the "insert" command it works fine, so the syntax is correct. Also, if the VB6 calls a...
3
1815
by: dbanalyst | last post by:
I have a VB6 app that executes an SQL stored procedure that returns a recordset that I loop through 1 record at a time. During the looping process, I am attempting to insert contents from the recordset to another SQL table. This seems to work with small recordsets, but hangs the app if the recordset is large. When I use the SQL Analyzer tool and execute the "insert" command it works fine, so the syntax is correct. Also, if the VB6 calls a...
0
8895
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8755
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9267
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6012
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4523
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4787
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3231
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2645
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2159
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.