473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

ASP cannot run stored proc until the web user has run the proc in Query Analyzer

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 problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.

I finally got it to work again, but this is not a viable solution for
our production environment:

1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.

After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.

Feb 22 '07 #1
1 2262
central_scrutinizer,

Does the ASP page hang, timeout or just return with no results? If it hangs
or times out, this may be happening:

1. The command is executed from the ASP page.
2. SQL Server needs to allocate more disk space (data or log) and performs
an autogrow of a large amount of disk space. This could take an extended
period of time.
3. The user cancels the ASP page or the page times out.
4. SQL Server cancels the transaction and also cancels the need for
additional disk space.
5. If steps 1-4 are repeated, you get the same result.
6. A user runs the stored procedure in QA allowing it to finish.
7. SQL Server allocates the addional space.
8. The ASP page now runs fine because the space has been allocated (until
the next time the database needs to allocate more space).

-- Bill

1. We would run the stored
"central_scrutinizer" <cb*****@hotmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...
>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 problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.

I finally got it to work again, but this is not a viable solution for
our production environment:

1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.

After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.

Feb 23 '07 #2

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

Similar topics

3
by: Bob | last post by:
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...
5
by: Warren Wright | last post by:
Hi group, I have a select statement that if run against a 1 million record database directly in query analyzer takes less than 1 second. However, if I execute the select statement in a stored...
2
by: Daniel | last post by:
hi ng, i am newbie to sqlserver and my problem seems simple, but i didn't find information about it: How can i display the RETURN @x value of a stored procedure in the sql analyzer of the...
3
by: Gary Besta | last post by:
I am trying to add a simple case statement to a stored procedure or user defined function. However when I try and save the function/procedure I get 2 syntax errors. Running the query in query...
4
by: William F. Robertson, Jr. | last post by:
One of my developers came to me with this question and I don't have an answer for them. The only suggestion I had for them was to change the return to a output parameter and put a try catch around...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
2
by: rokslide | last post by:
Hi all, I have a web application which (until a few weeks ago) was running fine. Now, periodically, a stored procedure will stop responding and timeout when called via the web application. The...
0
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.