SQL Timeout

[Lin Ma]

Dear all,

I need run a few large queries for one time only. But I get scripttimeout.
So I set up server.ScriptTimeout value. It works fine.

But for one query, system gives me the following error:

Microsoft OLE DB Provider for SQL Server (0x80040E31)
Timeout expired

No matter how much I increase ScriptTimeout value, I still have the same
error.

Is there a setting for SQL server timeout value?

Thanks,

Lin Ma

[Richard K Bethell]

Script timeout value has no effect on SQL timeout. You have to change the
value of your ADODB.Connection's CommandTimeout property to something long
enough to allow your SQL server to do the work.

R.

"Lin Ma" <a@a.com> wrote in message
news:ODGDLlzkDHA.1728@TK2MSFTNGP11.phx.gbl...

> Dear all,
>
> I need run a few large queries for one time only. But I get scripttimeout.
> So I set up server.ScriptTimeout value. It works fine.
>
> But for one query, system gives me the following error:
>
> Microsoft OLE DB Provider for SQL Server (0x80040E31)
> Timeout expired
>
> No matter how much I increase ScriptTimeout value, I still have the same
> error.
>
> Is there a setting for SQL server timeout value?
>
> Thanks,
>
> Lin Ma
>
>

[jimnpd]

I have developed a web page in aspx that runs a sql statement against a
database with over one million records. My problem is that after approximately
40 seconds I get the following error sql connection error.:disgust;

[CMBergin]

Does your statement run in your database's test environment? (i.e, Query
Analyzer for SQL Server or TOAD or something for Oracle).

"jimnpd" <webforumsuser@macromedia.com> wrote in message
news:d04ju1$nil$1@forums.macromedia.com...

> I have developed a web page in aspx that runs a sql statement against a
> database with over one million records. My problem is that after

approximately

> 40 seconds I get the following error sql connection error.:disgust;
>

[jimnpd]

yes it run runs.

The exact error is System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

[CMBergin]

The reason I ask is because those test environments will tell you what the
execution time is. Knowing the execution time, and knowing the script
timeout value set on your web server, you can decide if your query is too
slow or your timeout is too quick and take appropriate action to fix
whichever side you want to fix. Make sense?

"jimnpd" <webforumsuser@macromedia.com> wrote in message
news:d05bgj$t5$1@forums.macromedia.com...

> yes it run runs.
>
> The exact error is System.Data.SqlClient.SqlException: Timeout expired.

The timeout period elapsed prior to completion of the operation or the
server is not responding.

[jimnpd]

Yes I ran the script in sql anaylser and it took approx. 25 minutes for a years worth of data. Can you tell me how to change the timeout ????

[CMBergin]

Whew! 25 minutes?
I think we need to optimize your query first....
Get an execution plan from QA; check for slow operations like table scans.
A table scan of a large table is SLOW. Defining appropriate indices will
speed the query along like you wouldn't believe. Also post your SQL
statement. There might be more efficient ways to get your data.

But the timeout can be altered in IIS (Right click->Properties->[Virtual
Directory]->Configuration->[App Options], set the ASP script timeout there)
or by altering the object itself, but I don't remember how since I don't
have to do it. Check [url]www.w3schools.com[/url] and look through their ADO
reference; it will be there.


"jimnpd" <webforumsuser@macromedia.com> wrote in message
news:d0qirr$d6t$1@forums.macromedia.com...

> Yes I ran the script in sql anaylser and it took approx. 25 minutes for a

years worth of data. Can you tell me how to change the timeout ????

[jimnpd]

here is my sql statement; the one field text mssage is a blob

SELECT DISTINCT o.CreateForward, o.VIN, o.VehState, o.VehPlateNbr,
o.VehPlateYr, o.VehMake, o.VehType, o.ReqUniqueID, i.Dept
FROM ResponseOUT o INNER JOIN RequestIN i ON o.ReqUniqueID = i.UniqueID
WHERE o.CreateForward>=@StartDate AND o.CreateForward<=@EndDate AND
o.VehPlateNbr LIKE '%' + UPPER(@Plate) + '%' AND o.VehState LIKE '%' +
UPPER(@State) + '%' AND o.VehMake LIKE '%' + UPPER(@Make) + '%' AND
o.TextMessage LIKE '%' + (@Color) + '%' AND o.TextMessage LIKE '%' + (@VehType)
+ '%' AND o.Vin LIKE '%' + (@Vin) + '%' AND o.VehPlateNbr >'0' AND i.Dept
Like UPPER(@Dept) AND o.TextMessage LIKE '%' + (@Doors) + '%'