sql server timeouts on remote server query

[dbenoit64]

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

 

[AkhilDwivedi]

Hi dave,

I thought you don't have index on "vmv_lab_code" field. just put an index on this field and try again