Connection with SQL Server generate "Timeout expired"

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.

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

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?

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

"joaovtt" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

I know this is an old thread, but I just stumbled upon it and have also had this problem in the past.

You can make the timeout work by setting it immediately before opening the connection:

Dim con As Object
Set con = CreateObject("ADODB.Connection")

con.ConnectionTimeout=600

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');"