Connection.Execute & SQL Stored Procedure

I am hopeful that those of you with more experience in the compilation of
.asp, the connection object and MS SQL Server as the DB can help me out. I
have a web app that routinely calls stored procedures in a MS SQL DB.
In one web page, I make 6 calls with the Connection.Execute 'store
procedure'. Five of the six calls execute as expected. However; I have one
call in middle of the 6 that only executes the stored procedure partially.
This particular stored procedure does quite a bit of work within the DB, mak
e
3-4 select quiries and then a few updates to a single record. In this
procedure, one of SQL update statements is constructed in a loop to ensure
the appropriate fields are updated and no others. (The fields updated vary
with the input being used) This is where the procedure will "time out". I
have reduce/elimnated all the "debuggin" code that is no esential to the
store procedure. When the dubbuging print and select statements commented
out the procedure will execute further when called from the asp page.
Has anyone run across a situation where the .asp does not allow a stored
procedure to execute to completion before executing the next line of .asp
code? If so is there a way to force the Connection.Execute call to wait unti
l
the SQL server stored procedure has completed executing before processing th
e
next line of asp? Note the stored procedure does not explicitly return
anything to the asp code... also noteworthy, if the same stored procedure is
run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
procedure runs flawlessly and as expected to completion...
Anyone have an idea?Simon McLaren wrote:
> I am hopeful that those of you with more experience in the
> compilation of .asp, the connection object and MS SQL Server as the
> DB can help me out. I have a web app that routinely calls stored
> procedures in a MS SQL DB.
This would have been more on topic at .inetserver.asp.db. However, read on.

> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I
> have one call in middle of the 6 that only executes the stored
> procedure partially. This particular stored procedure does quite a
> bit of work within the DB, make 3-4 select quiries and then a few
> updates to a single record. In this procedure, one of SQL update
> statements is constructed in a loop to ensure the appropriate fields
> are updated and no others. (The fields updated vary with the input
> being used) This is where the procedure will "time out". I have
> reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements
> commented out the procedure will execute further when called from the
> asp page.
>
There are two timeout properties to be concerned with:
1. The IIS ScriptTimeout property, which controls the max time a page will
be allowed to execute on the server. This can be set globally using IIS
Manager to modify your website's application properties. It can also be set
at the page level using:
Server.ScriptTimeout = <some reasonable value>
2. The ADO Connection's CommandTimeout property. This controls the amount of
time a Command will allow a statement to execute. If set for a longer period
of time than the ScriptTimeout, the script may time out before the command
finishes executing.This is set via:
dim cn
set cn=createobject("adodb.connection")
cn.open ...
cn.CommandTimeout = <some reasonable value>
You should go to msdn.microsoft.com/library and read the documentation about
these properties.
Having said that, I need to add: Why call 6 procedures from ASP? One of the
main benefits of procedures is encapsulation, a huge benefit of which is
reducing the number of round trips to the database. You should create a new
procedure which accepts all the arguments required to call the other 6
procedures. This procedure calls the 6 procedures in turn and then returns
the overall result to the ASP client. One trip to the database. This can be
a huge performance booster.
I also need to add: you need to do some work to optimize the procedures you
have. You should not be allowing an asp page to time out waiting for
database activity to complete. Is there any way to take that time-consuming
activity offline?
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||I'll try to answer to your question as much as I understood it.
First of all, ASP is not compiled, it is interpreted language. Ok, this has
nothing with your question but I had to say it :)
ADO Connection/Command object has CommandTimeout property which defines for
how long it will wait for SP to execute.
I did not work with ASP and ADO for a long time. I had that "luck" to switch
to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
advice you to:
-Remove print commands from your SP, it may confuse ADO
-Set "set nocount on" at the beging of your code
-Close connection as soon as you finish you finish your work with DB
-Use SQL OLE DB provider for SQL Server
http://msdn.microsoft.com/library/d...
qlprovspec.asp
I hope this help
Regards,
Marko Simic
"Simon McLaren" wrote:

> I am hopeful that those of you with more experience in the compilation of
> .asp, the connection object and MS SQL Server as the DB can help me out. I
> have a web app that routinely calls stored procedures in a MS SQL DB.
> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I have one
> call in middle of the 6 that only executes the stored procedure partially.
> This particular stored procedure does quite a bit of work within the DB, m
ake
> 3-4 select quiries and then a few updates to a single record. In this
> procedure, one of SQL update statements is constructed in a loop to ensure
> the appropriate fields are updated and no others. (The fields updated vary
> with the input being used) This is where the procedure will "time out".
I
> have reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements commented
> out the procedure will execute further when called from the asp page.
> Has anyone run across a situation where the .asp does not allow a stored
> procedure to execute to completion before executing the next line of .asp
> code? If so is there a way to force the Connection.Execute call to wait un
til
> the SQL server stored procedure has completed executing before processing
the
> next line of asp? Note the stored procedure does not explicitly return
> anything to the asp code... also noteworthy, if the same stored procedure
is
> run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
> procedure runs flawlessly and as expected to completion...
> Anyone have an idea?
>|||Bob and Simic,
Thanks for taking the time to respond. Bob to answer you question about why
the 6 SP calls... Originally it the six where all one procedure.
Progromatically, the six procedures achimplish "steps" of a process, all of
which in this application could be (and are) occassionally executed
independently. I have created an encapsulating SP that in turn calls the 6
seperate SP's, but this non-completion issue forced me to back away from tha
t
idea to try and isolate the "offending" procedure.
The Connection/Command timeouts have been set to 60seconds. This failure
happens much quicker than 60 seconds.
By trial and error last w I tried commenting out all "debugging" code (to
include print and select statement indented to allow me to see what was
happening and if dynamicly generated SQL statements where correct.) in the
offending stored procedure, figuring not that it might be confusing ADO, but
rather that is was adding to the execution time of the stored procedure. An
d
presto, execution to completion for the .asp page and no debuggin "insight"
from the query analyzer.
While I "stumbled" across the issue on my own, it was not until reading your
responses that I understood what the actual issue was. With "debugging code
"
commented out of the stored procedures, I believe I can move back to a singl
e
SP and trip to the database.
Thanks,
Simon
"Simic Marko" wrote:
> I'll try to answer to your question as much as I understood it.
> First of all, ASP is not compiled, it is interpreted language. Ok, this ha
s
> nothing with your question but I had to say it :)
> ADO Connection/Command object has CommandTimeout property which defines fo
r
> how long it will wait for SP to execute.
> I did not work with ASP and ADO for a long time. I had that "luck" to swit
ch
> to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
> advice you to:
> -Remove print commands from your SP, it may confuse ADO
> -Set "set nocount on" at the beging of your code
> -Close connection as soon as you finish you finish your work with DB
> -Use SQL OLE DB provider for SQL Server
> http://msdn.microsoft.com/library/d...sqlprovspec.asp
> I hope this help
> Regards,
> Marko Simic
>
> "Simon McLaren" wrote:
>