Connection.Execute and SQL stored procedures

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

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 until
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?

.

Relevant Pages

  • RE: exec sp_help_job user account rights
    ... wrapper in which u can call original stored procedure sp_help_job using “WITH ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... It looks like it is not going to be an easy job getting it to work on the SQL Server 2005 and Express:(. ... 'Then you can execute the command and then retrieve the ID ... I am 100% sure this code works against any version of SQL Server, as long as the connection is OK and the SP has two parameters @varCompany as Input and @Return_Value as Output. ... I do not think your problem is due to difference of SQL Server2000 and SQL Server2005, unless your stored procedure has some thing that only works in SQL Server2000, not SQL Server 2005. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: exec sp_help_job user account rights
    ... I don’t know whether it work or not, but u can create a stored procedure ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.security)
  • Return value from Stored Procedure
    ... I've posted this question on the aspnet NG where I got some useful responses but they've yet solved my problem, then I find this classic asp NG, and hopefully, someone can spot the error right away. ... I inherited some legacy ASP codes in my office. ... The original code's back-end is using the SQL Server 2000 and I am testing to use it on the Express edition. ... Here is the shorten version of the stored procedure code: ...
    (microsoft.public.data.ado)