OLEDB provider for sql server timeout expires

[dbenoit64]

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 & &quot order by vmv_lab_code" sql = replace(sql, "'", "''&quot sql = "select * from openquery(MON, '" & sql & "');" 'Sql = "SELECT vmv_code from tvar1 where vmv_code in (" & strVMVLAB_CODES & &quot" Response.Write("

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

&quot Cmd.CommandText = Sql Cmd.CommandTimeout = 99999 set rsVMV = server.CreateObject ("ADODB.Recordset&quot 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