Timeout Expired

[cags]

hi
I am trying to run a long stored procedure(approx 2 minutes) from an asp page which should then go on to display the returning values (about 4400 rows worth)...when I run it and capture the error, I get 2147217871 - timeout expired.

Anyone know how to fix this?

 

[mgallot]

Change the Connection Pooling retry time to a higher number in the ODBC settings in the control panel.

 

[tlbroadbent]

Have you optimized the stored procedure? Do the tables involved have indexes? Are you using cursors in the SP? If so, could the cursors be eliminated in favor SQL operations on relations? I'd make sure the procedure worked well before increasing timeout values.

Users hate stored procedures that take 2 minutes to execute and return data to an ASP page. SQL is capable of handling tens of thousands of records in a few seconds if queries are optimized and proper indexes are installed. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Guest_imported]

The default timeout for a query to execute is 30 seconds in most OLE DB providers. You can change it in one of the following two ways:

if you are running your query using connection.execute method then before you start the query set the command timeout to a higher value using:
connection.commandtimeout = 120 'seconds

If you use command object for your query then do

command.commandtimeout = 120 'seconds

Setting commandtimeout to 0 makes it infinite.

Hope this helps