Hi All,

When I execute the following query

"DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio IN ('0','N')
AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND CodInterno_IV =
'0')"

in a SQL Server database a receive the error message "Timeout
Expired".

I already changed the parameters to the OLEDB connection:
- Connect Timeout = 3000
- Connection Lifetime = 600

But I continue receiving the same error message.

Anybody can help me?

Best regards,
JV.


--
joaovtt
------------------------------------------------------------------------
joaovtt's Profile: http://www.excelforum.com/member.php...o&userid=24286
View this thread: http://www.excelforum.com/showthread...hreadid=381734

Hi JV,

joaovtt wrote:
When I execute the following query

"DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio IN
('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND
CodInterno_IV = '0')"

in a SQL Server database a receive the error message "Timeout
Expired".

I already changed the parameters to the OLEDB connection:
- Connect Timeout = 3000
- Connection Lifetime = 600

But I continue receiving the same error message.

If you execute this statement using Query Analyzer or a similar tool, how
long does it take to execute?

How are you executing the statement in VBA? Using the Connection object or
the Command object?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

hi Jake,

if I use Query Analyzer, this query take around 3 or 4 minutes.


I'm using the following code:

Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=SQLOLEDB.1;Connect Timeout=0;Command Timeout=0;Data
Source=" & server & ";Initial Catalog=" & dataBase & ";User Id=" &
userName & ";password=" & uPassword & ""
con.Execute "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio
IN ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND
CodInterno_IV = '0');"


best regards,
JV


--
joaovtt
------------------------------------------------------------------------
joaovtt's Profile: http://www.excelforum.com/member.php...o&userid=24286
View this thread: http://www.excelforum.com/showthread...hreadid=381734

Hi JV,

I have never attempted to use "Command Timeout" in the connection string
itself. Maybe it's not supported? Either way, you can add this code before
invoking the Execute method:

con.CommandTimeout = 0

That should allow the query to take as long as it needs to.

That said, 3 to 4 minutes for a query as simple as this seems like a long
time. How many records are in your table? What types of fields are those
that are referenced in your WHERE clause? Are there indexes on those
fields?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


joaovtt wrote:
hi Jake,

if I use Query Analyzer, this query take around 3 or 4 minutes.


I'm using the following code:

Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=SQLOLEDB.1;Connect Timeout=0;Command Timeout=0;Data
Source=" & server & ";Initial Catalog=" & dataBase & ";User Id=" &
userName & ";password=" & uPassword & ""
con.Execute "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio
IN ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND
CodInterno_IV = '0');"


best regards,
JV

Maybe start using a T-SQL stored proc for this?

Also in Query Analyzer run the optimizer to see if you missed any
indexes. Either your SQL server is underperforming or not fully
optimized.

DM Unseen

I'm sorry, but how can I call a stored procedure from a excel macro?


My table has up to 4 million records... At the moment, I'm not using
any indexes because it's a table for staging in a ETL process. If I use
indexes any time I truncate and insert in this table, these indexes will
be update?


--
joaovtt
------------------------------------------------------------------------
joaovtt's Profile: http://www.excelforum.com/member.php...o&userid=24286
View this thread: http://www.excelforum.com/showthread...hreadid=381734