General discussion

Locked

ASP+SQL SPROC=server.scripttimeout

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)

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

ASP+SQL SPROC=server.scripttimeout

by a_williams In reply to ASP+SQL SPROC=server.scri ...

Point value changed by question poster.

Collapse -

ASP+SQL SPROC=server.scripttimeout

by Glen_McLeod In reply to ASP+SQL SPROC=server.scri ...

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

Collapse -

ASP+SQL SPROC=server.scripttimeout

by a_williams In reply to ASP+SQL SPROC=server.scri ...

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.

Collapse -

ASP+SQL SPROC=server.scripttimeout

by hclarius In reply to ASP+SQL SPROC=server.scri ...

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.

Collapse -

ASP+SQL SPROC=server.scripttimeout

by a_williams In reply to ASP+SQL SPROC=server.scri ...

The question was auto-closed by TechRepublic

Collapse -

ASP+SQL SPROC=server.scripttimeout

by chrandrob In reply to ASP+SQL SPROC=server.scri ...

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.

Collapse -

ASP+SQL SPROC=server.scripttimeout

by a_williams In reply to ASP+SQL SPROC=server.scri ...

The question was auto-closed by TechRepublic

Collapse -

ASP+SQL SPROC=server.scripttimeout

by a_williams In reply to ASP+SQL SPROC=server.scri ...

This question was auto closed due to inactivity

Back to Desktop Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums