Connection with SQL Server generate "Timeout expired"

[joaovtt]

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.

 

[Jake Marx]

Hi JV,

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]

 

[joaovtt]

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

 

[Jake Marx]

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]

 

[DM Unseen]

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

 

[joaovtt]

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?

 

[Robin Hammond]

this shows a previous post where I describe running a stored proc.

http://tinyurl.com/b4ewn

4 million rows is not a lot. One way or another you should have a primary
key set on the table that will give you a start towards indexing it. You
could also try creating a trace file using Profiler testing your typical
activities then use the index tuning wizard in enterprise manager with the
trace.

Robin Hammond
www.enhanceddatasystems.com

 

[DM Unseen]

Indexes get updated after truncation and insertion, Only dropping the
table destroys the the index.

there are a lot of ways to run an sp from Excel, but your main concern
here is how much "raw" data is goin over from Excel to SQLserver. just
a few values or whole sheets of data.

- Use ADO code to connect to SQlserver with VBA and run an sp with
parametes
- Use a Querytable with ODBC and use the Call syntax. this could be run
without code (with a little trickery)


Pls elaborate on the data, then i can tell which method is best.

DM Unseen