Posted by Not4u on 12/20/05 19:42

Hello

Config : SQL 2000 on WIN 2000 (IIS 5.0)

In my ASP page for some queries i have this error :

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired


My asp page calls a stored procedure passing many parameters.
I used the SQL profiler to get the exact stored procedure with all
parameters.

If i execute stored procedure in the Query Analyzer, it's execute
successfully in 3-4 seconds.
After executing 2-3 times the stored procedure in the Query Analyzer,
the error disapear from the ASP Page which runs fine and quickly.

My procedure is too long to be detailled here, but to do short, it's
look for the availabilities (the stock) of different products for a
desired length of time, with different parameters (color,size,etc..).
My main table look like :

Day | Id_prod | Provider | Stock | Price
1 1 1 2 3
1 2 1 1 2
1 1 2 4 5
1 2 2 0 4
2 1 1 1 9
2 2 1 3 7
2 1 2 1 7
2 2 2 4 6
...
.....
366 1 1 4 4
366 2 1 1 5
366 1 2 2 8
366 2 2 0 9

The primary key is : day,Provider,Id_prod
And the main sql statment in my stored procedure :

IF @end>@begin
INSERT INTO #tmptable
SELECT Id_prod,Provider,MIN(stock) FROM mytable WHERE day >= @begin
AND day <=@end
ELSE
INSERT INTO #tmptable
SELECT Id_prod,Provider,MIN(stock) FROM mytable WHERE day>=@begin OR
day<=@end)

Note : I use a temporary table to use paging, i just display 10
results/pages.

The problem only appears if @end<@begin (exemple; which product is
available from dec 29th to jan 2nd in blue color)

Any help would be much appreciated.
Thank and happy Christmas.



PS:If i set the timeout CommandTimeout = 9999 the problem is resolve but
it's not a solution for me.

[Back to original message]