Im running 3 distributed sql querys on a linked server (sits in a different province) through an asp page. Both databases are Sql Server

the select staments both kind of look like the following:

select * from openquery(MON, 'SELECT vmv_lab_code, variable_name as variable, method_code as method, unit_code as unit from tvar2 where vmv_lab_code in (''02011'',''T132-02'') order by vmv_lab_code');


WHen setting up my linked server i set all timeouts to 0.

Timeouts on the remote sever itself are set high enough (if that even makes a difference)

WHen i run the first two queries in sql query analyzer, together they run in about 1:48.

However, when running the queries in my asp page, when it gets to the second statement the page times out on the recordset.open.

shows this error statement:

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

Timeout expired

/envirodat_moncton/root/main/en/generatePivot.asp, line 124

I really dont get it because in addition, my connection timeout and commandtimeout properties are set to 99999.

Again, when i run these statments in query analyzer they work fine.

however they timeout when running in my asp code.

Any ideas

thanks,

dave

Some things that could be causing the timeout problem:

1) Syntax error in your ASP code. This happens to me quite often in both PHP and ColdFusion when pages take a long time to load and then time-out.

2) Older SQL Server ODBC driver. Need to upgrade it to the newest version.

See the following discussion thread in Experts Exchange:

http://www.experts-exchange.com/...rver/Q_10160418.html

3) Your query may be a bit complex in terms of the * rather than selecting specific columns and also the sub-query may be taking awhile to parse. Are you using any dynamic parameters in the SQL statement within your ASP script?
========================================
Buh Bye!

Cheers,
Me

1)There are no syntax errors

2)Im using the newest OLEDB driver for sql server. NOT ODBC

The discussion you suggested doesnt really apply to my situation. I still tried those fixes in vain but no luck.


3) Im not actually selecting *, i just kind of threw that in there to cut down on the lenght of text i had to paste in there. I am using dynamic parameters. Im testing with a large query right now. Something odd is that it chokes on a query that only takes 35 seconds. the one before it takes over a minute and works fine. when i cut out the 35 second one and use the next query which takes even longer. it works!

Here is the query that times out. I know this is the exact code that runs because i output it before the recordset attempts to open (thats where the error is):


THE CODE

________________________


Sql = "SET NOCOUNT ON " Sql = "SELECT vmv_lab_code, variable_name as variable, method_code as method, unit_code as unit from tvar2 where vmv_lab_code in (" & strVMVLAB_CODES & ") order by vmv_lab_code" sql = replace(sql, "'", "''") sql = "select * from openquery(MON, '" & sql & "');" 'Sql = "SELECT vmv_code from tvar1 where vmv_code in (" & strVMVLAB_CODES & ")" Response.Write("

") response.Write(sql) Response.Write("

") Cmd.CommandText = Sql Cmd.CommandTimeout = 99999 set rsVMV = server.CreateObject ("ADODB.Recordset") rsVMV.CursorLocation = adUseClient rsVMV.Open Cmd, , adOpenDynamic

____________________



THE ACTUAL STATEMENT THAT RUNS:

________________________________

select * from openquery(MONCTON1, 'SELECT vmv_lab_code, variable_name as variable, method_code as method, unit_code as unit from tvar2 where vmv_lab_code in (''02011-02'',''02041-02'',''02042-80'',''02042-81'',''02061-02'',''02061-80'',''02061-81'',''02065-81'',''02073-02'',''02078-81'',''06107-02'',''06154-02'',''06581-02'',''07110-02'',''07315-02'',''07601-01'',''07601-02'',''08102-81'',''09105-02'',''09106-02'',''10101-02'',''10110-02'',''102078-02'',''102079-02'',''102080-02'',''102081-02'',''102082-02'',''10301-02'',''10301-80'',''11103-02'',''12102-02'',''12107-02'',''13302-02'',''13305-02'',''14102-02'',''15413-02'',''16304-02'',''16309-02'',''17205-02'',''17209-02'',''19103-02'',''20110-02'',''25304-02'',''26304-02'',''26305-02'',''29305-02'',''29306-02'',''30304-02'',''48302-02'',''80315-02'',''82302-01'',''82302-02'',''97001-81'',''T00410-02'',''T107-02'',''T108-02'',''T109-02'',''T110-02'',''T111-02'',''T112-02'',''T113-02'',''T114-02'',''T115-02'',''T116-02'',''T118-02'',''T120-02'',''T121-02'',''T122-02'',''T123-02'',''T125-02'',''T126-02'',''T127-02'',''T132-02'') order by vmv_lab_code');

________________________

Thanks



dave

Try switching to the ODBC driver and see what happens.
========================================
Buh Bye!

Cheers,
Me