OLEDB provider for sql server timeout expires

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OLEDB provider for sql server timeout expires

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2003-06-03 : 09:27:35

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

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-03 : 09:42:13

Wow, that's a lot of parameters for an IN statement. Where are you getting strVMVLAB_CODES from? Possibly a Form POST with lots of checkboxes? If you are getting these values from another table you'll be a lot better off using an INNER JOIN.

quote:
rsVMV.Open Cmd, , adOpenDynamic

Also why are using adOpenDynamic? Use adOpenSnapshot, I dont suppose you will be updating any of the values in the recordset.

Owais

dbenoit64
Starting Member

36 Posts

Posted - 2003-06-03 : 10:20:33

Tried the adOpenSnapshot

tried disconnected recordsets.. nothing works

the values passed to the In function are dynamic. this is probably the biggest query that would be ran but even smaller ones come up with problems.

the odd thing is that it is a shorter query than the first one where the first one does not timeout.??

any ideas

db

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources