excel vba - ADODB Command.Execute Timeout -

i seeing timeout error when running query excel vba

the query takes <2 seconds sql server management studio, vba timeout happens @ 2 minutes nothing returned

is there not doing right setting command object? have noticed adodb seems slower never this

the query joins several tables , other calculations, going 1.5 seconds >2 minutes must mean in vba have missed

this vba connection string code:

if svrcon nothing     set svrcon = new adodb.connection end if  if not checkserverconnectionstate      constr = "provider=sqloledb;data source=ussantapps332;" & _                     "initial catalog=global_oee_data_capture_dev;user id=sqluser;password=*****;"      ' open connection     svrcon.connectiontimeout = 0     svrcon.open constr  end if 

this vba select code:

dim cmd adodb.command dim par adodb.parameter dim rst adodb.recordset          ' create command object set cmd = new adodb.command  cmd.commandtimeout = 120 cmd.activeconnection = svrcon cmd.commandtext = sql   ' create parameter object if isarrayinitialized(params)     x = 0 ubound(params)         if isnull(params(x, 1))             set par = cmd.createparameter(type:=params(x, 0), size:=1)         else             set par = cmd.createparameter(type:=params(x, 0), size:=len(params(x, 1)) + 1)         end if         par.value = params(x, 1)          cmd.parameters.append par          set par = nothing          doevents     next end if   ' open recordset object on error goto executeerror debug.print format(now, "hh:mm:ss") set rst = cmd.execute debug.print format(now, "hh:mm:ss") on error goto 0 

the sql string , parameters passed function, connection opened method

the query is:

select u.unitsid, l.linename, v.vsname, o.operatorshift, o.lineleader, o.cotyops, o.tempops, u.workorder, u.productcode,            s.proddesc, u.timelinestart, u.timelineend, u.unitsproduced, u.actlinespeed, u.tgtlinespeed, sum(case when c.dtincludedinoee = 0 d.downtimelength else 0 end),            u.offlinetaskid, r.rate, s.labhrsperthou, s.pheads, t.tgtoee, t.tgteff, t.tgtprod       dataunits u         left join dataoperatornames o on o.operatorid = u.operatornameid         inner join setuplines l on u.lineid = l.lineid         inner join setupvaluestreams v on v.vsid = l.vsid         inner join setuppus p on v.puid = p.puid         left join datadowntimes d on u.unitsid = d.unitsid         left join setupdowntimes sd on d.dtid = sd.dtid         left join setupdowntimecats c on sd.dtcatid = c.dtcatid         left join (select vsid, avg(rateval) rate                  datarates fystart >= '2014-07-01' , fystart < '2015-07-01'                         group vsid) r on r.vsid = l.vsid         left join datastandards s on s.prodcode = u.productcode         left join (select lineid, avg(tgtoee) tgtoee, avg(tgteff) tgteff, avg(tgtprod) tgtprod                     datatargets tgtmonth >= '2015-03-01' , tgtmonth < '2015-04-01'                             group lineid) t on l.lineid = t.lineid       (s.sapversion = (select min(sapversion) datastandards s2 s2.prodcode = s.prodcode)             or s.sapversion null)       , p.siteid = 2 , u.timelinestart >= '2015-03-05 23:00' , u.timelinestart < '2015-03-31 23:00'       group u.unitsid, l.linename, v.vsname, o.operatorshift, o.lineleader, o.cotyops, o.tempops, u.workorder, u.productcode, s.proddesc, u.timelinestart,                u.timelineend, u.unitsproduced, u.actlinespeed, u.tgtlinespeed, u.offlinetaskid, r.rate, s.labhrsperthou, s.pheads, t.tgtoee, t.tgteff, t.tgtprod       order u.timelinestart asc 

the discussion on chat showed that:

• the problem occurs while connecting remote database.
• the old sqloledb provider used.

i suggested give chance newer provider sqlncli should more effective while communicating mssql. when connection string modified execution time dropped 2 minutes 3 seconds.