General discussion

  • Creator
    Topic
  • #2092254

    ASP+SQL SPROC=server.scripttimeout

    Locked

    by a_williams ·

    i have created a an asp page that contains
    5 search boxes and 5 drop down boxes. the search boxes contain the search strings while the drop downs specifcy what field to search for that search string. i created 3 sql stored procedures. the first creates a temporary table of all records. the second deletes records from the temporary table that don’t match any of the search criteria.
    the third, which is the one called from the asp page is the one that sends the search parameters to the second one. my problem is that when i run the stored procedure from the query analyzer it runs fine ~5 seconds. if i call it from an asp page it times out
    i’ve increased the server.scripttimeout to over 5 minutes and it still times out. what gives? i’d appreciate any help. here’s the code that calls the stored procedure.
    Set objConn = Server.CreateObject “ADODB.Connection”)
    objConn.Open strConn
    strQuery = “stored procedure ‘param’,’param'”
    Set objRS = objConn.Execute(strQuery)

All Comments

  • Author
    Replies
    • #3878308

      ASP+SQL SPROC=server.scripttimeout

      by a_williams ·

      In reply to ASP+SQL SPROC=server.scripttimeout

      Point value changed by question poster.

    • #3879341

      ASP+SQL SPROC=server.scripttimeout

      by glen_mcleod ·

      In reply to ASP+SQL SPROC=server.scripttimeout

      I’ve never seen the Execute method coded quite like that, and I looked in MSDN and couldn’t find a syntax example that uses your method either.

      Use a Command object and append some parameters:

      Set cmCommand = Server.CreateObject(“ADODB.Command”)
      cmCommand.CommandType = adCmdStoredProc
      cmCommand.CommandText = “stored proc name”
      ‘for each parameter, all on 1 line:
      cmCommand.Parameters.Append cmCommand.CreateParameter(“Name”, type, direction, [size (if a string)], [value (if you have it)])

      ‘you can specify the value later:
      cmCommand.Parameters(“Name”).Value = [whatever value]

      Set objRs = Server.CreateObject(“ADODB.Recordset”)
      objRs.Open cmCommand

      This is the accepted way of using Stored procedures and parameters.

      Glen

      • #3869884

        ASP+SQL SPROC=server.scripttimeout

        by a_williams ·

        In reply to ASP+SQL SPROC=server.scripttimeout

        strQuery and strConn are variables i created.
        strQuery is the query string (select * from….) and strConn is a string that contains a DSN-less db connection.
        i found that when calling a recordset from a stored procedure that creates a temporary table, somehow the returned recordset doesn’t get returned to an asp page. i’m not quite sure the reason for this but the solution i’ve found is to use SET NOCOUNT ON in the stored procedure. This forces a recordset to be returned no matter what. Eventhough this solved my problem, i consider it to be only a work around and would still like to know the correct way to fix this problem.

    • #3888319

      ASP+SQL SPROC=server.scripttimeout

      by hclarius ·

      In reply to ASP+SQL SPROC=server.scripttimeout

      The basic SQL construct is made up of SELECT,
      FROM, WHERE, GROUP BY. I capture the necessary input from the user(using forms) to complete the basic construct. Apply logic to develop the proper SQL string(“SQL”).(I have 6 fields, 4 tables & 4 levelsof aggregation). Then (assuming opened connection)

      Set recordSet=Server.CreateObject (“ADODB.Connection”)
      recordSet.Open SQL, connectToDatabase

      Then close connect & set recordset to Nothing

      More details upon request.

    • #3840906

      ASP+SQL SPROC=server.scripttimeout

      by chrandrob ·

      In reply to ASP+SQL SPROC=server.scripttimeout

      You’re definitely going about this the right way by using stored procedures, and the way that you are executing the stored procedures is just fine.

      My question is why are you using three stored procedures instead of just one? You can do this whole process with just one stored procedure. This is really slowing your process down.

      From your description of the process, it sounds like you’re not actually using real temp tables, but merely tables that you consider to be temporary. An actual temp table is automatically dropped when the stored procedure ends. and the second and third sp’s would not be able to access it. Using a temp table or a real table treated as a temp table would also slow down your query.

      If you are using a real table instead of an actual temp table, then the sp will lock the table while it’s inserting into, deleting from, or selecting from it, and other queries will have to wait their turn before being processed. This could cause a long backlog of queries on a busy page — timeouts galore.

      Your best bet is to simply run a select query from the table that contains all of the data. Save yourself some headaches though. On all select queries, add a “with(nolock)” optimizer hint, like this:

      Select * From MyTable with(nolock) Where blah, blah, blah ……

      If you need further help with this one, email me at chrandrob@home.com.

    • #3701808

      ASP+SQL SPROC=server.scripttimeout

      by a_williams ·

      In reply to ASP+SQL SPROC=server.scripttimeout

      This question was auto closed due to inactivity

Viewing 4 reply threads